Can you confirm whether you are running the expdp command from Server end or Client level end. I remember an instance where the DB server was 11g and my client was running on 10g and it hung too, if this is the same with you try to expdp from Server end.
Also please refer following note and check if it helps you:
Expdp Hangs During Exporting Data Due to a Lock Situation [ID 756834.1]
Data Pump Hanging When Exporting To NFS Location [ID 434508.1]
I'm running the expdp command from Server so there should no issue with version different.
The strange thing here is that I have 2 instances DEVT and TEST where both have the same tables structure. In the TEST users are testing so they key-in a lot of data, while in the DEVT the data is static. The hanging of expdp only happens in the TEST.
In the Doc ID 786165.1 it said for expdp the value for parameter ESTIMATE is either BLOCKS (default) or STATISTICS, where for STATISTICS, all tables should have been analyzed recently. It means the default ESTIMATE = BLOCKS, no need to analyze all tables before using expdp with FULL=y,
Do I need to analyze all tables in the TEST using expdp with FULL=y and ESTIMATE = BLOCKS?
Thanks in advance.
I have add the parameter EXCLUDE=STATISTICS and then try again. The expdp still hangs with:
Connected to: Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** DIRECTORY=xxx_dir DUMPFILE=xxx.dmp FULL=y EXCLUDE=STATISTICS
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 227.5 MB
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
and it hangs.
select * from dba_datapump_sessions;
OWNER_NAME JOB_NAME INST_ID SADDR SESSION_TYPE
SYSTEM SYS_EXPORT_FULL_01 1 000000015F852538 DBMS_DATAPUMP
SYSTEM SYS_EXPORT_FULL_01 1 000000015F62B4D0 MASTER
SYSTEM SYS_EXPORT_FULL_01 1 000000015F6F8D18 WORKER
select * from dba_datapump_jobs;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 1 1 3
is your database is AMM enable,
What is the value for STREAMS_POOL_SIZE,
Have you tried with CONTENT=METADATA_ONLY is completed successfully. or try to export excluding the schemas SYS and SYSTEM.
you can also check the metalink id
DataPump Export (EXPDP) Hangs When Exporting Referential Constraints [ID 1368586.1]
Yes, the expdp with CONTENT=METADATA_ONLY can go through without any error.
Here is the STREAMS_POOL_SIZE and other in my database:
select * from v$sgainfo;
NAME BYTES RESIZEABLE
Fixed SGA Size 2235208 No
Redo Buffers 12132352 No
Buffer Cache Size 1627389952 Yes
Shared Pool Size 2566914048 Yes
Large Pool Size 16777216 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 33554432 Yes
Shared IO Pool Size 33554432 Yes
Granule Size 16777216 No
Maximum SGA Size 4275781632 No
Startup overhead in Shared Pool 125798072 No
Free SGA Memory Available 0
Any idea how to fix this?
Do you have any Table with LONG columns ?
To check that, you may use the following query:
select owner, table_name, column_name
where data_type like 'LONG%';
If this is the case, then you may use the parameter ACCESS_METHOD as showed in the Note below:
DataPump Export (EXPDP) Is Hanging While Exporting A Table With LONG Column [ID 813396.1]
Else, what you've noticed between TEST and DEV is interesting.
Do you have the same memory parameters between the 2 Databases ?
If the users modify continuously the Data in the Database TEST then, you may use the following parameter during the Export:
FLASHBACK_TIME=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\"
Hope this help,
Message was edited by: LubiezJean-Valentin
This is a great discussion. I'm having the same issue, a Datapump export that hangs. Mine hangs on the same table every time. I have tried the status, and the Note ID 1368586.1 referential constraint change. None work. It's not locks, either, as I have tried it without any locks.
Still a great discussion. and now I get a status every 15 seconds, so that's progress. It looks like this:
Bytes Processed: 20,425,888
Percent Done: 88
Current Parallelism: 1
Job Error Count: 0
Dump File: /usr/local/oracle/admin/gp7/dpdump/130814acurup.dmp
bytes written: 20,963,328
Worker 1 Status:
Object Schema: AC_URUP
Object Name: AC_SCHEDULE
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 28
Total Objects: 47
Worker Parallelism: 1
It hangs on the same table every time. Very strange. Keep up the discussion please.