ORA-31634 and dbms_datapump

Today I tried to import some data into the database. I used dbms_datapump instead of impdp which I usually use for that. Unfortunately I forgot to chgrp oinstall *.dmp, so I've got an obvious error and after an obvious fix I've tried to run the job again... and got ORA-31634: job already exists. I did not want to rename a new job and after a little googling I found this nice blog post: http://arjudba.blogspot.com/2009/05/how-to-cleanup-orphaned-datapump-jobs.html. So I did like the post says:

> select * from user_datapump_jobs;
JOB_NAME OPERATION JOB_MODE STATE    DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
-------- --------- -------- -------- ------ ----------------- -----------------
MYJOB    IMPORT    SCHEMA   DEFINING      1                 1                 2

I tried:

drop table MYJOB;
purge recyclebin;

and I saw the job was still there. Then I did:

declare
 j number;
begin
 j := dbms_datapump.attach('MYJOB');
 dbms_datapump.stop_job(j);
end;
/

but the job obviously did not want to disappear. I had no idea what else I could do, so... I disconnected from the database. After I connected again the user_datapump_jobs was empty and I was able to import my data! I'm still wondering if it's because the job had attached sessions or it just takes some time to finish a job.

Category: Charlie's blog | Views: 4800 | Added by: Charley_Dixon | Tags: Oracle
Total comments: 0
Name *:
Email:
Code *: