首页 » ORACLE 9i-23ai » impdp network_link 在11201中导入分区表时行记录为0 rows?
impdp network_link 在11201中导入分区表时行记录为0 rows?
impdp,expdp 可以使用network_link参数 在不用指定directory 也不用生成dump 文件的方式传送数据
今天群里有个同学遇到一个奇怪的问题
“impdp 用了network_link 但是schema下的其它表都可以导入，只有几张表导入时元数据可以导入就是无数据记录（0 row）,而且没有任何错误”
下面我来还原那个问题
database version 11201 single instance, OS version OEL5.8,我是在一个实例的两个schema来模仿
1,TARGET DATABASE ,Configuration TNSNAMES.ora FILE [oracle@rac1 ~]$ tnsping anbob TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 06-AUG-2012 20:35:45 Copyright (c) 1997, 2009, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = anbob.com))) OK (480 msec) 2,source database,create a test data table(hash partition table) anbob@ANBOB>create table hash_obj(id int,name varchar2(20),ctime date) 2 partition by hash(id) 3 partitions 6; Table created. anbob@ANBOB>insert into hash_obj select rownum,'r'||rownum ,sysdate 2 from dual connect by rownum <10000; 9999 rows created. anbob@ANBOB>commit; 3,TARGET DATABASE ,CREATE PUBLIC DATABASE LINK sys@ANBOB>create public database link dl_pub_ab connect to anbob identified by anbob using 'anbob'; Database link created. 4,TARGET DATABASE ,VERIFY DBLINK ,make sure target database can use dblink access export schema tables sys@ANBOB>select count(*) from anbob.obj@dl_pub_ab; COUNT(*) ---------- 10000 5,source database , privileges required sys@ANBOB>grant IMP_FULL_DATABASE to anbob; 6,source database , create user for import sys@ANBOB>create user weejar identified by weejar; User created. sys@ANBOB>grant connect,resource to weejar; Grant succeeded. 7,target database, import to weejar schema from anbob schema [oracle@rac1 ~]$ impdp system/oracle remap_schema=anbob:weejar schemas=anbob network_link=dl_pub_ab include=TABLE:\"like \'HASH/_%\' escape \'/\' \" Import: Release 11.2.0.1.0 - Production on Mon Aug 6 21:03:53 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** remap_schema=anbob:weejar schemas=anbob network_link=dl_pub_ab include=TABLE:"like 'HASH/_%' escape '/' " Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 512 KB Processing object type SCHEMA_EXPORT/TABLE/TABLE . . imported "WEEJAR"."HASH_OBJ":"SYS_P23" 2520 rows . . imported "WEEJAR"."HASH_OBJ":"SYS_P24" 2481 rows . . imported "WEEJAR"."HASH_OBJ":"SYS_P21" 1283 rows . . imported "WEEJAR"."HASH_OBJ":"SYS_P22" 1232 rows . . imported "WEEJAR"."HASH_OBJ":"SYS_P25" 1188 rows . . imported "WEEJAR"."HASH_OBJ":"SYS_P26" 1295 rows Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 21:05:00 anbob@ANBOB>select owner,SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE 2 from dba_objects where object_name='HASH_OBJ'; OWNER SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ------------------------------ ---------- -------------- ------------------- ANBOB SYS_P21 80205 80205 TABLE PARTITION ANBOB SYS_P22 80206 80206 TABLE PARTITION ANBOB SYS_P23 80207 80207 TABLE PARTITION ANBOB SYS_P24 80208 80208 TABLE PARTITION ANBOB SYS_P25 80209 80209 TABLE PARTITION ANBOB SYS_P26 80210 80210 TABLE PARTITION ANBOB 80204 TABLE WEEJAR SYS_P21 80426 80426 TABLE PARTITION WEEJAR SYS_P22 80427 80427 TABLE PARTITION WEEJAR SYS_P23 80428 80428 TABLE PARTITION WEEJAR SYS_P24 80429 80429 TABLE PARTITION WEEJAR SYS_P25 80430 80430 TABLE PARTITION WEEJAR SYS_P26 80431 80431 TABLE PARTITION WEEJAR 80425 TABLE 14 rows selected. note:object_id=data_object_id 以上就是正常的现象，现在来做一些操作，展现问题 anbob@ANBOB>truncate table hash_obj; Table truncated. anbob@ANBOB>select owner,SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE 2 from dba_objects where object_name='HASH_OBJ'; OWNER SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ------------------------------ ---------- -------------- ------------------- ANBOB 80204 TABLE ANBOB SYS_P26 80210 80444 TABLE PARTITION ANBOB SYS_P25 80209 80443 TABLE PARTITION ANBOB SYS_P24 80208 80442 TABLE PARTITION ANBOB SYS_P23 80207 80441 TABLE PARTITION ANBOB SYS_P22 80206 80440 TABLE PARTITION ANBOB SYS_P21 80205 80439 TABLE PARTITION WEEJAR 80425 TABLE WEEJAR SYS_P26 80431 80431 TABLE PARTITION WEEJAR SYS_P25 80430 80430 TABLE PARTITION WEEJAR SYS_P24 80429 80429 TABLE PARTITION WEEJAR SYS_P23 80428 80428 TABLE PARTITION WEEJAR SYS_P22 80427 80427 TABLE PARTITION WEEJAR SYS_P21 80426 80426 TABLE PARTITION note: note:object_id<>data_object_id sys@ANBOB>conn weejar/weejar; Connected. weejar@ANBOB>drop table hash_obj purge; Table dropped. weejar@ANBOB>conn anbob/anbob Connected. anbob@ANBOB>insert into hash_obj select rownum,'r'||rownum ,sysdate 2 from dual connect by rownum <10000; 9999 rows created. anbob@ANBOB>commit; Commit complete. anbob@ANBOB>select count(*) from hash_obj; COUNT(*) ---------- 9999 [oracle@rac1 ~]$ impdp system/oracle remap_schema=anbob:weejar schemas=anbob network_link=dl_pub_ab include=TABLE:\"like \'HASH/_%\' escape \'/\' \" Import: Release 11.2.0.1.0 - Production on Mon Aug 6 23:08:55 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** remap_schema=anbob:weejar schemas=anbob network_link=dl_pub_ab include=TABLE:"like 'HASH/_%' escape '/' " Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 512 KB Processing object type SCHEMA_EXPORT/TABLE/TABLE . . imported "WEEJAR"."HASH_OBJ":"SYS_P23" 0 rows . . imported "WEEJAR"."HASH_OBJ":"SYS_P24" 0 rows . . imported "WEEJAR"."HASH_OBJ":"SYS_P21" 0 rows . . imported "WEEJAR"."HASH_OBJ":"SYS_P22" 0 rows . . imported "WEEJAR"."HASH_OBJ":"SYS_P25" 0 rows . . imported "WEEJAR"."HASH_OBJ":"SYS_P26" 0 rows Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 23:09:39 note: 明明有数据导入确是0 rows,对非分区表的truncate不影响 Cause： expdp,impdp 使用network_link 传输分区表 object_id<>data_object_id时，bug 8692663 Solution： 打补丁或升到11202以上，也可以创建一个临时非分区表代替 下面打补丁，重试 /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory Patch 8692663 : applied on Mon Aug 06 23:42:54 PDT 2012 Unique Patch ID: 13818158 Created on 7 Jun 2011, 06:12:17 hrs PST8PDT Bugs fixed: 8692663 这个补丁要关库更新一系列脚本，在我的配置超差的VM上跑了半个小时 [oracle@rac1 ~]$ impdp system/oracle remap_schema=anbob:weejar schemas=anbob network_link=dl_pub_ab include=TABLE:\"like \'HASH/_%\' escape \'/\' \" Import: Release 11.2.0.1.0 - Production on Tue Aug 7 00:34:39 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** remap_schema=anbob:weejar schemas=anbob network_link=dl_pub_ab include=TABLE:"like 'HASH/_%' escape '/' " Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 512 KB Processing object type SCHEMA_EXPORT/TABLE/TABLE . . imported "WEEJAR"."HASH_OBJ":"SYS_P23" 2520 rows . . imported "WEEJAR"."HASH_OBJ":"SYS_P24" 2481 rows . . imported "WEEJAR"."HASH_OBJ":"SYS_P21" 1283 rows . . imported "WEEJAR"."HASH_OBJ":"SYS_P22" 1232 rows . . imported "WEEJAR"."HASH_OBJ":"SYS_P25" 1188 rows . . imported "WEEJAR"."HASH_OBJ":"SYS_P26" 1295 rows Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 00:36:23 ok,finished!
微信扫一扫，打赏作者吧～
目前这篇文章有1条评论(Rss)评论关闭。