赋权并创建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, 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
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