ORA-30926 while using impdp over a database link

Posted in: Oracle, Technical Track


The other day I was doing an import using impdp in 12.1.0.2, and received the following error:’ORA-30926: unable to get a stable set of rows in the source tables’.


Starting "SYS_PYTHIAN"."SYS_IMPORT_TABLE_03":  sys_pythian/******** parfile=table_import.par 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.STATS_LOAD [MARKER] 
MARKER
ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 11265
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x12f199188     27116  package body SYS.KUPW$WORKER
0x12f199188     11286  package body SYS.KUPW$WORKER
0x12f199188     24286  package body SYS.KUPW$WORKER
0x12f199188     24415  package body SYS.KUPW$WORKER
0x12f199188     20692  package body SYS.KUPW$WORKER
0x12f199188     10206  package body SYS.KUPW$WORKER
0x12f199188     13381  package body SYS.KUPW$WORKER
0x12f199188      3173  package body SYS.KUPW$WORKER
0x12f199188     12035  package body SYS.KUPW$WORKER
0x12f199188      2081  package body SYS.KUPW$WORKER
0x12ffe7908         2  anonymous block
In STATS_UNLOAD
DBMS_STATS.EXPORT_STATS_FOR_DP
DBMS_STATS.EXPORT_STATS_FOR_DP
In STATS_LOAD with process_order 39
Fixing up the name in the impdp stat table
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.STATS_LOAD [MARKER] 
ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 11259
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x6fc030c8     27116  package body SYS.KUPW$WORKER
0x6fc030c8     11286  package body SYS.KUPW$WORKER
0x6fc030c8     24286  package body SYS.KUPW$WORKER
0x6fc030c8     24415  package body SYS.KUPW$WORKER
0x6fc030c8     10105  package body SYS.KUPW$WORKER
0x6fc030c8     13381  package body SYS.KUPW$WORKER
0x6fc030c8      3173  package body SYS.KUPW$WORKER
0x6fc030c8     12035  package body SYS.KUPW$WORKER
0x6fc030c8      2081  package body SYS.KUPW$WORKER
0x134b42cf8         2  anonymous block
In STATS_UNLOAD
DBMS_STATS.EXPORT_STATS_FOR_DP
DBMS_STATS.EXPORT_STATS_FOR_DP
In STATS_LOAD with process_order 39
Fixing up the name in the impdp stat table
Job "SYS_PYTHIAN"."SYS_IMPORT_TABLE_03" stopped due to fatal error at Thu Jan 19 13:47:30 2017 elapsed 0 00:01:58

While investigating the situation, I found that the source database had OLAP option removed and found a document 1353491.1 (that I thought could help) which stated that if the package DBMS_CUBE_EXP is present in SYS.EXPPKGACT$, and it does not exist, it should be deleted. So that’s what I did:


SQL> SELECT comp_id, status, SUBSTR(version,1,10) version, comp_name 
FROM dba_registry ORDER BY 1;

COMP_ID STATUS VERSION COMP_NAME
------------------------------ ----------- ---------- --------------------------------------------

CATALOG VALID 12.1.0.2.0 Oracle Database Catalog Views

CATPROC VALID 12.1.0.2.0 Oracle Database Packages and Types

XDB VALID 12.1.0.2.0 Oracle XML Database

SQL> select * 
FROM SYS.EXPPKGACT$ 
WHERE PACKAGE = 'DBMS_CUBE_EXP' AND SCHEMA= 'SYS';

PACKAGE SCHEMA CLASS LEVEL#

----------------- ---------- ---------- ----------
DBMS_CUBE_EXP SYS 4 1050

SQL> select PACKAGE, SCHEMA, class from exppkgact$ where (schema, package) 
not in 
(select owner, object_name 
from dba_objects 
where object_type='PACKAGE');

PACKAGE SCHEMA CLASS
-------------------------- ---------- ---------- 
DBMS_CUBE_EXP SYS 4

SQL> create table exppkgact$_bck as select * from exppkgact$;

Table created.

SQL> delete from exppkgact$ where package = 'DBMS_CUBE_EXP' and schema = 'SYS';

1 row deleted.

SQL> commit;

Commit complete.

But I still got the same error. After conducting more research, I found a workaround to run the import without stats and once I did that, I was able to run the import successfully. Since this was on a development database, I just ran stats on the table, but if you needed to you could export your stats and import them, in this case I only excluded the stats:

[oracle@ ~ ]$ cat table_import.par
tables=TEST.TEST_TABLE
directory=export_dir_pythian 
logfile=impdp.log
network_link=PYTHIAN
TABLE_EXISTS_ACTION=replace
EXCLUDE=STATISTICS
STATUS=120

[oracle@ ~ ]$ cat impdp.log
;;; 
Import: Release 12.1.0.2.0 - Production on Thu Jan 19 13:49:57 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
;;; 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Starting "SYS_PYTHIAN"."SYS_IMPORT_TABLE_04": sys_pythian/******** parfile=table_import.par 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . imported "TEST"."TEST_TABLE" 222224 rows
Job "SYS_PYTHIAN"."SYS_IMPORT_TABLE_04" successfully completed at Thu Jan 19 13:50:07 2017 elapsed 0 00:00:07

Conclusion

I hope this workaround helps you in the event that you ever face this error while doing an impdp over a database link.

This was originally posted in rene-ace.com

email

Author

Interested in working with Rene? Schedule a tech call.

About the Author

Currently I am an Oracle ACE ; Speaker at Oracle Open World, Oracle Developers Day, OTN Tour Latin America and APAC region and IOUG Collaborate ; Co-President of ORAMEX (Mexico Oracle User Group); At the moment I am an Oracle Project Engineer at Pythian. In my free time I like to say that I'm Movie Fanatic, Music Lover and bringing the best from México (Mexihtli) to the rest of the world and in the process photographing it ;)

1 Comment. Leave new

Hi Rene,

Interesting blog, I have never seen this.

As per note 2162936.1 this happens due to stale statistics.

It may be interesting to see the actual failing statement.

SQL> alter system set events ‘30926 trace name errorstack level 3’;

Then after the trace produced:

SQL> alter system set events ‘30926 trace name errorstack off’;

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *