Exp and expdp Import and Export Oracle10g Learning Series (8)

I. matters that should be paid attention to when using both:

1. Exp and imp are client-side tool programs that can be used either on the client side or on the server side. Expdp and impdp are server-side tools, they can only be used on the oracle server, not on the client.

Expdp impdp import large files are faster than exp and imp, and can be guided in parallel, but small import files are slow. Someone on the Internet said: for a library of more than 600g, it only takes about 40 minutes for expdp and 50 hours for exp.

Imp applies only to exp exported files, not expdp exported files, and impdp applies only to expdp exported files, not exp exported files.

Second, the simple usage of exp and expdp:

1. Exp backup

(1) when you import multiple tables by table name, tables=tb2,tb3 or multiple tables, you can separate them with commas or spaces

Exp bob/qazwsx@orcl tables=tb2,tb3 file=/tmp/baktb01.dmp log=/tmp/baktb01.log

(2) Press owner=bob to export all the tables under the user.

Exp bob/qazwsx@orcl owner=bob file=/tmp/baktb02.dmp log=/tmp/baktb02.log

(3) Export the whole library, full=y, with system or user guide with sysdba permission

Exp bob/qazwsx@orcl full=y file=/tmp/baktb03.dmp log=/tmp/baktb03.log

(4) if the exported table file reports an error of more than 2G, it can be exported in multiple files.

Exp bob/qazwsx@orcl filesize=1g tables=tb2,tb3 file=/tmp/tb01.dmp,/tmp/tb02.dmp log=/tmp/baktb04.log

(5) Export table structure

Exp bob/qazwsx@orcl rows=n tables=tb2 file=/tmp/baktb04.dmp

(6) Export table data

Exp bob/qazwsx@orcl rows=y tables=tb2 file=/tmp/baktb05.dmp

2. Imp reduction

(1) restore table

Imp bob/qazwsx@orcl tables=tb2,tb3 file=/tmp/baktb01.dmp log=/tmp/baktb01.log

If the table already exists or needs to ignore the table structure, ignore=y, you need to ignore the parameters

Imp bob/qazwsx@orcl tables=tb2,tb3 file=/tmp/baktb01.dmp ignore=y log=/tmp/baktb01.log

(2) restore all tables under the user

Imp bob/qazwsx@orcl owner=bob file=/tmp/baktb02.dmp log=/tmp/baktb02.log

(3) Library restore

Imp bob/qazwsx@orcl full=y file=/tmp/baktb03.dmp log=/tmp/baktb03.log

3. Expdp backup

Users with sysdba permissions, create an expdp directory under / tmp/ and give import and export permissions

SQL > create directory expdp as'/ tmp/'

SQL > grant read,write on directory expdp to bob

(1) when you import multiple tables by table name, tables=tb2,tb3 or multiple tables, you can separate them with commas or spaces

Expdp bob/qazwsx@orcl tables=tb2,tb3 directory=expdp dumpfile=bak01.dmp logfile=expdp01.log

(2) Press schemas=bob to export all the tables under the user.

Expdp bob/qazwsx@orcl schemas=bob directory=expdp dumpfile=bak02.dmp logfile=expdp02.log

(3) Export the whole library, full=y, with system or user guide with sysdba permission

Expdp bob/qazwsx@orcl full=y directory=expdp dumpfile=bak03.dmp logfile=expdp03.log

Or expdp\ 'sys/oracle as sysdba\' full=y directory=expdp dumpfile=full.dmp

(4) Guide according to query conditions

Expdp bob/qazwsx@orcl directory=expdp dumpfile=bak04.dmp tables=emp query='WHERE deptno=20'

(5) according to the table space guide

Expdp bob/qazwsx@orcl directory=expdp dumpfile=tablespace.dmp tablespaces=temp,example

4. Impdp restore

(1) restore table

Impdp bob/qazwsx@orcl tables=tb2,tb3 directory=expdp dumpfile=bak01.dmp

(2) restore all tables under the user

Impdp bob/qazwsx@orcl schemas=bob directory=expdp dumpfile=bak02.dmp

(3) Library restore

Impdp bob/qazwsx@orcl full=y directory=expdp dumpfile=bak03.dmp

(4) append, use append if the table structure exists

Impdp scott/tiger dumpfile=expdp:scott.bak tables=emp tables_exists_action=append

(5) replace, do not release tablespace

Impdp scott/tiger dumpfile=expdp:scott.bak tables=emp tables_exists_action=replace

(6) truncation will free up space and reinsert new data.

Impdp scott/tiger dumpfile=expdp:scott.bak tables=emp tables_exists_action=truncate

(7) when only the emp table is restored, the other two tables will skip the restore

Impdp scott/tiger dumpfile=expdp:scott.bak tables=emp,dept,salgrade tables_exists_action=skip

(8) change the owner of the table:

Example: impdp user/passwd direcotry=xxx dumpfile=xxx.dmp remap_schema= original schema: new schema name logfile=xxx.log

Impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system

(9) Import tablespaces

Impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example