Oracle Data Pump, oracle

Oracle Data Pump, oracle

 

To use the data pump, you must first create a database directory

Database directory only allows sysCreate

Common usersAuthorization required

Assume scottThe user is an import user.

SQL>! Mkdir dp_dir

SQL> create directory dp_dir as '/home/oracle/dp_dir ';

Directory created.

SQL> grant read, write on directory dp_dir to scott;

 

Grant succeeded.

 

Data Pump export method:Export scottUser

Expdp

Export Case 1: export by table

Expdp scott/tiger directory = dp_dir dumpfile = tab. dmp logfile = scott. log tables = dept, emp

 

Export case 2 by user

Expdp scott/tiger directory = dp_dir dumpfile = dumpscott. dmp schemas = scott

 

Export Case 3: Full Database Export and parallel export

Expdp scott/tiger directory = dp_dir dumpfile = full. dmp parallel = 4 full = y

The backup file is in the/home/oracle/dp_dir/directory.

 

Data Pump import method:Import scottUser

Impdp: copy the file to the/home/oracle/dp_dir/directory.

 

Import Case 1: import by table, from scott to scott2

Impdp scott2/tiger directory = dp_dir dumpfile = tab. dmp tables = scott. dept, scott. emp remap_schema = scott: scott2

 

Import case 2 by user, from scott to scott2

Impdp scott/tiger directory = dp_dir dumpfile = schema. dmp remap_schema = scott: scott2

 

Import Case 3: Full Database Import

Impdp scott/tiger directory = dp_dir dumpfile = full. dmp full = y

Appendix:

Import operations in different tablespaces of different users:

Impdp user name/password directory = 'name of the directory you created 'dumpfile = 'name of the exported file'

Remap_schema = Name of the exported User: name of the user to be imported remap_tablespace = Name of the exported tablespace: name of the tablespace to be imported

 

 

Impdp system/oracle@XXX.XXX.XX.XXX/orcl directory = dp_dir remap_schema = EFMIS_23_YANSHI: efmis_23_20150511 transform = oid: n dumpfile = EFMIS_23_YANSHI_201505110900.dmp

 

Expdp fasp_20000/123@192.168.3.36/orcl directory = dp_dir dumpfile = dumpscott. dmp schemas = fasp_20000

 

Expdp efmis_62/EFMIS_62@192.168.100.82/orcl directory = dpdump dumpfile = EFMIS_62_20150821.dmp schemas = EFMIS_62

 

Impdp system/oracle@192.168.3.36/orcl directory = dp_dir remap_schema = OA_0608: HQOA_20150806 dumpfile = OA_0608_20150806.DMP

 

Logfile = EFMIS_62_20150821.log