Shulou(Shulou.com)06/01 Report--

1. Oracle database operation

1. Create a database

Create database databasename

Create table table_new as select * from table_old; replication structure and data

Create table table_new as select * from table_old where 1: 0; copy structure only

2. Delete the database

Drop database dbname

3. Back up the database

Full backup

Exp demo/demo@orcl buffer=1024 file=d:\ back.dmp full=y

Demo: user name, password

Buffer: cache siz

File: specific backup file address

Full: whether to export all files

Ignore: ignore the error, or overwrite if the table already exists

Export tables for system and sys users in the database

Exp demo/demo@orcl file=d:\ backup\ 1.dmp owner= (system,sys)

Export the specified table

Exp demo/demo@orcl file=d:\ backup2.dmp tables= (teachers,students)

By filter criteria, export

Exp demo/demo@orcl file=d:\ back.dmp tables= (table1) query=\ "where filed1 like 'fg%'\"

You can compress the export; add compress=y after the command; if you need a log, then: log=d:\ log.txt

Back up the database of the remote server

Exp username / password @ remote IP: Port / instance file= location:\ file name. Dmp full=y

4. Database restore

Open cmd and execute the following command directly without having to log in to sqlplus.

Complete reduction

Imp demo/demo@orcl file=d:\ back.dmp full=y ignore=y log=D:\ implog.txt

It is important to specify the log so that errors can be analyzed and remedied.

Import the specified table

Imp demo/demo@orcl file=d:\ backup2.dmp tables= (teachers,students)

Restore to a remote server

Imp username / password @ remote IP: Port / instance file= location:\ file name. Dmp full=y

II. Oracle table operation

1. Create a table

Create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],..)

Create a new table based on an existing table:

A:select * into table_new from table_old (create a new table using the old table)

B:create table tab_new as select col1,col2... From tab_old definition only

Summary:

a. If only the replicated table structure adds a condition that can never be established, then the replicated table structure is represented, but the contents of the table are not replicated.

Create table user name. Table name as select * from user name. Table name where 1: 2

For example, create table zdsy.bs_log2 as select * from zdsy.bs_log where 1x2

b. Fully replicate tables (including creating tables and copying records in tables)

Create table test as select * from bs_log-- bs_log is a copied table

2. Delete the table

Drop table tabname

3. Rename the table

Description: alter table table name rename to new table name

Eg:alter table tablename rename to newtablename

4. Add a field

Description: alter table table name add (whether the default value of field name field type is empty)

Example: alter table tablename add (ID int)

Alter table tablename add (ID varchar2 (30) default 'empty' not null)

5. Modify the field

Description: alter table table name modify (whether the default value of field name field type is empty)

Eg:alter table tablename modify (ID number (4))

6. Duplicate name field

Description: alter table table name rename column column name to new column name (where: column is the keyword)

Eg:alter table tablename rename column ID to newID

7. Delete a field

Description: alter table table name drop column field name

Eg:alter table tablename drop column ID

8. Add primary key

Alter table tabname add primary key (col)

9. Delete the primary key

Alter table tabname drop primary key (col)

10. Create an index

