expdp 和impdp使用之一（不同用户和不用表空间）

1、创建DIRECTORY

1 2	SQL>  create  or  replace  directory dir_dp  as  '/fol/dir_dp' ; Directory created.

2、授权

1 2	SQL>  grant  read ,write  on  directory dir_dp  to  scott; Grant  succeeded.

3.查看目录及权限

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

SQL>  set  lines 200 pagesize 1000 SQL>  SELECT  privilege, directory_name, DIRECTORY_PATH  FROM  user_tab_privs t, all_directories d  WHERE  t.table_name(+) = d.directory_name  ORDER  BY  2, 1; PRIVILEGE       DIRECTORY_NAME                 DIRECTORY_PATH --------------- ------------------------------ ---------------------------------------------------------------------------------------------------- READ             DATA_FILE_DIR                  /fol/app/oracle/product/11.2.0/db_1/demo/ schema /sales_history/ READ             DATA_PUMP_DIR                  /fol/app/oracle/admin/CPP/dpdump/ READ             DATA_PUMP_DIR                  /fol/app/oracle/admin/CPP/dpdump/ WRITE           DATA_PUMP_DIR                  /fol/app/oracle/admin/CPP/dpdump/ WRITE           DATA_PUMP_DIR                  /fol/app/oracle/admin/CPP/dpdump/ READ             DIR_DP                         /fol/dir_dp WRITE           DIR_DP                         /fol/dir_dp READ             LOG_FILE_DIR                   /fol/app/oracle/product/11.2.0/db_1/demo/ schema /log/ WRITE           LOG_FILE_DIR                   /fol/app/oracle/product/11.2.0/db_1/demo/ schema /log/ READ             MEDIA_DIR                      /fol/app/oracle/product/11.2.0/db_1/demo/ schema /product_media/ READ             ORACLE_OCM_CONFIG_DIR          /fol/app/oracle/product/11.2.0/db_1/ccr/state WRITE           ORACLE_OCM_CONFIG_DIR          /fol/app/oracle/product/11.2.0/db_1/ccr/state READ             ORACLE_OCM_CONFIG_DIR2         /fol/app/oracle/product/11.2.0/db_1/ccr/state WRITE           ORACLE_OCM_CONFIG_DIR2         /fol/app/oracle/product/11.2.0/db_1/ccr/state READ             SS_OE_XMLDIR                   /fol/app/oracle/product/11.2.0/db_1/demo/ schema /order_entry/ WRITE           SS_OE_XMLDIR                   /fol/app/oracle/product/11.2.0/db_1/demo/ schema /order_entry/ READ             SUBDIR                         /fol/app/oracle/product/11.2.0/db_1/demo/ schema /order_entry//2002/Sep WRITE           SUBDIR                         /fol/app/oracle/product/11.2.0/db_1/demo/ schema /order_entry//2002/Sep                  XMLDIR                         /fol/app/oracle/product/11.2.0/db_1/rdbms/xml 19  rows  selected. SQL>  select  DEFAULT_TABLESPACE  from  dba_users  where  username= 'SCOTT' ; DEFAULT_TABLESPACE ------------------------------ USERS

4.执行导出

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

