
3 Most Common Datapump Monitoring Questions Answered
Which DBA are you?
A. The one that kicks off a datapump export/import, and forgets about it? (You start the process, then go for coffee or start working on something else.)
OR
B. The one that kicks off a datapump export/import, then stares at the black screen and wonders, what the heck is the job doing? What table is being processed? It has been over 5 minutes and there are has been no changes on the screen. Is the job stuck?
I can relate to those DBAs that are in category B. I used to wonder what is datapump doing, especially for imports. You can tell the last table that was loaded, and you have no idea what table is getting imported right now.
I used to ask myself how much time is needed for the export to complete.
I did not know the answer.
I used to ask myself, what table is getting imported, especially if the import was processing a large table.
I did not know the answer. I could tell the last table that was processed, but not the current one.
I used to ask myself, what is datapump doing, is the job hung, or is it actually processing data?
I did not know the answer. I could only tell what the job did prior to this moment.
Do you ask yourself these questions? Or even better, are there other people asking YOU these questions.
I used to Google for queries to find answers to my questions, and usually by the time I found the answer, the export or import would complete. Then, I would forget all about the queries, and next time I would start from the beginning.
Do you want to be able to answer questions like
- what is datapump doing?
- what table is being exported/imported?
- how far ahead is the export?
Look no further. Your questions are answered by the two scripts I created. You only need to save them to your toolbox and start using them!
Below, I’ll share with you for FREE, my scripts to monitor datapump export and import jobs anytime.
/* Script name: mon_expdp.sql Example: @mon_expdp.sql Purpose: To monitor datapump export jobs Created By Diana Robete Copyright: @2016 dbaparadise.com */ col job_name noprint new_value _job_name col username for A20 col sid for 9999 col opname for A40 col %Done for A5 col "TimeLeft(sec)" for 9999999999999 col start_time for A22 set linesize 200 set verify off select job_name from dba_datapump_jobs where state='EXECUTING'; SELECT a,sid, b.username, b.opname, round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%Done", TIME_REMAINING "TimeLeft(sec)", to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time FROM v$session a, v$session_longops b WHERE a.sid = b.sid AND b.opname like '%&_job_name%'; !expdp \'/ as sysdba\' attach=&_job_name |
Sample Output
/* Script name: mon_impdp.sql Example: @mon_impdp.sql Purpose: To monitor datapump import jobs Created By Diana Robete Copyright: @2016 dbaparadise.com */ col job_name new_value _job_name col username for A20 col sid for 9999 col opname for A40 col %Done for A5 col "TimeLeft(sec)" for 9999999999999 col start_time for A22 set linesize 200 select job_name from dba_datapump_jobs where state='EXECUTING'; SELECT a,sid, b.username, b.opname, round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%Done", TIME_REMAINING "TimeLeft(sec)", to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time FROM v$session a, v$session_longops b WHERE a.sid = b.sid AND b.opname like '%&_job_name%'; !impdp \'/ as sysdba\' attach=&_job_name |
If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!
–Diana
I never thought of launching “expdp attach” FROM sql*plus, as you do here with “!” — that’s cool.
For more details on troubleshooting a seemingly “hung” datapump job, look up my blog post on https://community.oracle.com/blogs/dearDBA/2015/07/22/impdp-stalls-for-no-apparent-reason
Thank you!
Great post!! Thanks 🙂
Greetings from Colombia.
Thank you Daniel!
Good info
Thank you!
good information
Thank you
from india