{"id":940,"date":"2024-12-26T22:01:29","date_gmt":"2024-12-26T22:01:29","guid":{"rendered":"https:\/\/w3buddy.com\/?p=940"},"modified":"2026-01-15T13:15:54","modified_gmt":"2026-01-15T07:45:54","slug":"oracle-datapump-expdp-impdp-monitoring-scripts","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/oracle-datapump-expdp-impdp-monitoring-scripts\/","title":{"rendered":"Oracle DATAPUMP EXPDP\/IMPDP Monitoring Scripts"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">When monitoring EXPDP\/IMPDP jobs, we rely on log files generated by the processes and alert logs for error tracking. While this works in most cases, detailed monitoring is crucial for large datasets and long-running sessions. Below are useful queries to help monitor your Data Pump jobs more effectively.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Key Tables\/Views to Monitor Data Pump Jobs:<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>DBA_DATAPUMP_JOBS<\/strong><\/li>\n\n\n\n<li><strong>DBA_DATAPUMP_SESSIONS<\/strong><\/li>\n\n\n\n<li><strong>DBA_RESUMABLE<\/strong><\/li>\n\n\n\n<li><strong>V$SESSION_LONGOPS<\/strong><\/li>\n\n\n\n<li><strong>V$SESSION<\/strong><\/li>\n\n\n\n<li><strong>V$DATAPUMP_JOB<\/strong><\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Script to Find the Status of Work Done<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">This query provides information on the progress of the Data Pump job, including percentage completed and time remaining.<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT x.job_name, ddj.state, ddj.job_mode, ddj.degree,\n       x.owner_name, z.sql_text, p.message,\n       p.totalwork, p.sofar,\n       ROUND((p.sofar \/ p.totalwork) * 100, 2) AS done,\n       p.time_remaining\nFROM dba_datapump_jobs ddj\nLEFT JOIN dba_datapump_sessions x ON (x.job_name = ddj.job_name)\nLEFT JOIN v$session y ON (y.saddr = x.saddr)\nLEFT JOIN v$sql z ON (y.sql_id = z.sql_id)\nLEFT JOIN v$session_longops p ON (p.sql_id = y.sql_id)\nWHERE y.module = 'Data Pump Worker'\n  AND p.time_remaining > 0;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Another Simple Script Using Only the LONGOPS View<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">This query provides a simpler overview of the progress, using just the <code>v$session_longops<\/code> view.<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT \n    ROUND(sofar \/ totalwork * 100, 2) AS percent_completed, \n    v$session_longops.* \nFROM \n    v$session_longops \nWHERE \n    sofar &lt;> totalwork \nORDER BY\n    target, sid;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Procedure to Find Job Status in Terms of Percentage &amp; Number of Rows<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This PL\/SQL procedure provides the status of the job, including the percentage completed and the number of rows processed.<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SET SERVEROUTPUT ON\nDECLARE\n  ind NUMBER;              \n  h1 NUMBER;               \n  percent_done NUMBER;     \n  job_state VARCHAR2(30);  \n  js ku$_JobStatus;        \n  ws ku$_WorkerStatusList; \n  sts ku$_Status;          \nBEGIN\n  h1 := DBMS_DATAPUMP.attach('&amp;JOB_NAME', '&amp;JOB_OWNER');\n  dbms_datapump.get_status(h1,\n           dbms_datapump.ku$_status_job_error +\n           dbms_datapump.ku$_status_job_status +\n           dbms_datapump.ku$_status_wip, 0, job_state, sts);\n  js := sts.job_status;\n  ws := js.worker_status_list;\n  dbms_output.put_line('** Job percent done = ' || TO_CHAR(js.percent_done));\n  dbms_output.put_line('restarts - ' || js.restart_count);\n  \n  ind := ws.first;\n  WHILE ind IS NOT NULL LOOP\n    dbms_output.put_line('rows completed - ' || ws(ind).completed_rows);\n    ind := ws.next(ind);\n  END LOOP;\n  \n  DBMS_DATAPUMP.detach(h1);\nEND;\n\/<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Important Notes:<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The procedure requires <code>JOB_NAME<\/code> and <code>JOB_OWNER<\/code> as input parameters. You can retrieve this information from your export\/import log or use the previous SQL script to obtain it.<\/li>\n\n\n\n<li>If you\u2019re performing the <code>expdp\/impdp<\/code> operation as <code>SYSDBA<\/code>, be sure to execute this package using the same privilege level (<code>SYSDBA<\/code>).<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">This method enables more granular and real-time monitoring of long-running <code>expdp\/impdp<\/code> jobs, ensuring you can track progress and troubleshoot effectively.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When monitoring EXPDP\/IMPDP jobs, we rely on log files generated by the processes and alert logs for error tracking. While this works in most cases, detailed monitoring is crucial for large datasets and long-running sessions. Below are useful queries to help monitor your Data Pump jobs more effectively. Key Tables\/Views to Monitor Data Pump Jobs: [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-940","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/940","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=940"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/940\/revisions"}],"predecessor-version":[{"id":1552,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/940\/revisions\/1552"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=940"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=940"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=940"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}