expdp scott/scott@CPP schemas=scott directory=dir_dp dumpfile =expdp_scott1.dmp logfile=expdp_scott1.log; $ expdp scott/scott@CPP schemas=scott directory=dir_dp dumpfile =expdp_scott1.dmp logfile=expdp_scott1.log; Export: Release 11.2.0.4.0 - Production  on  Fri Sep 11 16:02:49 2015 Copyright (c) 1982, 2011, Oracle  and / or  its affiliates.   All  rights reserved. Connected  to : Oracle  Database  11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With  the Partitioning, OLAP, Data Mining  and  Real  Application Testing options Starting  "SCOTT" . "SYS_EXPORT_SCHEMA_01" :  scott/********@CPP schemas=scott directory=dir_dp dumpfile=expdp_scott1.dmp logfile=expdp_scott1.log  Estimate  in  progress using BLOCKS method... Processing object type SCHEMA_EXPORT/ TABLE /TABLE_DATA Total estimation using BLOCKS method: 10.18 MB Processing object type SCHEMA_EXPORT/ USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/ TABLE / TABLE Processing object type SCHEMA_EXPORT/ TABLE /COMMENT Processing object type SCHEMA_EXPORT/ TABLE / INDEX / INDEX Processing object type SCHEMA_EXPORT/ TABLE / CONSTRAINT / CONSTRAINT Processing object type SCHEMA_EXPORT/ TABLE / INDEX / STATISTICS /INDEX_STATISTICS Processing object type SCHEMA_EXPORT/ TABLE / CONSTRAINT /REF_CONSTRAINT Processing object type SCHEMA_EXPORT/ TABLE / STATISTICS /TABLE_STATISTICS . . exported  "SCOTT" . "TEST"                               8.414 MB   86785  rows . . exported  "SCOTT" . "DEPT"                               5.937 KB       4  rows . . exported  "SCOTT" . "EMP"                                8.570 KB      14  rows . . exported  "SCOTT" . "SALGRADE"                           5.867 KB       5  rows . . exported  "SCOTT" . "BONUS"                                  0 KB       0  rows Master  table  "SCOTT" . "SYS_EXPORT_SCHEMA_01"  successfully loaded/unloaded ****************************************************************************** Dump file  set  for  SCOTT.SYS_EXPORT_SCHEMA_01  is :    /fol/dir_dp/expdp_scott1.dmp Job  "SCOTT" . "SYS_EXPORT_SCHEMA_01"  successfully completed  at  Fri Sep 11 16:03:28 2015 elapsed 0 00:00:37

目标库：

1、创建测试表空间和用户

1 2 3 4 5 6 7 8 9 10 11 12

SQL>  create  tablespace LLC datafile  '+DATA/phub/datafile/LLC01.dbf'  size  5G; Tablespace created. SQL>  create  user  lilc identified  by  lilc  default  tablespace LLC; User  created. SQL>  grant  dba  to  lilc; Grant  succeeded. SQL> conn lilc/lilc; Connected. SQL>  select  DEFAULT_TABLESPACE  from  dba_users  where  username= 'LILC' ; DEFAULT_TABLESPACE ------------------------------ LLC

2.创建DIRECTORY

1 2	SQL>  create  or  replace  directory dir_dp  as  '/home/oracle/dir_dp' ; Directory created.

3.授权

1	SQL>  grant  read ,write  on  directory dir_dp  to  lilc;

4.执行导入：

更换表空间和更换用户导入：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

[oracle@cwogg dir_dp]$ impdp lilc/lilc directory=dir_dp DUMPFILE=expdp_scott1.dmp remap_schema=scott:lilc remap_tablespace=USERS:LLC exclude= USER Import: Release 11.2.0.4.0 - Production  on  Fri Sep 11 16:40:57 2015 Copyright (c) 1982, 2011, Oracle  and / or  its affiliates.   All  rights reserved. Connected  to : Oracle  Database  11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With  the Partitioning, Automatic Storage Management, OLAP, Data Mining and  Real  Application Testing options Master  table  "LILC" . "SYS_IMPORT_FULL_01"  successfully loaded/unloaded Starting  "LILC" . "SYS_IMPORT_FULL_01" :  lilc/******** directory=dir_dp DUMPFILE=expdp_scott1.dmp remap_schema=scott:lilc remap_tablespace=USERS:LLC exclude= USER  Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/ TABLE / TABLE Processing object type SCHEMA_EXPORT/ TABLE /TABLE_DATA . . imported  "LILC" . "TEST"                                16.81 MB  173570  rows . . imported  "LILC" . "DEPT"                                5.937 KB       4  rows . . imported  "LILC" . "EMP"                                 8.570 KB      14  rows . . imported  "LILC" . "SALGRADE"                            5.867 KB       5  rows . . imported  "LILC" . "BONUS"                                   0 KB       0  rows Processing object type SCHEMA_EXPORT/ TABLE / INDEX / INDEX Processing object type SCHEMA_EXPORT/ TABLE / CONSTRAINT / CONSTRAINT Processing object type SCHEMA_EXPORT/ TABLE / INDEX / STATISTICS /INDEX_STATISTICS Processing object type SCHEMA_EXPORT/ VIEW / VIEW Processing object type SCHEMA_EXPORT/ TABLE / CONSTRAINT /REF_CONSTRAINT Processing object type SCHEMA_EXPORT/ TABLE / STATISTICS /TABLE_STATISTICS Job  "LILC" . "SYS_IMPORT_FULL_01"  successfully completed  at  Fri Sep 11 16:41:05 2015 elapsed 0 00:00:07

目标库上没有相同的用户导出和导入：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

$ expdp system/123123@CPP schemas=test directory=dir_dp dumpfile =test.dmp logfile=test.log; Export: Release 11.2.0.4.0 - Production  on  Fri Sep 11 17:32:57 2015 Copyright (c) 1982, 2011, Oracle  and / or  its affiliates.   All  rights reserved. Connected  to : Oracle  Database  11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With  the Partitioning, OLAP, Data Mining  and  Real  Application Testing options Starting  "SYSTEM" . "SYS_EXPORT_SCHEMA_01" :  system/********@CPP schemas=test directory=dir_dp dumpfile=test.dmp logfile=test.log  Estimate  in  progress using BLOCKS method... Processing object type SCHEMA_EXPORT/ TABLE /TABLE_DATA Total estimation using BLOCKS method: 20.06 MB Processing object type SCHEMA_EXPORT/ USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/ TABLE / TABLE . . exported  "TEST" . "T"                                   16.81 MB  173576  rows . . exported  "TEST" . "T2"                                  19.25 KB      74  rows Master  table  "SYSTEM" . "SYS_EXPORT_SCHEMA_01"  successfully loaded/unloaded ****************************************************************************** Dump file  set  for  SYSTEM.SYS_EXPORT_SCHEMA_01  is :    /fol/dir_dp/test.dmp Job  "SYSTEM" . "SYS_EXPORT_SCHEMA_01"  successfully completed  at  Fri Sep 11 17:33:06 2015 elapsed 0 00:00:07

导入：（更改用户的默认表空间）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

[oracle@cwogg dir_dp]$ impdp lilc/lilc directory=dir_dp DUMPFILE=test.dmp remap_schema=test:test remap_tablespace=test:LLC logfile=test.log; Import: Release 11.2.0.4.0 - Production  on  Fri Sep 11 17:53:26 2015 Copyright (c) 1982, 2011, Oracle  and / or  its affiliates.   All  rights reserved. Connected  to : Oracle  Database  11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With  the Partitioning, Automatic Storage Management, OLAP, Data Mining and  Real  Application Testing options Master  table  "LILC" . "SYS_IMPORT_FULL_01"  successfully loaded/unloaded Starting  "LILC" . "SYS_IMPORT_FULL_01" :  lilc/******** directory=dir_dp DUMPFILE=test.dmp remap_schema=test:test remap_tablespace=test:LLC logfile=test.log  Processing object type SCHEMA_EXPORT/ USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/ TABLE / TABLE Processing object type SCHEMA_EXPORT/ TABLE /TABLE_DATA . . imported  "TEST" . "T"                                   16.81 MB  173576  rows . . imported  "TEST" . "T2"                                  19.25 KB      74  rows Job  "LILC" . "SYS_IMPORT_FULL_01"  successfully completed  at  Fri Sep 11 17:53:32 2015 elapsed 0 00:00:05