IMPDP匯入遠端資料庫
這個功能在測試環境中較多使用。
-- 在目標庫建立DBLINK後，透過IMPDP匯入遠端資料庫到目標。
[oracle@dg dbbackup(01:13:06)]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 29 01:15:17 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
01:15:37 > create public database link db_wailon connect to scott identified by tiger using 'wailon';
Database link created.
01:16:39 > col name for a20
01:16:47 > col host for a30
01:16:54 > select name,userid,host from link$;
NAME USERID HOST
-------------------- ------------------------------ ------------------------------
OGG SCOTT ogg
DB_WAILON SCOTT wailon
01:16:57 > exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-- 使用IMPDP的NETWORK_LINK連線遠端資料庫，確保匯入前存在相關的表空間，使用者在匯入時自動建立
-- NETWORK_LINK=遠端資料庫DBLINK_NAME，SCHEMAS=需要匯入的使用者，REMAP_SCHEMA=原SCHEMA:新SCHEMA
[oracle@dg dbbackup(01:18:22)]$ impdp scott/tiger network_link=db_wailon schemas=scott remap_schema=scott:wailon
Import: Release 11.2.0.3.0 - Production on Sun Sep 29 01:19:20 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_IMPORT_SCHEMA_01": scott/******** network_link=db_wailon schemas=scott remap_schema=scott:wailon
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 10.37 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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "WAILON"."GGS_DDL_HIST" 2030 rows
. . imported "WAILON"."GGS_MARKER" 829 rows
. . imported "WAILON"."CHECKPOINT" 2 rows
. . imported "WAILON"."DEPT" 6 rows
. . imported "WAILON"."EMP" 18 rows
. . imported "WAILON"."GGS_DDL_HIST_ALT" 71 rows
. . imported "WAILON"."GGS_SETUP" 6 rows
. . imported "WAILON"."SALGRADE" 5 rows
. . imported "WAILON"."BONUS" 0 rows
. . imported "WAILON"."CHECKPOINT_LOX" 0 rows
. . imported "WAILON"."GGS_DDL_COLUMNS" 0 rows
. . imported "WAILON"."GGS_DDL_LOG_GROUPS" 0 rows
. . imported "WAILON"."GGS_DDL_OBJECTS" 0 rows
. . imported "WAILON"."GGS_DDL_PARTITIONS" 0 rows
. . imported "WAILON"."GGS_DDL_PRIMARY_KEYS" 0 rows
. . imported "WAILON"."GGS_DDL_RULES" 0 rows
. . imported "WAILON"."GGS_DDL_RULES_LOG" 0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_SCHEMA_01" completed with 0 error(s) at 01:21:02
-- 檢查是否匯入成功
[oracle@dg dbbackup(01:26:42)]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 29 01:27:01 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
01:26:50 > alter user wailon identified by wailon;
User altered.
01:27:02 > select table_name,tablespace_name from dba_tables where owner='WAILON';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GGS_SETUP USERS
GGS_DDL_HIST_ALT USERS
GGS_MARKER USERS
GGS_DDL_RULES USERS
GGS_DDL_RULES_LOG USERS
CHECKPOINT USERS
CHECKPOINT_LOX USERS
DEPT USERS
EMP USERS
BONUS USERS
SALGRADE USERS
GGS_DDL_HIST USERS
GGS_DDL_COLUMNS USERS
GGS_DDL_LOG_GROUPS USERS
GGS_DDL_PARTITIONS USERS
GGS_DDL_PRIMARY_KEYS USERS
GGS_DDL_OBJECTS USERS
GGS_TEMP_UK
GGS_STICK
GGS_TEMP_COLS
20 rows selected.
01:27:15 > drop user wailon cascade;
User dropped.
01:29:53 > exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-- 使用IMPDP匯入到不同的使用者不同的表空間，確保匯入前存在相關的表空間
-- REMAP_TABLESPACE=原TABLESPACE:新TABLESPACE
[oracle@dg dbbackup(01:42:10)]$ impdp scott/tiger network_link=db_wailon remap_tablespace=users:wailon remap_schema=scott:wailon
Import: Release 11.2.0.3.0 - Production on Sun Sep 29 01:42:28 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_IMPORT_SCHEMA_01": scott/******** network_link=db_wailon remap_tablespace=users:wailon remap_schema=scott:wailon
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12.37 MB
Processing object type SCHEMA_EXPORT/USER
ORA-39083: Object type USER failed to create with error:
ORA-00959: tablespace 'WAILON' does not exist
Failing sql is:
CREATE USER "WAILON" IDENTIFIED BY VALUES 'S:757B41B311870958859653625C627A2D72CCED18785157E6D6AD16F95A9D;F894844C34402B67' DEFAULT TABLESPACE "WAILON" TEMPORARY TABLESPACE "TEMP01"
-- 表空間不存在時報錯
-- 建立需要的表空間
[oracle@dg dbbackup(02:01:03)]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 29 02:01:10 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
02:06:39 > create tablespace wailon datafile size 100m;
Tablespace created.
02:07:00 > exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-- 重新匯入
[oracle@dg dbbackup(02:07:03)]$ impdp scott/tiger network_link=db_wailon remap_tablespace=users:wailon remap_schema=scott:wailon
Import: Release 11.2.0.3.0 - Production on Sun Sep 29 02:07:05 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_IMPORT_SCHEMA_01": scott/******** network_link=db_wailon remap_tablespace=users:wailon remap_schema=scott:wailon
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13.37 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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "WAILON"."GGS_DDL_HIST" 2534 rows
. . imported "WAILON"."GGS_MARKER" 1435 rows
. . imported "WAILON"."CHECKPOINT" 2 rows
. . imported "WAILON"."DEPT" 6 rows
. . imported "WAILON"."EMP" 18 rows
. . imported "WAILON"."GGS_DDL_HIST_ALT" 123 rows
. . imported "WAILON"."GGS_SETUP" 6 rows
. . imported "WAILON"."SALGRADE" 5 rows
. . imported "WAILON"."BONUS" 0 rows
. . imported "WAILON"."CHECKPOINT_LOX" 0 rows
. . imported "WAILON"."GGS_DDL_COLUMNS" 0 rows
. . imported "WAILON"."GGS_DDL_LOG_GROUPS" 0 rows
. . imported "WAILON"."GGS_DDL_OBJECTS" 0 rows
. . imported "WAILON"."GGS_DDL_PARTITIONS" 0 rows
. . imported "WAILON"."GGS_DDL_PRIMARY_KEYS" 0 rows
. . imported "WAILON"."GGS_DDL_RULES" 0 rows
. . imported "WAILON"."GGS_DDL_RULES_LOG" 0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_SCHEMA_01" completed with 0 error(s) at 02:08:57
-- 檢查是否匯入成功
[oracle@dg dbbackup(02:09:03)]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 29 02:09:26 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
02:09:26 > alter user wailon identified by wailon;
User altered.
02:09:43 > conn wailon/wailon
Connected.
02:09:47 > select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
CHECKPOINT TABLE
CHECKPOINT_LOX TABLE
DEPT TABLE
EMP TABLE
GGS_DDL_COLUMNS TABLE
GGS_DDL_HIST TABLE
GGS_DDL_HIST_ALT TABLE
GGS_DDL_LOG_GROUPS TABLE
GGS_DDL_OBJECTS TABLE
GGS_DDL_PARTITIONS TABLE
GGS_DDL_PRIMARY_KEYS TABLE
GGS_DDL_RULES TABLE
GGS_DDL_RULES_LOG TABLE
GGS_MARKER TABLE
GGS_SETUP TABLE
GGS_STICK TABLE
GGS_TEMP_COLS TABLE
GGS_TEMP_UK TABLE
SALGRADE TABLE
20 rows selected.
02:09:51 > select tablespace_name,table_name from dba_tables where owner='WAILON';
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
WAILON GGS_SETUP
WAILON GGS_DDL_HIST_ALT
WAILON GGS_MARKER
WAILON GGS_DDL_RULES
WAILON GGS_DDL_RULES_LOG
WAILON CHECKPOINT
WAILON CHECKPOINT_LOX
WAILON DEPT
WAILON EMP
WAILON BONUS
WAILON SALGRADE
WAILON GGS_DDL_HIST
WAILON GGS_DDL_COLUMNS
WAILON GGS_DDL_LOG_GROUPS
WAILON GGS_DDL_PARTITIONS
WAILON GGS_DDL_PRIMARY_KEYS
WAILON GGS_DDL_OBJECTS
GGS_TEMP_UK
GGS_STICK
GGS_TEMP_COLS
20 rows selected.
02:10:09 > set long 4000
02:10:50 > select dbms_metadata.get_ddl('TABLE','GGS_TEMP_UK') FROM dual;
DBMS_METADATA.GET_DDL('TABLE','GGS_TEMP_UK')
--------------------------------------------------------------------------------
CREATE GLOBAL TEMPORARY TABLE "WAILON"."GGS_TEMP_UK"
( "SEQNO" NUMBER NOT NULL ENABLE,
"KEYNAME" VARCHAR2(100),
"COLNAME" VARCHAR2(100),
"NULLABLE" NUMBER,
"VIRTUAL" NUMBER,
"UDT" NUMBER,
"ISSYS" NUMBER,
PRIMARY KEY ("SEQNO", "KEYNAME", "COLNAME") ENABLE
) ON COMMIT DELETE ROWS
-- 臨時表只是資料字典定義，只在使用時才會使用臨時表空間
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-2119070/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 通過EXPDP/IMPDP匯出匯入遠端資料倒本地
- 資料庫遠端檔案匯入資料庫
- oracle資料庫遠端不落地匯入本地資料庫Oracle資料庫
- 資料庫泵（expdp/impdp）匯入匯出流程資料庫
- 遠端資料庫AWR 匯入到本地分析資料庫
- expdp impdp 資料庫匯入匯出命令詳解資料庫
- Impdp資料泵匯入
- 然後再全庫匯入排除view資料庫在impdp匯入View資料庫
- 使用資料泵impdp匯入資料
- Oracle expdp/impdp匯出匯入命令及資料庫備份Oracle資料庫
- Oracle expdp/impdp匯入匯出備份資料庫指令碼Oracle資料庫指令碼
- 用exp/imp把遠端資料匯入到本地資料庫中資料庫
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- Oracle 19.3資料庫impdp匯入view時hang住Oracle資料庫View
- Oracle 28.6資料庫impdp匯入view時hang@11Oracle資料庫View
- 19c資料庫impdp匯入view時hang住資料庫View
- 10g 單機資料庫impdp 匯入RAC DB資料庫
- expdp與impdp全庫匯出匯入
- 資料泵IMPDP 匯入工具的使用
- expdp遠端匯出資料
- IMPDP 高版本DUMP檔案匯入低版本資料庫資料庫
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- 資料泵在本地匯出資料到遠端資料庫中資料庫
- expdp與impdp全庫匯出匯入（二）
- impdp匯入資料後,system 表空間整理
- Oracle 遠端匯出匯入 imp/expOracle
- 使用EXPDP/IMPDP匯入匯出表中資料/後設資料測試
- 資料庫 MySQL 資料匯入匯出資料庫MySql
- 【IMPDP】使用IMPDP自動建立使用者並完成資料的匯入
- Oracle expdp資料泵遠端匯出Oracle
- 用impdp匯入資料的一次經歷
- rac環境下使用impdp匯入資料出錯
- ORACLE expdp匯出遠端庫指定使用者資料到本地資料庫Oracle資料庫
- 資料庫的匯入匯出資料庫
- mysql 資料庫匯入匯出MySql資料庫
- MySQL資料庫匯入匯出MySql資料庫
- 遠端登入server匯出linux下oracle資料庫中的資料並從本地下載ServerLinuxOracle資料庫
- 大文字資料，匯入匯出到資料庫資料庫