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

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 sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 4 10:26:45 2013Copyright （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 optionsSQL> 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
-
XSQL> show user
USER is "ZWC"
SQL> select tname from tab;no rows selectedSQL> select tname from tab@zwc;TNAME
------------------------------
T_ZHONGWCSQL> 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（*）
----------
75453SQL> 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:zwcImport: Release 11.2.0.3.0 - Production on Mon Feb 4 10:47:45 2013Copyright （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--------------------------------------------------------------------------------------验证
[plain] view plaincopyprint？
[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 更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12Oracle 4个大对象（lobs）数据类型介绍CentOS 下修改/dev/shm 大小解决ORA-00845相关资讯 DBLINK impdp 创建DBlink

Oracle dblink配置（今 07:31）
过度使用DBLINK做系统集成会带来的（07月27日）
利用Oracle自带的impdp和expdp进行（05月18日）

expdp与impdp参数用法（08月03日）
impdp的TABLE_EXISTS_ACTION参数选（05月28日）
Oracle impdp的skip_constraint_ （03月29日）

本文评论查看全部评论（0）