OraFAQ Forum: Server Utilities » datapump Export taking long time(HUNG)

Home » RDBMS Server » Server Utilities » datapump Export taking long time(HUNG) (11gr2 LINUX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

datapump Export taking long time(HUNG) [message #564425]

Thu, 23 August 2012 11:46

chandu208
Messages: 17
Registered: July 2012

Junior Member

i'm doing full database export
Expdp directory=xxx.dmp dumpfile=aaa.dmp logfile=xxx.log FULL=Y
: :: : : :: : : : ;
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 24.87 MB
Processing object type SCHEMA_EXPORT/USER
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/DB_LINK

then my export hangs..... Sad

Sad

checked in alert log nothing found.

and then killed the job and reran again but same....checked the status and it's saying EXECUTING, dont know where i'm doing wrong, can anyone please help me out

thanks in advance

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564426 is a reply to message #564425]

Thu, 23 August 2012 11:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

determine Session ID of the export session
then periodically issue SQL below

SELECT * FROM V$SESS_IO WHERE SID = <export_session_ID>;

if/when one or more of the reported value is increasing, then export session is busying working
& you need to be patient

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564427 is a reply to message #564425]

Thu, 23 August 2012 12:17

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Exclude the db links and retry.
If it works then you are sure the problem is in the db links and then you could:
1/ Try each one
2/ Export in another way.

You could also try to export ONLY the db links.

Regards
Michel

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564435 is a reply to message #564427]

Thu, 23 August 2012 14:14

chandu208
Messages: 17
Registered: July 2012

Junior Member

Thank you all but its not only stopping at db links when i try other time it's stopping at some other place(ex: object grants)

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564436 is a reply to message #564435]

Thu, 23 August 2012 14:19

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So it is not stopping but working or waiting for something to be able to work.
You have to post more information for more help starting with BlackSwan query and the following one (still periodically):

select last_call_et, state, wait_time, second_in_waits, event
from v$session
where sid=<export_session_ID>
/

Regards
Michel

[Updated on: Thu, 23 August 2012 14:22]

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564439 is a reply to message #564436]

Thu, 23 August 2012 15:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Thank you all but its not only stopping at db links when i try other time it's stopping at some other place(ex: object grants)

you have a mystery & we have no clues.
Only YOU can provide insight as to what the Oracle engine is actually doing.

What is the approximate size of the FULL database that you are attempting to export?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564453 is a reply to message #564439]

Thu, 23 August 2012 23:59

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

What is the approximate size of the FULL database that you are attempting to export?

Quote:

Total estimation using BLOCKS method: 24.87 MB

Regards
Michel

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564507 is a reply to message #564453]

Fri, 24 August 2012 08:41

chandu208
Messages: 17
Registered: July 2012

Junior Member

Size of db is around 3.5GB

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564508 is a reply to message #564507]

Fri, 24 August 2012 08:59

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

But with 25MB of real data if Data Pump is correct.

Did you do what we asked you?

Regards
Michel

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564707 is a reply to message #564508]

Mon, 27 August 2012 14:00

Flyby
Messages: 188
Registered: March 2011
Location: Belgium

Senior Member

Somewhere along the Oracle10g there was a bug involving datapump hanging on ... Which version do you use? Usually you can see the waitstats of the datapump process(es)

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564712 is a reply to message #564707]

Mon, 27 August 2012 16:30

chandu208
Messages: 17
Registered: July 2012

Junior Member

I'm using 11.2.0.2.0 LINUX

again i started datapump export for multiple schemas

$ expdp dumpfile=dds_0827.dmp directory=EXPORT_DIR schemas=schema1,schema2
Export: Release 11.2.0.2.0 - Production on Mon Aug 27 20:30:48 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_03": /******** AS SYSDBA dumpfile=dds_0827.dmp directory=EXPORT_DIR schemas=schema1,schema2
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.757 GB
Processing object type SCHEMA_EXPORT/USER
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/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

just Hanging

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564713 is a reply to message #564712]

Mon, 27 August 2012 17:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

unless & until you post requested details, You're On Your Own (YOYO)!

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564714 is a reply to message #564713]

Mon, 27 August 2012 18:01

chandu208
Messages: 17
Registered: July 2012

Junior Member

Total DB size around 60 GB
11gR2
Linux 5.6
exporting 2 schemas

hanging..........

Tell me what else need from me........any suggestions??

Thanks in advance

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564715 is a reply to message #564714]

Mon, 27 August 2012 18:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

use your browser to search for question marks.
ensure you have provided an answer to each & every question in this thread asked of you

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564716 is a reply to message #564715]

Mon, 27 August 2012 18:21

Flyby
Messages: 188
Registered: March 2011
Location: Belgium

Senior Member

Webcrawling found someone post on a similar problem on OTN Maybe it got fixed in 11.2.0.3.3
Also check this page, title "On 4 undocumented DataPump parameters"
Ain't cool waiting for a future version? Range of versions believed to be affected Versions BELOW 12.1; Bug 9791589 - Data Pump export hang / uses excessive memory with many "WITH GRANT OPTION" grants - superceded [ID 9791589.8]

[Updated on: Mon, 27 August 2012 18:35]

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #564962 is a reply to message #564716]

Wed, 29 August 2012 14:18

alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California

Senior Member

I use the following and export one schema at a time. If one of the schemas hang then you know where to look.

ECSCDAQ > select 'expdp  system/manager schemas='||username||
  2  ' directory=MY_DIR dumpfile='||username||'.dmp logfile=expdp'
  3  ||username||'.log'
  4  from dba_users order by username;

expdp  system/manager schemas=APPQOSSYS directory=MY_DIR dumpfile=APPQOSSYS.dmp logfile=expdpAPPQOSSYS.log
expdp  system/manager schemas=CDA_APPS directory=MY_DIR dumpfile=CDA_APPS.dmp logfile=expdpCDA_APPS.log
expdp  system/manager schemas=CDA_DEV_MON directory=MY_DIR dumpfile=CDA_DEV_MON.dmp logfile=expdpCDA_DEV_MON.log
expdp  system/manager schemas=CDA_PV_APPS directory=MY_DIR dumpfile=CDA_PV_APPS.dmp logfile=expdpCDA_PV_APPS.log
expdp  system/manager schemas=CDA_PV_RO_USER directory=MY_DIR dumpfile=CDA_PV_RO_USER.dmp logfile=expdpCDA_PV_RO_USER.log
expdp  system/manager schemas=CDA_RO_USER directory=MY_DIR dumpfile=CDA_RO_USER.dmp logfile=expdpCDA_RO_USER.log
expdp  system/manager schemas=CDA_SHARED directory=MY_DIR dumpfile=CDA_SHARED.dmp logfile=expdpCDA_SHARED.log
expdp  system/manager schemas=CDA_SHARED_USER directory=MY_DIR dumpfile=CDA_SHARED_USER.dmp logfile=expdpCDA_SHARED_USER.log
expdp  system/manager schemas=CDA_WR_USER directory=MY_DIR dumpfile=CDA_WR_USER.dmp logfile=expdpCDA_WR_USER.log
expdp  system/manager schemas=CMS_PV_WR_USER directory=MY_DIR dumpfile=CMS_PV_WR_USER.dmp logfile=expdpCMS_PV_WR_USER.log
expdp  system/manager schemas=CMS_WR_USER directory=MY_DIR dumpfile=CMS_WR_USER.dmp logfile=expdpCMS_WR_USER.log
expdp  system/manager schemas=CSCDA_QAR directory=MY_DIR dumpfile=CSCDA_QAR.dmp logfile=expdpCSCDA_QAR.log
expdp  system/manager schemas=CSCDA_QAW directory=MY_DIR dumpfile=CSCDA_QAW.dmp logfile=expdpCSCDA_QAW.log
expdp  system/manager schemas=CTXSYS directory=MY_DIR dumpfile=CTXSYS.dmp logfile=expdpCTXSYS.log
expdp  system/manager schemas=DBSNMP directory=MY_DIR dumpfile=DBSNMP.dmp logfile=expdpDBSNMP.log
expdp  system/manager schemas=DIP directory=MY_DIR dumpfile=DIP.dmp logfile=expdpDIP.log
expdp  system/manager schemas=DMSYS directory=MY_DIR dumpfile=DMSYS.dmp logfile=expdpDMSYS.log
expdp  system/manager schemas=ECSCDAQ directory=MY_DIR dumpfile=ECSCDAQ.dmp logfile=expdpECSCDAQ.log
expdp  system/manager schemas=EXFSYS directory=MY_DIR dumpfile=EXFSYS.dmp logfile=expdpEXFSYS.log
expdp  system/manager schemas=IMAGE_SERVICE directory=MY_DIR dumpfile=IMAGE_SERVICE.dmp logfile=expdpIMAGE_SERVICE.log
expdp  system/manager schemas=IMAGE_SERVICE_USER directory=MY_DIR dumpfile=IMAGE_SERVICE_USER.dmp logfile=expdpIMAGE_SERVICE_USER.log
expdp  system/manager schemas=IPSOFT directory=MY_DIR dumpfile=IPSOFT.dmp logfile=expdpIPSOFT.log
expdp  system/manager schemas=MDDATA directory=MY_DIR dumpfile=MDDATA.dmp logfile=expdpMDDATA.log
expdp  system/manager schemas=MDSYS directory=MY_DIR dumpfile=MDSYS.dmp logfile=expdpMDSYS.log
expdp  system/manager schemas=MGMT_VIEW directory=MY_DIR dumpfile=MGMT_VIEW.dmp logfile=expdpMGMT_VIEW.log
expdp  system/manager schemas=OLAPSYS directory=MY_DIR dumpfile=OLAPSYS.dmp logfile=expdpOLAPSYS.log
expdp  system/manager schemas=ORACLE_OCM directory=MY_DIR dumpfile=ORACLE_OCM.dmp logfile=expdpORACLE_OCM.log
expdp  system/manager schemas=ORDDATA directory=MY_DIR dumpfile=ORDDATA.dmp logfile=expdpORDDATA.log
expdp  system/manager schemas=ORDPLUGINS directory=MY_DIR dumpfile=ORDPLUGINS.dmp logfile=expdpORDPLUGINS.log
expdp  system/manager schemas=ORDSYS directory=MY_DIR dumpfile=ORDSYS.dmp logfile=expdpORDSYS.log
expdp  system/manager schemas=OUTLN directory=MY_DIR dumpfile=OUTLN.dmp logfile=expdpOUTLN.log
expdp  system/manager schemas=SI_INFORMTN_SCHEMA directory=MY_DIR dumpfile=SI_INFORMTN_SCHEMA.dmp logfile=expdpSI_INFORMTN_SCHEMA.log
expdp  system/manager schemas=SPATIAL_CSW_ADMIN_USR directory=MY_DIR dumpfile=SPATIAL_CSW_ADMIN_USR.dmp logfile=expdpSPATIAL_CSW_ADMIN_USR.log
expdp  system/manager schemas=SPATIAL_WFS_ADMIN_USR directory=MY_DIR dumpfile=SPATIAL_WFS_ADMIN_USR.dmp logfile=expdpSPATIAL_WFS_ADMIN_USR.log
expdp  system/manager schemas=SYS directory=MY_DIR dumpfile=SYS.dmp logfile=expdpSYS.log
expdp  system/manager schemas=SYSTEM directory=MY_DIR dumpfile=SYSTEM.dmp logfile=expdpSYSTEM.log
expdp  system/manager schemas=TEST directory=MY_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
expdp  system/manager schemas=WMSYS directory=MY_DIR dumpfile=WMSYS.dmp logfile=expdpWMSYS.log
expdp  system/manager schemas=XDB directory=MY_DIR dumpfile=XDB.dmp logfile=expdpXDB.log
expdp  system/manager schemas=XS$NULL directory=MY_DIR dumpfile=XS$NULL.dmp logfile=expdpXS$NULL.log
expdp  system/manager schemas=YSUN directory=MY_DIR dumpfile=YSUN.dmp logfile=expdpYSUN.log

[Updated on: Wed, 29 August 2012 14:19]

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #676642 is a reply to message #564425]

Wed, 26 June 2019 13:07

Nitheesan
Messages: 1
Registered: June 2019

Junior Member

Use - EXCLUDE=DB_LINK - will work

Thanks!

Report message to a moderator

Re: datapump Export taking long time(HUNG) [message #676643 is a reply to message #676642]

Wed, 26 June 2019 13:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Nitheesan wrote on Wed, 26 June 2019 11:07

Use - EXCLUDE=DB_LINK - will work

Thanks!

You don't get a second chance to make a FIRST impression.
This thread is only about 7 YEARS old & last time OP visited this forum was 2013, so I doubt he will benefit from your response.

Please do NOT resurrect zombie threads that have been inactive for YEARS.

Report message to a moderator

Previous Topic:	ORA-04031 error with EXPDP
Next Topic:	SQL*Loader - wildcard character

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:55:29 CST 2025