Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

IMPDP hangs but no error messages

744634Mar 4 2010 — edited Mar 10 2010

I have completed several schema level imports into a new 11.1.0.6 database on a new Windows Server 2003 64 bit server quad core with 16GB of RAM, but one schema gets to the "TABLE_DATA" stage and seems to hang, it never progresses but does not stop or display any error messages either.
The impdp output is below, that is as far as it gets, then stops. Plenty of tablespace available, no issues can be seen.
It is exported from an 11.1.0.6 database as well.

Import: Release 11.1.0.6.0 - 64bit Production on Wednesday, 03 March, 2010 22:55:31
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "USERBOB"."USERBOB_04MAR" successfully loaded/unloaded
Starting "USERBOB"."USERBOB_04MAR": USERBOB/******** directory=exportimport11g schemas=USERBOB dumpfile=USERBOB_04MAR_%u.dmp job_name=USERBOB_04MAR logfile=USERBOB_04MAR.log table_exists_action=REPLACE parallel=4
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"USERBOB" 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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

I left it running 10 hours overnight with no change, way longer than would be needed. Also tried a second export just in case the first was corrupt.
When I use the impdp status command, the output looks fine as shown below, with state=EXECUTING and error count=0.
Import> status

Job: USERBOB_imp3_03MAR2010
Operation: IMPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 4
Job Error Count: 0
Dump File: G:\exportimport11g\USERBOB_04MAR_%u.dmp
Dump File: G:\exportimport11g\USERBOB_04MAR_01.dmp
Dump File: G:\exportimport11g\USERBOB_04MAR_02.dmp
Dump File: G:\exportimport11g\USERBOB_04MAR_03.dmp
Dump File: G:\exportimport11g\USERBOB_04MAR_04.dmp
Dump File: G:\exportimport11g\USERBOB_04MAR_05.dmp

Worker 1 Status:
Process Name: DW01
State: EXECUTING
Object Schema: USERBOB
Object Name: SCHEDULER$_JOB_ARG
Object Type: SCHEMA_EXPORT/TABLE/TABLE
Completed Objects: 456
Total Objects: 457
Worker Parallelism: 1

I cannot figure out whats wrong. I can see the table definitions get created when I look at the user's schema in sql developer, but the tables don't have any data in them, so I eventually killed the job.
I may try an import with EXCLUDE=statistics,indexes as my next best guess. Any help appreciated.

Edited by: user12152237 on Mar 4, 2010 7:07 PM