在異機上利用dblink做impdp資料匯入

xfhuangfu發表於2015-07-04
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.

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28373936/viewspace-1722299/,如需轉載,請註明出處,否則將追究法律責任。

相關文章