首页 » ORACLE » 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)评论关闭。

  1. 玉儿 | #1
    2012-08-07 at 16:47

    郁闷。我就是那个悲催的小同学!!!!!!!