dblink test 2


1.dblink可以用来把远端link的数据库中的某个用户下的schema同步到本地数据库中
同时,在dba_users中会自动创建你指定的数据库用户名称,测试开始

创建dblink:
SQL> create public database link linkto123 connect to scott identified by tiger
2 using ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.123)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))’;
//创建完毕dblink之后,记得要把scott解锁,否则后面会遇到很多错误信息,减少troubleshooting的时间

开始impdp导入测试:

[oracle@r~]$ impdp scott/tiger logfile=scott_remap.log schemas=scott remap_schema=scott:scott2 network_link=linkto123

Import: Release 10.2.0.4.0 – Production on Tuesday, 18 January, 2011 17:43:24

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SCOTT”.”SYS_IMPORT_SCHEMA_01″: scott/******** logfile=scott_remap.log schemas=scott remap_schema=scott:scott2 network_link=linkto123
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
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 “SCOTT2″.”DEPT” 4 rows
. . imported “SCOTT2″.”EMP” 14 rows
. . imported “SCOTT2″.”SALGRADE” 5 rows
. . imported “SCOTT2″.”BONUS” 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job “SCOTT”.”SYS_IMPORT_SCHEMA_01″ successfully completed at 17:44:02
//导入了很原始的scott中的四张表,我本地的数据库中scott大概有几百张表,linkto123远端的oracle中,scott没有任何变更过,我这个时候
很好奇是否会在dba_users中见到scott2????,查看一下:
SQL> select username from dba_users where account_status=’OPEN’;

USERNAME
——————————
SYSTEM
SYS
CM
SYSMAN
SCOTT
DBSNMP
MGMT_VIEW
SCOTT2

8 rows selected.
//竟然有scott2这个是在remap_schema的时候,自己创建的,而不是create user这样显示创建的。。。还不错,为以后的数据迁移提供了更多的选择。
我在想如果我这个dblink是连着自己身的,如果再做一次会有什么效果???