{"id":4677,"date":"2021-11-04T16:32:55","date_gmt":"2021-11-04T07:32:55","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=4677"},"modified":"2021-11-04T16:32:55","modified_gmt":"2021-11-04T07:32:55","slug":"%eb%8d%b0%ec%9d%b4%ed%84%b0-%ed%8e%8c%ed%94%84-%ea%b1%b8%ec%96%b4-%eb%86%93%ea%b3%a0-%eb%aa%a8%eb%8b%88%ed%84%b0%eb%a7%81-%ed%95%98%eb%8a%94-%eb%b0%a9%eb%b2%95-%ec%a0%95%eb%a6%ac","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=4677","title":{"rendered":"\ub370\uc774\ud130 \ud38c\ud504 \uac78\uc5b4 \ub193\uace0 \ubaa8\ub2c8\ud130\ub9c1 \ud558\ub294 \ubc29\ubc95 \uc815\ub9ac"},"content":{"rendered":"

\n

\ub370\uc774\ud130 \ud38c\ud504 \uac78\uc5b4 \ub193\uace0 \ubaa8\ub2c8\ud130\ub9c1 \ud558\ub294 \ubc29\ubc95<\/p>\n

\n

\n

1) Using the datapump client (expdp & impdp) STATUS command:-<\/p>\n

When the export or import job is running press\u00a0+C keys to get to the respective datapump client prompt OR you can use another session of datapump client and using the ATTACH<\/strong><\/span> clause attach to the running job and then issue the\u00a0STATUS<\/strong><\/span>\u00a0command:-<\/p>\n

