RAC建立DBlink並使用impdp抽取源庫資料

dawn009發表於2014-03-14
轉載於--&gt>
賦權並建立dblink
[oracle@zhongwc1 ~]$ sqlplus / as sysdba  
  
SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 4 10:26:45 2013  
  
Copyright (c) 1982, 2011, .  All rights reserved.  
  
  
Connected to:  
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,  
Data Mining and Real Application Testing options  
  
SQL> grant create database link to zwc;  
  
Grant succeeded.  
  
SQL> conn zwc/  
Enter password:   
Connected.  
SQL> show user  
USER is "ZWC"  
SQL> select tname from tab;  
  
no rows selected  
create public database link ZWC  
  connect to zhongwc identified by zhongwc  
  using '(DESCRIPTION =  
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))  
    (CONNECT_DATA =  
      (SERVER = DEDICATED)  
      (SERVICE_NAME = ZWC)  
    )  
  9    )';  
  
Database link created.  
  
SQL> select * from dual@zwc;  
  
D  
-  
X  
  
SQL> show user  
USER is "ZWC"  
SQL> select tname from tab;  
  
no rows selected  
  
SQL> select tname from tab@zwc;  
  
TNAME  
------------------------------  
T_ZHONGWC  
  
SQL> select count(*) from t_zhongwc;  
select count(*) from t_zhongwc  
                     *  
ERROR at line 1:  
ORA-00942: table or view does not exist  
  
  
SQL> select count(*) from t_zhongwc@zwc;  
  
  COUNT(*)  
----------  
     75453  
  
SQL> exit  
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,  
Data Mining and Real Application Testing options  
[oracle@zhongwc1 ~]$ impdp system/oracle network_link=zwc schemas=zhongwc remap_schema=zhongwc:zwc  
  
Import: Release 11.2.0.3.0 - Production on Mon Feb 4 10:47:45 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, Real Application Clusters, Automatic Storage Management, OLAP,  
Data Mining and Real Application Testing options  
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** network_link=zwc schemas=zhongwc remap_schema=zhongwc:zwc   
Estimate in progress using BLOCKS method...  
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA  
Total estimation using BLOCKS method: 9 MB  
Processing object type SCHEMA_EXPORT/USER  
ORA-31684: Object type USER:"ZWC" 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  
. . imported "ZWC"."T_ZHONGWC"                            75453 rows  
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 10:48:32  
 
驗證
[oracle@zhongwc1 ~]$ sqlplus zwc  
  
SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 4 10:50:09 2013  
  
Copyright (c) 1982, 2011, Oracle.  All rights reserved.  
  
Enter password:   
  
Connected to:  
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,  
Data Mining and Real Application Testing options  
  
SQL> select count(*) from t_zhongwc@zwc;  
  
  COUNT(*)  
----------  
     75453  
  
SQL> select count(*) from t_zhongwc;  
  
  COUNT(*)  
----------  
     75453  
  
SQL> drop public database link zwc;  
  
Database link dropped.  
  
SQL> select count(*) from t_zhongwc@zwc;  
select count(*) from t_zhongwc@zwc  
                               *  
ERROR at line 1:  
ORA-02019: connection description for remote database not found  
  
  
SQL> select count(*) from t_zhongwc;  
  
  COUNT(*)  
----------  
     75453  

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

相關文章