Simple Test of expdp and impdp

1. Use expdp and impdp

Oracle Database 10 Gb introduces the latest data pump technology, the role of Data Pump Export Import (expdp and impdp)

1) implement logical backup and logical recovery.
2) move objects between database users.
3) move objects between databases
4) Implement table space migration.

Ii. Differences between data pump export and traditional export

Before 10 Gb, the traditional export and import respectively use the exp tool and IMP tool, starting from 10 Gb, not only retain the original exp and IMP tools, the Data Pump Export Import tools expdp and impdp are also provided.
Precautions when using expdp and impdp:

1) exp and IMP are client tool programs that can be used either on the client or on the server.
2) expdp and impdp are tool programs on the server. They can only be used on the Oracle server and cannot be used on the client.

3) imp only applies to exp export files, not expdp export files; impdp only applies to expdp export files, not exp export files.

Data Pump export includes four modes: export table, export scheme, export tablespace, and export database.

When the expdp tool is used, its dump file can only be stored in the OS directory corresponding to the directory object, rather than directly specifying the OS directory where the dump file is located. therefore, when using the expdp tool, you must first create
Directory object. You must grant the permission to use directory objects to database users.
============================================ Export/ import table ============================================
1. Log On As sys, create directories, and authorize users

C: \ Users \ Administrator> sqlplus/As sysdba; SQL * Plus: Release 11.2.0.1.0 production on Friday October 17 23:16:23 2014 copyright (c) 1982,201 0, Oracle. all rights reserved. connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit productionwith the partitioning, OLAP, data mining and real application testing optionssql> create directory test_dump as 'd: \ dumpdirectory '; the directory has been created. SQL> grant read, write on directory test_dump to Scott; authorization successful.

2. Export the Scott. EMP table. Note that do not add points after tables = EMP; otherwise, an error occurs.