Export> status<\/strong><\/span>\r\n\r\nJob: SYS_EXPORT_FULL_01\r\n  Operation: EXPORT\r\n  Mode: FULL\r\n  State: EXECUTING\r\n  Bytes Processed: 0\r\n  Current Parallelism: 1\r\n  Job Error Count: 0\r\n  Dump File: \/u01\/app\/oracle\/dpump\/admin.dmp\r\n    bytes written: 4,096\r\n\r\nWorker 1 Status:\r\n  Process Name: DW00\r\n  State: EXECUTING\r\n  Object Schema: ADMIN\r\n  Object Name: TEST_01\r\n  Object Type: DATABASE_EXPORT\/SCHEMA\/PACKAGE_BODIES\/PACKAGE\/PACKAGE_BODY\r\n  Completed Objects: 78\r\n  Worker Parallelism: 1\r\n<\/pre>\n
Import> status\r\n\r\nJob: SYS_IMPORT_SCHEMA_01\r\n  Operation: IMPORT\r\n  Mode: SCHEMA\r\n  State: EXECUTING\r\n  Bytes Processed: 2,788,707,576\r\n  Percent Done: 99\r\n  Current Parallelism: 6\r\n  Job Error Count: 0\r\n  Dump File: \/apps\/keplero\/backup\/ORA11G\/dpump\/cishd-34173_%u.dmp\r\n  Dump File: \/apps\/keplero\/backup\/ORA11G\/dpump\/cishd-34173_01.dmp\r\n  Dump File: \/apps\/keplero\/backup\/ORA11G\/dpump\/cishd-34173_02.dmp\r\n  Dump File: \/apps\/keplero\/backup\/ORA11G\/dpump\/cishd-34173_03.dmp\r\n  Dump File: \/apps\/keplero\/backup\/ORA11G\/dpump\/cishd-34173_04.dmp\r\n  Dump File: \/apps\/keplero\/backup\/ORA11G\/dpump\/cishd-34173_05.dmp\r\n  Dump File: \/apps\/keplero\/backup\/ORA11G\/dpump\/cishd-34173_06.dmp\r\n\r\nWorker 1 Status:\r\n  Process Name: DW00\r\n  State: EXECUTING\r\n  Object Schema: XTP_AC\r\n  Object Name: SYS_C0063284986\r\n  Object Type: SCHEMA_EXPORT\/TABLE\/CONSTRAINT\/CONSTRAINT\r\n  Completed Objects: 1,120\r\n  Worker Parallelism: 1\r\n\r\nWorker 2 Status:\r\n  Process Name: DW01\r\n  State: WORK WAITING\r\n\r\nWorker 3 Status:\r\n  Process Name: DW02\r\n  State: WORK WAITING\r\n\r\nWorker 4 Status:\r\n  Process Name: DW03\r\n  State: WORK WAITING\r\n\r\nWorker 5 Status:\r\n  Process Name: DW04\r\n  State: WORK WAITING\r\n\r\nWorker 6 Status:\r\n  Process Name: DW05\r\n  State: WORK WAITING\r\n\r\nImport>\r\n\r\n<\/pre>\n
2) Querying\u00a0DBA_DATAPUMP_JOBS<\/strong>\u00a0view:-<\/p>\n
select * from dba_datapump_jobs;\r\n<\/strong><\/span><\/pre>\n
The STATE column of the above view would give you the status of the JOB to show whether EXPDP or IMPDP jobs are still running, or have terminated with either a success or failure status.<\/p>\n
\n
3) Querying\u00a0V$SESSION_LONGOPS & V$SESSION<\/strong>\u00a0views:-<\/p>\n
SELECT b.username, a.sid, b.opname, b.target,\r\n            round(b.SOFAR*100\/b.TOTALWORK,0) || '%' as \"%DONE\", b.TIME_REMAINING,\r\n            to_char(b.start_time,'YYYY\/MM\/DD HH24:MI:SS') start_time\r\n     FROM v$session_longops b, v$session a\r\n     WHERE a.sid = b.sid      ORDER BY 6;\r\n<\/span><\/strong><\/pre>\n
4) Querying\u00a0V$SESSION_LONGOPS & V$DATAPUMP_JOB<\/strong>\u00a0views:-<\/p>\n
SELECT sl.sid, sl.serial#, sl.sofar, sl.totalwork, dp.owner_name, dp.state, dp.job_mode\r\n     FROM v$session_longops sl, v$datapump_job dp\r\n     WHERE sl.opname = dp.job_name\r\n     AND sl.sofar != sl.totalwork;\r\n<\/span><\/strong><\/pre>\n
5) Querying all the related views with a single query:-<\/p>\n
select x.job_name,b.state,b.job_mode,b.degree\r\n, x.owner_name,z.sql_text, p.message\r\n, p.totalwork, p.sofar\r\n, round((p.sofar\/p.totalwork)*100,2) done\r\n, p.time_remaining\r\nfrom dba_datapump_jobs b\r\nleft join dba_datapump_sessions x on (x.job_name = b.job_name)\r\nleft join v$session y on (y.saddr = x.saddr)\r\nleft join v$sql z on (y.sql_id = z.sql_id)\r\nleft join v$session_longops p ON (p.sql_id = y.sql_id)\r\nWHERE y.module='Data Pump Worker'\r\nAND p.time_remaining > 0;\r\n<\/strong><\/span><\/pre>\n
6) Use the following procedure and replace the JOB_OWNER & JOB_NAME as per your env. which you fetch from import.log:-<\/p>\n
;;;\r\nImport: Release 12.1.0.2.0 - Production on Thu Jun 29 00:29:09 2017\r\n\r\nCopyright (c) 1982, 2014, Oracle and\/or its affiliates.  All rights reserved.\r\n;;;\r\nConnected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production\r\nWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options\r\nMaster table \"SYSTEM\".\"SYS_IMPORT_FULL_04\" successfully loaded\/unloaded\r\n<\/pre>\n
Here the JOB_OWNER is SYSTEM and JOB_NAME is SYS_IMPORT_FULL_04.<\/p>\n
And below is the procedure:-<\/p>\n
\r\nSET SERVEROUTPUT ON\r\nDECLARE\r\n  ind NUMBER;              \r\n  h1 NUMBER;               \r\n  percent_done NUMBER;     \r\n  job_state VARCHAR2(30);  \r\n  js ku$_JobStatus;        \r\n  ws ku$_WorkerStatusList; \r\n  sts ku$_Status;          \r\nBEGIN\r\nh1 := DBMS_DATAPUMP.attach('JOB_NAME', 'JOB_OWNER');\r\ndbms_datapump.get_status(h1,\r\n           dbms_datapump.ku$_status_job_error +\r\n           dbms_datapump.ku$_status_job_status +\r\n           dbms_datapump.ku$_status_wip, 0, job_state, sts);\r\njs := sts.job_status;\r\nws := js.worker_status_list;\r\n      dbms_output.put_line('*** Job percent done = ' ||\r\n                           to_char(js.percent_done));\r\n      dbms_output.put_line('restarts - '||js.restart_count);\r\nind := ws.first;\r\n  while ind is not null loop\r\n    dbms_output.put_line('rows completed - '||ws(ind).completed_rows);\r\n    ind := ws.next(ind);\r\n  end loop;\r\nDBMS_DATAPUMP.detach(h1);\r\nend;\r\n\/<\/strong> <\/span>\r\n\r\n<\/pre>\n
7) Also for any errors you can check the alert log and query the DBA_RESUMABLE view.<\/p>\n
select name, sql_text, error_msg from dba_resumable;\r\n<\/strong><\/span><\/pre>\n
That\u2019s all what I can think of at the moment, would add the queries to this post if I find another view which can be used to get the information of the datapump jobs.<\/p>\n","protected":false},"excerpt":{"rendered":"
\ub370\uc774\ud130 \ud38c\ud504 \uac78\uc5b4 \ub193\uace0 \ubaa8\ub2c8\ud130\ub9c1 \ud558\ub294 \ubc29\ubc95 1) Using the datapump client (expdp & impdp) STATUS command:- When the export or import job is running press\u00a0+C keys to get to the respective datapump client prompt OR you can use another session of datapump client and using the ATTACH clause attach to the running job and […]<\/p>\n","protected":false},"author":1,"featured_media":4141,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[10],"tags":[679,1258,1257,287,1259],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/08\/oracletips.jpg?fit=650%2C361","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4677"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4677"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4677\/revisions"}],"predecessor-version":[{"id":4678,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4677\/revisions\/4678"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/4141"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4677"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4677"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4677"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}