在異機上利用dblink做impdp資料匯入
oracle10g開始，增加了impdp、expdp功能。這幾天用dbblink做了一下異機上的資料匯入，
這樣就節省了資料匯出（expdp），然後再匯入的過程。
實驗環境：
OS: rhel4as4
DB：oracle10gr2
源庫：192.168.1.200 devdb 在源庫（192.168.1.200）上建立了tt表
目的庫：192.168.1.10 proddb
實驗目的：
在目的庫（10）上建立到源庫（200）的dblink
在目的庫（200）上執行impdp操作，將源庫（10）中scott使用者下的所有物件都匯入到目的庫（200）上
兩個庫的tnsname.ora 及listen.ora如下：
源庫：192.168.1.200 devdb 上：
[oracle@sphost admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/10.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORA10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10)
)
)
ORA10HHA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10hha)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
[oracle@sphost admin]$
[oracle@sphost admin]$
[oracle@sphost admin]$ more listener.ora
# listener.ora Network Configuration File: /home/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/10.2.0)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sphost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
[oracle@sphost admin]$
目的庫192.168.1.10 proddb上
[oracle@catalog admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/10.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORA10HHA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10hha)
)
)
ORA10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
[oracle@catalog admin]$
[oracle@catalog admin]$
[oracle@catalog admin]$ more listener.ora
# listener.ora Network Configuration File: /home/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/10.2.0)
(PROGRAM = extproc)
)
)
ora10hha =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
[oracle@catalog admin]$
實驗步驟：
在目的庫192.168.1.10 proddb上
以sysdba身份登入資料庫，給scott使用者授予建立dblink的許可權
[oracle@catalog ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 15:49:03 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> grant create database link to scott;
Grant succeeded.
在目的庫192.168.1.10 proddb上建立一個到源庫的dblink
SQL> create database link todevdb200 connect to scott identified by tiger using 'ORA10';
Database link created.
SQL> select count(*) from tt@todevdb200;
COUNT(*)
----------
199272
SQL>
能夠查到次資訊，表示dblink建立完畢
源庫：192.168.1.200 devdb 有一張表tt
現在想把源庫200中scott使用者下的資料庫匯入到目的庫10上
於是在目的庫10上執行如下操作：
首先檢視目標庫(10)中的目錄：
[oracle@catalog ~]$ sqlplus system/test as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 16:06:40 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> column owner format a10;
SQL> column db_link format a15;
SQL> column host format a10;
SQL> column username format a8;
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
---------- --------------- -------- ---------- ------------
SCOTT TODEV SCOTT ORA10 16-MAY-11
oracle@catalog ~]$ impdp scott/tiger schemas=SCOTT network_link=TODEV;
日誌如下：
[oracle@catalog ~]$ impdp scott/tiger schemas=SCOTT network_link=TODEV;
Import: Release 10.2.0.1.0 - Production on Tuesday, 17 May, 2011 0:13:25
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_IMPORT_SCHEMA_01": scott/******** schemas=SCOTT network_link=TODEV
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 22.43 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
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
ORA-39151: Table "SCOTT"."DEPT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."BONUS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."SALGRADE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
. . imported "SCOTT"."TT" 199272 rows
. . imported "SCOTT"."SYS_EXPORT_SCHEMA_01" 1057 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/TABLE/COMMENT
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 5 error(s) at 00:15:04
可以看出，已經匯入了tt表，為了驗證
目的庫192.168.1.10 proddb上執行
SQL> select count(*) from tt@todev;
COUNT(*)
----------
199272
SQL> select count(*) from tt;
COUNT(*)
----------
199272
SQL>
可以看出，目的庫中已經成功匯入了tt表。
注：如果要刪除dblink，執行如下命令：
SQL> drop database link todev;
Database link dropped.
這樣就節省了資料匯出（expdp），然後再匯入的過程。
實驗環境：
OS: rhel4as4
DB：oracle10gr2
源庫：192.168.1.200 devdb 在源庫（192.168.1.200）上建立了tt表
目的庫：192.168.1.10 proddb
實驗目的：
在目的庫（10）上建立到源庫（200）的dblink
在目的庫（200）上執行impdp操作，將源庫（10）中scott使用者下的所有物件都匯入到目的庫（200）上
兩個庫的tnsname.ora 及listen.ora如下：
源庫：192.168.1.200 devdb 上：
[oracle@sphost admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/10.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORA10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10)
)
)
ORA10HHA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10hha)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
[oracle@sphost admin]$
[oracle@sphost admin]$
[oracle@sphost admin]$ more listener.ora
# listener.ora Network Configuration File: /home/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/10.2.0)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sphost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
[oracle@sphost admin]$
目的庫192.168.1.10 proddb上
[oracle@catalog admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/10.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORA10HHA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10hha)
)
)
ORA10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
[oracle@catalog admin]$
[oracle@catalog admin]$
[oracle@catalog admin]$ more listener.ora
# listener.ora Network Configuration File: /home/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/10.2.0)
(PROGRAM = extproc)
)
)
ora10hha =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
[oracle@catalog admin]$
實驗步驟：
在目的庫192.168.1.10 proddb上
以sysdba身份登入資料庫，給scott使用者授予建立dblink的許可權
[oracle@catalog ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 15:49:03 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> grant create database link to scott;
Grant succeeded.
在目的庫192.168.1.10 proddb上建立一個到源庫的dblink
SQL> create database link todevdb200 connect to scott identified by tiger using 'ORA10';
Database link created.
SQL> select count(*) from tt@todevdb200;
COUNT(*)
----------
199272
SQL>
能夠查到次資訊，表示dblink建立完畢
源庫：192.168.1.200 devdb 有一張表tt
現在想把源庫200中scott使用者下的資料庫匯入到目的庫10上
於是在目的庫10上執行如下操作：
首先檢視目標庫(10)中的目錄：
[oracle@catalog ~]$ sqlplus system/test as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 16:06:40 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> column owner format a10;
SQL> column db_link format a15;
SQL> column host format a10;
SQL> column username format a8;
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
---------- --------------- -------- ---------- ------------
SCOTT TODEV SCOTT ORA10 16-MAY-11
oracle@catalog ~]$ impdp scott/tiger schemas=SCOTT network_link=TODEV;
日誌如下：
[oracle@catalog ~]$ impdp scott/tiger schemas=SCOTT network_link=TODEV;
Import: Release 10.2.0.1.0 - Production on Tuesday, 17 May, 2011 0:13:25
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_IMPORT_SCHEMA_01": scott/******** schemas=SCOTT network_link=TODEV
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 22.43 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
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
ORA-39151: Table "SCOTT"."DEPT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."BONUS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."SALGRADE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
. . imported "SCOTT"."TT" 199272 rows
. . imported "SCOTT"."SYS_EXPORT_SCHEMA_01" 1057 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/TABLE/COMMENT
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 5 error(s) at 00:15:04
可以看出，已經匯入了tt表，為了驗證
目的庫192.168.1.10 proddb上執行
SQL> select count(*) from tt@todev;
COUNT(*)
----------
199272
SQL> select count(*) from tt;
COUNT(*)
----------
199272
SQL>
可以看出，目的庫中已經成功匯入了tt表。
注：如果要刪除dblink，執行如下命令：
SQL> drop database link todev;
Database link dropped.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28373936/viewspace-1722299/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Impdp資料泵匯入
- oracle dblink用法總結和expdp和impdp利用dblink倒入匯出到本地Oracle
- 使用資料泵impdp匯入資料
- 然後再全庫匯入排除view資料庫在impdp匯入View資料庫
- Oracle使用資料泵在異機之間匯出匯入表Oracle
- impdp做資料匯入時約束和觸發器引起資料匯入後應用故障觸發器
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- IMPDP匯入遠端資料庫資料庫
- Oracle使用資料泵在異機之間匯出匯入多個 schemaOracle
- 資料庫泵（expdp/impdp）匯入匯出流程資料庫
- 10g 單機資料庫impdp 匯入RAC DB資料庫
- 資料泵IMPDP 匯入工具的使用
- expdp impdp 資料庫匯入匯出命令詳解資料庫
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- impdp匯入資料後,system 表空間整理
- Oracle expdp/impdp匯出匯入命令及資料庫備份Oracle資料庫
- 通過EXPDP/IMPDP匯出匯入遠端資料倒本地
- Oracle expdp/impdp匯入匯出備份資料庫指令碼Oracle資料庫指令碼
- 使用EXPDP/IMPDP匯入匯出表中資料/後設資料測試
- 使用impdp命令在不同例項間透過dblink同步資料庫資料庫
- 【IMPDP】使用IMPDP自動建立使用者並完成資料的匯入
- 用impdp匯入資料的一次經歷
- rac環境下使用impdp匯入資料出錯
- impdp 匯入 指令碼指令碼
- 【EXPDP/IMPDP】使用 EXPDP/IMPDP工具“模糊”匯出和匯入
- RAC建立DBlink並使用impdp抽取源庫資料
- Oracle資料庫遷移之三：dblink+impdpOracle資料庫
- SQLServer 異構資料庫之間資料的匯入匯出SQLServer資料庫
- Oracle 11g impdp 先資料後索引匯入方法Oracle索引
- Oracle 19.3資料庫impdp匯入view時hang住Oracle資料庫View
- Oracle 28.6資料庫impdp匯入view時hang@11Oracle資料庫View
- 19c資料庫impdp匯入view時hang住資料庫View
- Oracle IMPDP匯入資料案例之注意事項（undo/temp）Oracle
- oracle匯入匯出之expdp/impdpOracle
- expdp impdp只匯出匯入viewView
- 在SQL Server資料庫中匯入匯出資料SQLServer資料庫
- 【impdp】使用impdp工具排除特定表的匯入
- Sqoop匯入資料異常處理OOP