C: \ Users \ Administrator> expdp Scott/tiger directory = test_dump dumpfile = testexpdpscott. EMP tables = empexport: Release 11.2.0.1.0-production on Friday October 17 23:51:29 2014 copyright (c) 1982,200 9, Oracle and/or its affiliates. all rights reserved. connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit productionwith the partitioning, OLAP, data mining and real application testing options to start "Scott ". "sys_export_table_01": Scott/******** directory = test_dump dumpfile = testexpdpscott. EMP tables = EMP is using the blocks Method for estimation... total estimation of the blocks method used to process the object type table_export/table/table_data: 64 kB processing object type table_export/table/index processing object type table_export/table/constraint processing object type table_export/table/index/Statistics /index_statistics processing object type table_export/table/constraint/ref_constraint processing object type table_export/table/statistics/table_statistics .. scott is exported ". "EMP" 8.570 kb 14 rows have successfully loaded/uninstalled the master table "Scott ". "sys_export_table_01 "************************************* **************************************** * Scott. the dump file set of sys_export_table_01 is: D: \ dumpdirectory \ testexpdpscott. EMP job "Scott ". "sys_export_table_01" was successfully completed at 23:52:00

3. Log On As Scott and delete the EMP table. Import the table with impdp.

SQL> show user; User: "Scott" SQL> drop table EMP; Table deleted. SQL> select * from EMP; select * from EMP * 1st row error: ORA-00942: Table or view does not exist except C: \ Users \ Administrator> impdp Scott/tiger directory = test_dump dumpfile = testexpdpscott. empimport: Release 11.2.0.1.0-production on Friday October 17 23:55:33 2014 copyright (c) 1982,200 9, Oracle and/or its affiliates. all rights reserved. connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit productionwith the partitioning, OLAP, data mining and real application testing options the master table "Scott" has been successfully loaded/uninstalled ". "sys_import_full_01" start "Scott ". "sys_import_full_01": Scott/******** directory = test_dump dumpfile = testexpdpscott. EMP processing object type table_export/table/table_data .. imported "Scott ". "EMP" 8.570 kb 14 rows processing object type table_export/table/index processing object type table_export/table/constraint processing object type table_export/table/index/statistics/index_statistics Processing the object type table_export/table/constraint/ref_constraint processes the table_export/table/statistics/table_statistics job "Scott ". "sys_import_full_01" successfully completed explain SQL> select * from EMP at 23:55:39; empno ename job Mgr hiredate Sal comm deptno ---------- --------- ---------- ------------ ---------- 7369 Smith clerk 7902 17-12 month-80 800 Allen salesman 207499 20-2 month-81 7698 1600 ward salesman 300 22-2 month-81 1250 500 30

...... The table is successfully imported and restored. You can add the logfile parameter during the export and import process to specify the log file name. For example, logfile = testimpdp. Log

======================================== Export/import solution === ==========================================================

C: \ Users \ Administrator> expdp Scott/tiger directory = test_dump dumpfile = schema. DMP logfile = expdp_schema.log schemas = systemexport: Release 11.2.0.1.0-production on Saturday October 18 00:01:36 2014 copyright (c) 1982,200 9, Oracle and/or its affiliates. all rights reserved. connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit productionwith the partitioning, OLAP, data mining and real applica Tion testing Options start "Scott ". "sys_export_schema_01": Scott/********* directory = test_dump dumpfile = schema. DMP logfile = expdp_schema.log schemas = system is using the blocks Method for estimation... process the total Estimation of the object type schema_export/table/table_data using the blocks method: 320 KB processing object type schema_export/user processing object type schema_export/system_grant processing object type worker/role_grant processing object type schema_export/default_role processing object type schema_export/pre_schema/procact_schema Processing object type schema_export/synonym processing object type schema_export/type/type_spec ......

Import: C: \ Users \ Administrator> impdp Scott/tiger directory = test_dump dumpfile = schema. dmp logfile = impdp_schema.log schemas = System

===========================================Export/import tablespace ============== ============================================

C: \ Users \ Administrator> expdp Scott/tiger directory = test_dump dumpfile = ts. DMP logfile = testexpdptablespace. log tablespaces = usersexport: Release 11.2.0.1.0-production on Saturday October 18 00:16:50 2014 copyright (c) 1982,200 9, Oracle and/or its affiliates. all rights reserved. connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit productionwith the partitioning, OLAP, data mining and real application testing options to start "Scott ". "sys_export_tablespace_01": Scott/******** directory = test_dump dumpfile = ts. DMP logfile = testexpdptablespace. log tablespaces = users is using the blocks Method for estimation... total estimation of the blocks method used to process the object type table_export/table/table_data: 1.125 MB processing object type table_export/table/index processing object type table_export/table/constraint processing object type table_export/table/index/Statistics /index_statistics processing object type table_export/table/comment processing object type table_export/table/constraint/ref_constraint processing object type table_export/table/statistics/table_statistics .. exported "oe ". "purchaseorder" 243.9 kb 132 rows .. exported "oe ". "subcategory_ref_list_nestedtab" 6.585 kb 21 rows .. exported "oe ". "product_ref_list_nestedtab" 12.50 kb 288 rows .. exported "oe ". "categories_tab" 14.15 kb 22 rows .. scott is exported ". "Dept" 5.937 kb 4 rows .. scott is exported ". "EMP" 8.570 kb 14 rows .. scott is exported ". "emp2" 8.570 kb 14 rows .. scott is exported ". "emp3" 8.078 KB 2 rows .. scott is exported ". "salgrade" 5.867 kb 5 rows .. scott is exported ". "test" 5.085 kb 5 rows .. scott is exported ". "bonus" 0 kb 0 rows .. exported "sh ". "dimension_exceptions" 0 kb 0 rows have successfully loaded/uninstalled the master table "Scott ". "sys_export_tablespace_01 "************************************* **************************************** * Scott. the dump file set of sys_export_tablespace_01 is: D: \ dumpdirectory \ ts. DMP job "Scott ". "sys_export_tablespace_01" was successfully completed at 00:17:49

Import: C: \ Users \ Administrator> impdp Scott/tiger directory = test_dump dumpfile = ts. dmp logfile = testimpdptablespace. Log tablespaces = users

====================================== Export/import database ========== ============================================

C: \ Users \ Administrator> expdp Scott/tiger directory = test_dump dumpfile = scott_full_expdp.dmp full = yexport: Release 11.2.0.1.0-production on Saturday October 18 00:19:48 2014 copyright (c) 1982,200 9, oracle and/or its affiliates. all rights reserved. connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit productionwith the partitioning, OLAP, data mining and real application testing options "Scott ". "sys_export_full_01": Scott/******** directory = test_dump dumpfile = scott_full_expdp.dmp full = y is using the blocks Method for estimation... process the total Estimation of the object type database_export/Schema/table/table_data using the blocks method: 135.7 MB processing object type database_export/tablespace processing object type database_export/profile processing object type database_export/sys_user/user processing object type database_export/Schema/user processing object type database_export/role processing object type database_export /grant/system_grant/proc _ System_grant: processing object type database_export/Schema/grant/system_grant: processing object type database_export/Schema/role_grant ......

Import: C: \ Users \ Administrator> impdp Scott/tiger directory = test_dump dumpfile = scott_full_expdp.dmp full = y

Simple Test of expdp and impdp