溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
1、創建DIRECTORY
SQL> create or replace directory dir_dp as '/fol/dir_dp'; Directory created.
2、授權
SQL> grant read,write on directory dir_dp to scott; Grant succeeded.
3.查看目錄及權限
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.執行導出
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、創建測試表空間和用戶
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
SQL> create or replace directory dir_dp as '/home/oracle/dir_dp'; Directory created.
3.授權
SQL> grant read,write on directory dir_dp to lilc;
4.執行導入：
更換表空間和更換用戶導入：
[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
目標庫上沒有相同的用戶導出和導入：
$ 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
導入：（更改用戶的默認表空間）
[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
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
