Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

DBMS_DATAPUMP; how to get the log file of a job?

Andrei KübarDec 23 2011 — edited Dec 23 2011
Hi

I want the user to be able to see the logfile of his job from another session.

this is my procedure
create or replace procedure get_job_log (p_job_name IN varchar2 )
is
hdl_job  number;
l_job_state     VARCHAR2 (20);
l_status        sys.ku$_Status1010;
l_job_status    sys.ku$_JobStatus1010;
l_job_logentry sys.ku$_LogEntry1010;
l_job_logline  sys.ku$_LogLine1010;

begin
 hdl_job := DBMS_DATAPUMP.ATTACH(
                                     job_name   => p_job_name
                                    ,job_owner  => 'CLONE_USER'
                                    ) ;
 DBMS_DATAPUMP.GET_STATUS(
   handle  => hdl_job
   ,mask   => dbms_datapump.ku$_status_job_error + dbms_datapump.ku$_status_job_status + dbms_datapump.ku$_status_wip
   --,timeout   => 15
   ,job_state =>l_job_state
   ,status    =>l_status);
 l_job_logentry:=l_status.wip ;
 for x in l_job_logentry.first .. l_job_logentry.last loop
   dbms_output.put_line (l_job_logentry(x).LogText) ;
 end loop; 
 dbms_datapump.detach(hdl_job);
end;
/
when I run it for the first time, it works... kindof...

but my problem is that if I try running it again I get:

ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 902
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3407

if I close sqlplus session , open new one and run - it works. So what is the issue here? Is detach not enough? What else should I do?

my version is 11.1.0.6

UPDATE.

Looks like the above is not true. It doesn't error if it runs to the end of code... The problem seems to happen only when exception occures and .detach is not called. So this is not an issue anymore.

But I still don't get a decent performance from the .get_status function. Sometimes it just hangs and errors on timeout.

Still the question is: How can I get the LOG of the session using DBMS_DATAPUMP from another session and output it on the screen?...

Edited by: Andrei Kübar on Dec 23, 2011 12:44 PM

Edited by: Andrei Kübar on Dec 23, 2011 12:57 PM

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 20 2012
Added on Dec 23 2011
8 comments
2,726 views