dblink test3


说明:都知道dblink是连接远端数据库,访问远端数据库中表的一种便利方式,试想如果创建了一个dblink这个link是连接自身的,会有什么效果?
然后测试把一个用户下的schema迁移至,或者复制到另外一个预先创建的用户下

//先创建一个指向自己的dblink
create public database link linktoself connect to scott identified by tiger
using ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.128)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = test)))’

//在os上开始remap_schema的操作
zhangsan这个用户是我自己手工创建的,在impdp的时候 “remap_schema=scott:zhangsan”,我想即使我不手工创建,后续过程impdp时会自动帮我加上这个用户,当然
如果已经有了,在log中肯定有一个条类似”ORA-31684: Object type USER:”ZHANGSAN” already exists”的错误

[oracle@rhel5u4test ~]$ impdp scott/tiger directory=test logfile=scott_zhangsan_remap.log schemas=scott remap_schema=scott:zhangsan network_link=linktoself

Import: Release 10.2.0.4.0 – Production on Wednesday, 19 January, 2011 13:11:47

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/******** directory=test logfile=scott_zhangsan_remap.log schemas=scott remap_schema=scott:zhangsan network_link=linktoself
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 94.68 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”ZHANGSAN” 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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-31679: Table data object “ZHANGSAN”.”NC_JMSQUEUE” has long columns, and longs can not be loaded/unloaded using a network link
. . imported “ZHANGSAN”.”CMOBJPROPS7″ 1017 rows
. . imported “ZHANGSAN”.”CMDATA” 103 rows
. . imported “ZHANGSAN”.”CMOBJPROPS26″ 592 rows
. . imported “ZHANGSAN”.”CMOBJNAMES_BASE” 21652 rows
. . imported “ZHANGSAN”.”CMUNIQUENAMES” 18331 rows
. . imported “ZHANGSAN”.”CMOBJPROPS25″ 8 rows
. . imported “ZHANGSAN”.”CMOBJPROPS30″ 13 rows
. . imported “ZHANGSAN”.”CMOBJPROPS14″ 1330 rows
. . imported “ZHANGSAN”.”CMPOLICIES” 3352 rows
. . imported “ZHANGSAN”.”CMCLASSES” 145 rows
. . imported “ZHANGSAN”.”CMOBJPROPS13″ 653 rows

//不过在上述的整个过程中,除了意料之中的error意外,还有几个需要注意的:
从”ORA-31679: Table data object “ZHANGSAN”.”NC_JMSQUEUE” has long columns, and longs can not be loaded/unloaded using a network link”
中可以看出,network link对于long类型的支持,是存在问题的,所以在做数据迁移的时候需要额外的小心了。。