RAC创建DBlink并使用impdp抽取源库数据

RAC创建DBlink并使用impdp抽取源库数据 赋权并创建dblink [plain] view plaincopyprint?[Oracle@zhongwc1 ~]$ sqlplus / as sysd

RAC创建DBlink并使用impdp抽取源库数据

赋权并创建dblink

[plain] view plaincopyprint?
[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, Oracle.  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 

[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, Oracle.  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[plain] view plaincopyprint?
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 

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