Le souci
Mon export datapump s’est planté, j’ai effacé mon fichier de dump et donc plus moyen de faire l’attachement qui me permettrait de tuer la tâche en faisant kill_job. Pire encore, quand je tente le coup, datapump me donne un message d’erreur qui n’a pas gtrand chose à vois avec la problème réel.
[oracle@serveur ~]$ impdp toto/lolo attach=SYS_IMPORT_TRANSPORTABLE_01 Import: Release 11.2.0.4.0 - Production on Thu Apr 7 09:45:57 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning option ORA-31626: job does not exist ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.KUPV$FT", line 405 ORA-31638: cannot attach to job SYS_IMPORT_TRANSPORTABLE_01 for user TOTO ORA-31632: master table "TOTO.SYS_IMPORT_TRANSPORTABLE_01" not found, invalid, or inaccessible ORA-00942: table or view does not exist
Résolution
Je liste les tâches datapump qui tournent
SET lines 200 SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs ORDER BY 1,2; OWNER_NAME JOB_NAME OPERATION ------------------------------ ------------------------------ ------------------------------ JOB_MODE STATE ATTACHED_SESSIONS ------------------------------ ------------------------------ ----------------- TOTO SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0 TOTO SYS_EXPORT_FULL_02 EXPORT FULL NOT RUNNING 0 TOTO SYS_EXPORT_FULL_03 EXPORT FULL NOT RUNNING 0 TOTO SYS_EXPORT_FULL_04 EXPORT FULL NOT RUNNING 0 TOTO SYS_IMPORT_SCHEMA_01 IMPORT SCHEMA NOT RUNNING 0
Ca faisait longtemps que le ménage n’avait pas été fait !
Je cherche les tables effectivement attachées aux tâches datapump.
SELECT o.status, o.object_id, o.object_type,
o.owner||'.'||object_name OWNER_OBJECT
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;
STATUS OBJECT_ID OBJECT_TYPE OWNER_OBJECT
------- ---------- ------------------- -------------------------------------------------
VALID 8313098 TABLE TOTO.SYS_EXPORT_FULL_01
VALID 8313045 TABLE TOTO.SYS_EXPORT_FULL_02
VALID 8313021 TABLE TOTO.SYS_EXPORT_FULL_03
VALID 8313049 TABLE TOTO.SYS_EXPORT_FULL_04
VALID 9410606 TABLE TOTO.SYS_IMPORT_SCHEMA_01
Et je les droppe (saut une, mais juste pour avoir une ligne dans la requête d’après.
SQL> DROP TABLE TOTO.SYS_EXPORT_FULL_01 ; SQL> DROP TABLE TOTO.SYS_EXPORT_FULL_02 ; SQL> DROP TABLE TOTO.SYS_EXPORT_FULL_03 ; SQL> DROP TABLE TOTO.SYS_EXPORT_FULL_04 ;
Je vérifie la dispartion des jobs dont les tables ont été droppées.
SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs ORDER BY 1,2; OWNER_NAME JOB_NAME OPERATION ------------------------------ ------------------------------ ------------------------------ JOB_MODE STATE ATTACHED_SESSIONS ------------------------------ ------------------------------ ----------------- TOTO SYS_IMPORT_SCHEMA_01 IMPORT SCHEMA NOT RUNNING 0
C’était pourtant pas si compliqué