Há pouco tempo atrás 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ções do fornecedor.
Consultando os datapumps jobs:
SQL > select * from dba_datapump_jobs;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- -----------------
DATAPUMP_SESSIONS
-----------------
SYS SYS_IMPORT_SCHEMA_01 IMPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_03 EXPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_15 EXPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_07 EXPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_16 EXPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_14 EXPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_06 EXPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_12 EXPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_10 EXPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_IMPORT_SCHEMA_02 IMPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_IMPORT_SCHEMA_01 IMPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_08 EXPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_11 EXPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_05 EXPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_09 EXPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_IMPORT_SCHEMA_03 IMPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_04 EXPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_13 EXPORT SCHEMA NOT RUNNING 0 0
0
SYSTEM SYS_EXPORT_SCHEMA_02 EXPORT SCHEMA NOT RUNNING 0 0
0
20 linhas selecionadas.
SQL >
Tentei matar um dos Jobs pelo mecanismo tradicional, mas sem sucesso:
SQL > DECLARE
2 h1 NUMBER;
3 BEGIN
4 h1:=DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_03','SYSTEM');
5 DBMS_DATAPUMP.STOP_JOB (h1,1,0);
6 END;
7 /
DECLARE
*
ERRO na linha 1:
ORA-31626: job nÒo existe
ORA-06512: em "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: em "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: em "SYS.DBMS_DATAPUMP", line 3873
ORA-06512: em line 4
Desse modo, precisei considerar o Oracle Note “How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1)”.
Realizando consulta discriminada no documento para identificar os Jobs:
SQL > SET lines 200
SQL > COL owner_name FORMAT a10;
SQL > COL job_name FORMAT a20
SQL > COL state FORMAT a12
SQL > COL operation LIKE state
SQL > COL job_mode LIKE state
SQL > COL owner.object for a50
SQL > SELECT owner_name, job_name, rtrim(operation) "OPERATION",
2 rtrim(job_mode) "JOB_MODE", state, attached_sessions
3 FROM dba_datapump_jobs
4 WHERE job_name NOT LIKE 'BIN$%'
5 ORDER BY 1,2;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ------------ ------------ ------------ -----------------
SYSTEM SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_EXPORT_SCHEMA_02 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_EXPORT_SCHEMA_03 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_EXPORT_SCHEMA_04 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_EXPORT_SCHEMA_05 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_EXPORT_SCHEMA_06 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_EXPORT_SCHEMA_07 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_EXPORT_SCHEMA_08 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_EXPORT_SCHEMA_09 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_EXPORT_SCHEMA_10 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_EXPORT_SCHEMA_11 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_EXPORT_SCHEMA_12 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_EXPORT_SCHEMA_13 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_EXPORT_SCHEMA_14 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_EXPORT_SCHEMA_15 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_EXPORT_SCHEMA_16 EXPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_IMPORT_SCHEMA_01 IMPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_IMPORT_SCHEMA_02 IMPORT SCHEMA NOT RUNNING 0
SYSTEM SYS_IMPORT_SCHEMA_03 IMPORT SCHEMA NOT RUNNING 0
19 linhas selecionadas.
Identificando external tables órfãs:
SQL > set linesize 200 trimspool on
SQL > set pagesize 2000
SQL > col owner form a30
SQL > col created form a25
SQL > col last_ddl_time form a25
SQL > col object_name form a30
SQL > col object_type form a25
SQL > select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
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
3 from dba_objects
4 where object_name like 'ET$%'
5 /
OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_TIME
------------------------------ ------------------------------ ------------------------- ------- ------------------------- -------------------------
SYSTEM ET$024B51470002 TABLE VALID 28-ago-2020 12:38:42 28-ago-2020 12:38:42
SYSTEM ET$0308069F0002 TABLE VALID 28-ago-2020 16:42:25 28-ago-2020 16:42:25
SYSTEM ET$03C7523D0002 TABLE VALID 28-ago-2020 17:07:54 28-ago-2020 17:07:54
SQL >
Depois disso, caso a situação esteja caracterizada conforme a nota especifica, pode-se dropar as tabelas vide abaixo:
SQL > drop table system.ET$024B51470002 purge;
Tabela eliminada.
SQL > drop table system.ET$0308069F0002 purge;
Tabela eliminada.
SQL > drop table system.ET$03C7523D0002 purge;
Tabela eliminada.
Identificando “master tables” relacionadas com o processo:
SQL > COL owner.object FORMAT a50
SQL > SELECT o.status, o.object_id, o.object_type,
2 o.owner||'.'||object_name "OWNER.OBJECT"
3 FROM dba_objects o, dba_datapump_jobs j
4 WHERE o.owner=j.owner_name AND o.object_name=j.job_name
5 AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;
STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------- ---------- ------------------------- --------------------------------------------------
VALID 391826 TABLE SYSTEM.SYS_EXPORT_SCHEMA_01
VALID 392161 TABLE SYSTEM.SYS_EXPORT_SCHEMA_02
VALID 473352 TABLE SYSTEM.SYS_EXPORT_SCHEMA_03
VALID 522319 TABLE SYSTEM.SYS_EXPORT_SCHEMA_04
VALID 564085 TABLE SYSTEM.SYS_EXPORT_SCHEMA_05
VALID 569492 TABLE SYSTEM.SYS_EXPORT_SCHEMA_06
VALID 568753 TABLE SYSTEM.SYS_EXPORT_SCHEMA_07
VALID 574812 TABLE SYSTEM.SYS_EXPORT_SCHEMA_08
VALID 736720 TABLE SYSTEM.SYS_EXPORT_SCHEMA_09
VALID 737306 TABLE SYSTEM.SYS_EXPORT_SCHEMA_10
VALID 575698 TABLE SYSTEM.SYS_EXPORT_SCHEMA_11
VALID 974755 TABLE SYSTEM.SYS_EXPORT_SCHEMA_12
VALID 974775 TABLE SYSTEM.SYS_EXPORT_SCHEMA_13
VALID 1187564 TABLE SYSTEM.SYS_EXPORT_SCHEMA_14
VALID 1187589 TABLE SYSTEM.SYS_EXPORT_SCHEMA_15
VALID 1187614 TABLE SYSTEM.SYS_EXPORT_SCHEMA_16
VALID 1295890 TABLE SYSTEM.SYS_IMPORT_SCHEMA_01
VALID 1341921 TABLE SYSTEM.SYS_IMPORT_SCHEMA_02
VALID 1342685 TABLE SYSTEM.SYS_IMPORT_SCHEMA_03
19 linhas selecionadas.
SQL >
Realizando o DROP das mesmas:
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_01;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_02;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_03;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_04;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_05;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_06;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_07;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_08;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_09;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_10;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_11;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_12;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_13;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_14;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_15;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_16;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_IMPORT_SCHEMA_01;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_IMPORT_SCHEMA_02;
Tabela eliminada.
SQL > DROP TABLE SYSTEM.SYS_IMPORT_SCHEMA_03;
Tabela eliminada.
SQL >
Purge na recycle_bin:
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
Nessa fase, não é mais reportado datapumps jobs e o ambiente está normalizado:
SQL > SELECT * FROM user_datapump_jobs;
nÒo hß linhas selecionadas
SQL >
Portuguese
English
Spanish