Create [unique] index idxname on tabname (col … (.)

11. Delete the index

Drop index idxname

Note: the index is immutable and must be deleted and rebuilt if you want to change it.

12. Create a view

Create view viewname as select statement

13. Delete view

Drop view viewname

14. Rebuild the index

Select 'alter index' | | index_name | | 'rebuild tablespace SHINEYUE45 online;' from user_indexes where table_name='cw_kmbh'

Alter index CW_KMBH_KMBH rebuild tablespace SHINEYUE45 online

Alter index CW_KMBH_ZJM rebuild tablespace SHINEYUE45 online

Alter index PK_CW_KMBH rebuild tablespace SHINEYUE45 online

3. Oracle operation data

1. Data query

Select from [where] [order by [asc or desc]]

2. Insert data

Insert into table name values (values for all columns)

Insert into test values (1 recorder zhangsanqie 20)

Insert into table name (column) values (corresponding value)

Insert into test (id,name) values (2)

3. Update data

Update table set column = new value [where condition]-- > Update records that meet the criteria

Update test set name='zhangsan2' where name='zhangsan'

Update table set column = new value-- > update all data

Update test set age = 20

4. Delete data

Delete from table name where condition-- > Delete records that meet the criteria

Delete from test where id = 1

Delete from test-- > Delete all

Commit;-- > submit data

Rollback;-- > rollback data

Deleted data can be recovered in delete mode, but there is no way to submit it. When delete deletes, logs will be recorded-- > deletion will be very slow.

Truncate table table name

Delete all data, will not affect the table structure, will not log, the data can not be recovered-- > delete quickly

Drop table table name

Delete all data, including the table structure, without logging, and the data cannot be recovered-- > delete quickly

5. Data replication

Table data replication

Insert into table1 (select * from table2)

Copy table structure

Create table table1 select * from table2 where 1 > 1

Copy table structure and data

Create table table1 select * from table2

Copy the specified field

Create table table1 as select id, name from table2 where 1 > 1

6. Create user inverted library

Select * from dba_directories

Select * from dba_users

Drop user shineyue45_hhgjjcs cascade;---- deletion

Create user shineyue45_hhgjjcs identified by atwasoft2017 default tablespace SHINEYUE45;-- assigns the user the default tablespace

Grant connect,resource to shineyue45_hhgjjcs

Grant select any dictionary to shineyue45_hhgjjcs;-- query dictionary

Grant create any view to shineyue45_hhgjjcs;-- view

Grant select_catalog_role to shineyue45_hhgjjcs

Grant execute any procedure to shineyue45_hhgjjcs

Grant debug any procedure to shineyue45_hhgjjcs;-- storage

Grant debug connect session to shineyue45_hhgjjcs;-- session

Grant select any table to shineyue45_hhgjjcs;-- query table

GRANT "EXP_FULL_DATABASE" TO shineyue45_hhgjjcs WITH ADMIN OPTION;-- export

GRANT "IMP_FULL_DATABASE" TO shineyue45_hhgjjcs WITH ADMIN OPTION;-- Import

Scp-r / hhgjj_backup/backup/shineyue4020180928.dmp root@172.18.111.55:/oracle/backup/

Impdp SHINEYUE45_HHGJJCS/atwasoft2017@172.18.111.55:1521/orcl directory=HHGJJ_BACKUP dumpfile=shineyue4020181211.dmp logfile=shineyue4020181211.log remap_schema=HHGJJ_SYS40:SHINEYUE45_HHGJJCS remap_tablespace=HHGJJ_SYS40:SHINEYUE45 transform=OID:N

Note: 1 > if there is a partition table, you need to add the transform=segment_attributes:n parameter to this import statement. This parameter can be associated with ignoring the related tablespace and storage clause constraints that accompany the expdp export.

Expdp HHGJJ_SYS40/atwasoft2018@172.18.111.77:1521/orcl directory=BACKUP_DATA dumpfile=shineyue4020180918.dmp schemas=HHGJJ_SYS40

Note: do not have semicolons at the end of impdp and empdp statements

7.insert is used with select

Insert into im_gryhzh

Select fancinewidge.grbhrea.jkrxmreachd.hkzhzhzhzh``, '01parallel recalcityjkrzjhreedsysdatedswtyhbmnotec.mcwerec.bmmageda.cllrcczy, a.lrczyxm.',' 00czyxm', b.yhbm

From grdk_sq_htxx a where d.jkhtbh in bmpuryhbm bmeme bmsquimmeryhjg c grdkredkwrzz d (select jkhtbh from grdk_dk_zz where dkzt='02')

And d.hkzh not in (select yhzh from im_gryhzh)

And d.swtyhbm=b.bm and b.yhbm=c.yhbm and d.dkffrq > = to_date ('20180501' magic memory yyyymmdd') and a.jkhtbh=d.jkhtbh

7.creat and select are used together-production for backing up specified data

Create table grdk_tx_hk_yh_jh_0920 as

Select * from grdk_tx_hk_yh yh where exists

(select ywlsh from im_tx_zjcljg_zjb where ywlsh=yh.id and pcid in

(select pcid from im_tx_zjjsjy_zjb a where a.batchno in ('VS201809180059015334','VS201809180059015443')) and fse0)

And jkhtbh not in (select jkhtbh from grdk_dk_hkmx

Where jzrq=to_date ('20180918') and hkzt in ('02') and hktj='02' and gjjzqe=0)

8.update is used with select

Update grdk_tx_hk_yh set sfpbcg=0

Where id in (select ywlsh from im_tx_zjcljg_zjb where pcid in

(select pcid from im_tx_zjjsjy_zjb a where a.batchno in '20180918100077703223') and fse0)

And jkhtbh not in (

Select jkhtbh from grdk_dk_hkmx

Where jzrq=to_date ('20180918) and hkzt='02' and hktj='02' and gjjzqe=0)

Update tmp_cw_dwfzhs a set a.xmbm1mc = (select nvl (max (hsxmmc),'') from cw_fzhs_mx where hsxmbm=a.xmbm1 and hslxbm='001' and ztbh=v_ztbh and nd=v_cwnd)

Where exists (select max (hsxmmc) from cw_fzhs_mx where hsxmbm=a.xmbm1 and ztbh=v_ztbh and nd=v_cwnd and hslxbm='001')

9.delete is used with update

Delete from grdk_tx_hk where jkhtbh in (select jkhtbh from grdk_tx_hk_yh

Where id in (select ywlsh from im_tx_zjcljg_zjb where pcid in

(select pcid from im_tx_zjjsjy_zjb a where a.batchno in '20180918100077703223') and fse0)

And jkhtbh not in (

Select jkhtbh from grdk_dk_hkmx

Where jzrq=to_date ('20180918) and hkzt='02' and hktj='02' and gjjzqe=0)) and txrq > to_date (' 20180918))

10. Cursor usage-equivalent to a collection of data types in java, especially suitable for dealing with batch data (such as batch unaccounted for)

DECLARE

V_GRZH VARCHAR2 (20): =''

V_JXRQ DATE:=TO_DATE ('20190701' copyright YYYYMMDD')

V_DQJS NUMBER (20jue 2): = 0

V_HQJS NUMBER (20jue 2): = 0

V_DQLX NUMBER (20jue 2): = 0

V_HQLX NUMBER (20jue 2): = 0

V_LXHJ NUMBER (20jue 2): = 0

CURSOR CUR_GRZH IS SELECT GRZH FROM GJZF_GR_ZZ WHERE grzh='000000151018'

BEGIN

OPEN CUR_GRZH

LOOP

FETCH CUR_GRZH INTO V_GRZH

EXIT WHEN CUR_GRZH%NOTFOUND

V_DQJS:=0

V_HQJS:=0

P_HFB_JCR_NDJX_JSCALC (Vthose GRZH reps Vendor JXRQ repartees Vendor DQJS repositories Vendor HQJS Variety DQLX repertoire Vendor HQLX Variations LXHJ)

UPDATE GJZF_GR_ZZ SET NDJXDQJS=V_DQJS,NDJXHQJS=V_HQJS WHERE GRZH=V_GRZH

COMMIT

END LOOP

CLOSE CUR_GRZH

END

Four > Oracle timer operation

1 > query what timers are under the database

Select * from dba_jobs

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.