{"id":1912,"date":"2020-09-03T19:57:42","date_gmt":"2020-09-03T19:57:42","guid":{"rendered":"https:\/\/swiv.com.br\/how-to-cleanup-orphaned-datapump-jobs-in-dba_datapump_jobs\/"},"modified":"2026-05-27T20:02:53","modified_gmt":"2026-05-27T19:02:53","slug":"how-to-cleanup-orphaned-datapump-jobs-in-dba_datapump_jobs","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2020\/09\/03\/how-to-cleanup-orphaned-datapump-jobs-in-dba_datapump_jobs\/","title":{"rendered":"How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS"},"content":{"rendered":"\n
H\u00e1 pouco tempo atr\u00e1s me deparei com um banco de dados Oracle com jobs de export\/import em status de “NOT RUNNING”. Neste simples artigo exploro como normalizei o ambiente conforme instru\u00e7\u00f5es do fornecedor.<\/p>\n\n\n\n
Consultando os datapumps jobs:<\/p>\n\n\n
\nSQL > select * from dba_datapump_jobs;\n \nOWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS\n------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- -----------------\nDATAPUMP_SESSIONS\n-----------------\nSYS SYS_IMPORT_SCHEMA_01 IMPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_03 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_15 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_07 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_16 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_14 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_06 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_12 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_10 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_IMPORT_SCHEMA_02 IMPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_IMPORT_SCHEMA_01 IMPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_08 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_11 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_05 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_09 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_IMPORT_SCHEMA_03 IMPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_04 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_13 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \nSYSTEM SYS_EXPORT_SCHEMA_02 EXPORT SCHEMA NOT RUNNING 0 0\n 0\n \n \n20 linhas selecionadas.\n \nSQL >\n<\/pre><\/div>\n\n\n
Tentei matar um dos Jobs pelo mecanismo tradicional, mas sem sucesso:<\/p>\n\n\n
\nSQL > DECLARE\n 2 h1 NUMBER;\n 3 BEGIN\n 4 h1:=DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_03','SYSTEM');\n 5 DBMS_DATAPUMP.STOP_JOB (h1,1,0);\n 6 END;\n 7 \/\nDECLARE\n*\nERRO na linha 1:\nORA-31626: job n\u00d2o existe\nORA-06512: em "SYS.DBMS_SYS_ERROR", line 79\nORA-06512: em "SYS.DBMS_DATAPUMP", line 1137\nORA-06512: em "SYS.DBMS_DATAPUMP", line 3873\nORA-06512: em line 4\n<\/pre><\/div>\n\n
Desse modo, precisei considerar o Oracle Note “How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1)”.<\/p>\n\n\n<\/figure>\n\n\n\n
Realizando consulta discriminada no documento para identificar os Jobs:<\/p>\n\n\n
\nSQL > SET lines 200\nSQL > COL owner_name FORMAT a10;\nSQL > COL job_name FORMAT a20\nSQL > COL state FORMAT a12\nSQL > COL operation LIKE state\nSQL > COL job_mode LIKE state\nSQL > COL owner.object for a50\nSQL > SELECT owner_name, job_name, rtrim(operation) "OPERATION",\n 2 rtrim(job_mode) "JOB_MODE", state, attached_sessions\n 3 FROM dba_datapump_jobs\n 4 WHERE job_name NOT LIKE 'BIN$%'\n 5 ORDER BY 1,2;\n \nOWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS\n---------- -------------------- ------------ ------------ ------------ -----------------\nSYSTEM SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_EXPORT_SCHEMA_02 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_EXPORT_SCHEMA_03 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_EXPORT_SCHEMA_04 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_EXPORT_SCHEMA_05 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_EXPORT_SCHEMA_06 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_EXPORT_SCHEMA_07 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_EXPORT_SCHEMA_08 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_EXPORT_SCHEMA_09 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_EXPORT_SCHEMA_10 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_EXPORT_SCHEMA_11 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_EXPORT_SCHEMA_12 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_EXPORT_SCHEMA_13 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_EXPORT_SCHEMA_14 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_EXPORT_SCHEMA_15 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_EXPORT_SCHEMA_16 EXPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_IMPORT_SCHEMA_01 IMPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_IMPORT_SCHEMA_02 IMPORT SCHEMA NOT RUNNING 0\nSYSTEM SYS_IMPORT_SCHEMA_03 IMPORT SCHEMA NOT RUNNING 0\n \n19 linhas selecionadas.\n<\/pre><\/div>\n\n\n
\nSQL > set linesize 200 trimspool on\nSQL > set pagesize 2000\nSQL > col owner form a30\nSQL > col created form a25\nSQL > col last_ddl_time form a25\nSQL > col object_name form a30\nSQL > col object_type form a25\nSQL > select OWNER,OBJECT_NAME,OBJECT_TYPE, status,\n 2 to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created ,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time\n 3 from dba_objects\n 4 where object_name like 'ET$%'\n 5 \/\n \nOWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_TIME\n------------------------------ ------------------------------ ------------------------- ------- ------------------------- -------------------------\nSYSTEM ET$024B51470002 TABLE VALID 28-ago-2020 12:38:42 28-ago-2020 12:38:42\nSYSTEM ET$0308069F0002 TABLE VALID 28-ago-2020 16:42:25 28-ago-2020 16:42:25\nSYSTEM ET$03C7523D0002 TABLE VALID 28-ago-2020 17:07:54 28-ago-2020 17:07:54\n \nSQL >\n<\/pre><\/div>\n\n\n
Depois disso, caso a situa\u00e7\u00e3o esteja caracterizada conforme a nota especifica, pode-se dropar as tabelas vide abaixo:<\/p>\n\n\n
\nSQL > drop table system.ET$024B51470002 purge;\n \nTabela eliminada.\n \nSQL > drop table system.ET$0308069F0002 purge;\n \nTabela eliminada.\n \nSQL > drop table system.ET$03C7523D0002 purge;\n \nTabela eliminada.\n<\/pre><\/div>\n\n\n
Identificando “master tables” relacionadas com o processo:<\/p>\n\n\n
\nSQL > COL owner.object FORMAT a50\nSQL > SELECT o.status, o.object_id, o.object_type,\n 2 o.owner||'.'||object_name "OWNER.OBJECT"\n 3 FROM dba_objects o, dba_datapump_jobs j\n 4 WHERE o.owner=j.owner_name AND o.object_name=j.job_name\n 5 AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;\n \nSTATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT\n------- ---------- ------------------------- --------------------------------------------------\nVALID 391826 TABLE SYSTEM.SYS_EXPORT_SCHEMA_01\nVALID 392161 TABLE SYSTEM.SYS_EXPORT_SCHEMA_02\nVALID 473352 TABLE SYSTEM.SYS_EXPORT_SCHEMA_03\nVALID 522319 TABLE SYSTEM.SYS_EXPORT_SCHEMA_04\nVALID 564085 TABLE SYSTEM.SYS_EXPORT_SCHEMA_05\nVALID 569492 TABLE SYSTEM.SYS_EXPORT_SCHEMA_06\nVALID 568753 TABLE SYSTEM.SYS_EXPORT_SCHEMA_07\nVALID 574812 TABLE SYSTEM.SYS_EXPORT_SCHEMA_08\nVALID 736720 TABLE SYSTEM.SYS_EXPORT_SCHEMA_09\nVALID 737306 TABLE SYSTEM.SYS_EXPORT_SCHEMA_10\nVALID 575698 TABLE SYSTEM.SYS_EXPORT_SCHEMA_11\nVALID 974755 TABLE SYSTEM.SYS_EXPORT_SCHEMA_12\nVALID 974775 TABLE SYSTEM.SYS_EXPORT_SCHEMA_13\nVALID 1187564 TABLE SYSTEM.SYS_EXPORT_SCHEMA_14\nVALID 1187589 TABLE SYSTEM.SYS_EXPORT_SCHEMA_15\nVALID 1187614 TABLE SYSTEM.SYS_EXPORT_SCHEMA_16\nVALID 1295890 TABLE SYSTEM.SYS_IMPORT_SCHEMA_01\nVALID 1341921 TABLE SYSTEM.SYS_IMPORT_SCHEMA_02\nVALID 1342685 TABLE SYSTEM.SYS_IMPORT_SCHEMA_03\n \n19 linhas selecionadas.\n \nSQL >\n<\/pre><\/div>\n\n\n
Realizando o DROP das mesmas:<\/p>\n\n\n
\nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_01;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_02;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_03;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_04;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_05;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_06;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_07;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_08;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_09;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_10;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_11;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_12;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_13;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_14;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_15;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_16;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_IMPORT_SCHEMA_01;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_IMPORT_SCHEMA_02;\n \nTabela eliminada.\n \nSQL > DROP TABLE SYSTEM.SYS_IMPORT_SCHEMA_03;\n \nTabela eliminada.\n \nSQL >\n<\/pre><\/div>\n\n\n
H\u00e1 pouco tempo atr\u00e1s me deparei com um banco de dados Oracle com jobs de export\/import em status de “NOT RUNNING”. Neste simples artigo exploro como normalizei o ambiente conforme instru\u00e7\u00f5es do fornecedor. Consultando os datapumps jobs: Tentei matar um dos Jobs pelo mecanismo tradicional, mas sem sucesso: Desse modo, precisei considerar o Oracle Note […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[16],"class_list":["post-1912","post","type-post","status-publish","format-standard","hentry","category-administration","tag-expdp-impdp"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/1912","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=1912"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/1912\/revisions"}],"predecessor-version":[{"id":9283,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/1912\/revisions\/9283"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=1912"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=1912"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=1912"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/swiv.com.br\/wp-content\/uploads\/2022\/07\/image-350.png\")
<\/figure>\n\n\n\n