RAC creates a DBlink and uses impdp to extract data from the source database.

RAC creates a DBlink and uses impdp to extract data from the source database. Then it creates a dblink [plain] viewplaincopyprint? [Oracle @ zhongwc1 ~] $ Sqlplusassysd

RAC creates a DBlink and uses impdp to extract data from the source database. Then it creates a dblink [plain] view plaincopyprint? [Oracle @ zhongwc1 ~] $ Sqlplus/as sysd

RAC creates a DBlink and uses impdp to extract data from the source database.

Grant permissions and create 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,201 1, Oracle. All rights reserved.


Connected:
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,201 1, Oracle. All rights reserved.

Connected:
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

Bytes --------------------------------------------------------------------------------------

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,201 1, 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
.. 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