102 lines
4.0 KiB
SQL
102 lines
4.0 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display datapump job status
|
|
* Parameter : 1 - Job owner name
|
|
* 2 - Datapump Job Name
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 26-Mar-12 Vishal Gupta First Draft
|
|
*
|
|
*
|
|
*/
|
|
|
|
VARIABLE owner VARCHAR2(30);
|
|
VARIABLE job_name VARCHAR2(30);
|
|
|
|
|
|
BEGIN
|
|
:owner := upper('&&1');
|
|
:job_name := upper('&&2');
|
|
IF INSTR(:owner,'.') > 0 THEN
|
|
:job_name := SUBSTR(:owner, INSTR(:owner,'.') + 1 ) ;
|
|
:owner := SUBSTR(:owner, 1 , INSTR(:owner,'.') - 1 ) ;
|
|
END IF;
|
|
END;
|
|
/
|
|
|
|
set serveroutput on
|
|
|
|
DECLARE
|
|
ind NUMBER; -- Loop index
|
|
h1 NUMBER; -- Data Pump job handle
|
|
percent_done NUMBER; -- Percentage of job complete
|
|
job_state VARCHAR2(30); -- To keep track of job state
|
|
sts ku$_Status; -- The status object returned by get_status
|
|
jd KU$_JobDesc; -- Job Description
|
|
js ku$_JobStatus; -- The job status from get_status
|
|
wsl ku$_WorkerStatusList; -- Worker status
|
|
wip ku$_logentry;
|
|
BEGIN
|
|
h1 := DBMS_DATAPUMP.attach(job_name => :job_name
|
|
, job_owner => CASE :owner WHEN '' THEN USER ELSE :owner END
|
|
);
|
|
|
|
dbms_datapump.get_status( handle => h1
|
|
, mask => dbms_datapump.ku$_status_job_error
|
|
+ dbms_datapump.ku$_status_job_desc
|
|
+ dbms_datapump.ku$_status_job_status
|
|
+ dbms_datapump.ku$_status_wip
|
|
, timeout => 0
|
|
, job_state => job_state
|
|
, status => sts);
|
|
|
|
js := sts.job_status;
|
|
jd := sts.job_description;
|
|
wsl := js.worker_status_list;
|
|
--wip := sts.wip;
|
|
wip := sts.ERROR;
|
|
|
|
dbms_output.put_line(' ' );
|
|
dbms_output.put_line('------------------------------------------' );
|
|
dbms_output.put_line('Datapump Job Details' );
|
|
dbms_output.put_line('------------------------------------------' );
|
|
dbms_output.put_line('Job Owner : ' || jd.owner );
|
|
dbms_output.put_line('Job Name : ' || jd.job_name );
|
|
dbms_output.put_line('Operation : ' || jd.operation );
|
|
dbms_output.put_line('Job Mode : ' || jd.job_mode );
|
|
dbms_output.put_line('Remote Link : ' || jd.remote_link );
|
|
dbms_output.put_line('Platform : ' || jd.platform );
|
|
dbms_output.put_line('Exp Platform : ' || jd.exp_platform );
|
|
dbms_output.put_line('Global Name : ' || jd.global_name );
|
|
dbms_output.put_line('Exp Global Name : ' || jd.exp_global_name );
|
|
dbms_output.put_line('Job Start Time : ' || TO_CHAR(jd.start_time,'DD-Mon-YY hh24:mi:ss') );
|
|
dbms_output.put_line('Exp Start Time : ' || TO_CHAR(jd.exp_start_time,'DD-Mon-YY hh24:mi:ss') );
|
|
dbms_output.put_line('SCN : ' || jd.scn );
|
|
dbms_output.put_line('Max Degree : ' || jd.max_degree );
|
|
dbms_output.put_line('Termin Reason : ' || jd.term_reason );
|
|
dbms_output.put_line('Logfile : ' || jd.log_file );
|
|
dbms_output.put_line('SQL File : ' || jd.sql_file );
|
|
|
|
dbms_output.put_line('------------------------------------------' );
|
|
dbms_output.put_line('Datapump Job Log' );
|
|
dbms_output.put_line('------------------------------------------' );
|
|
|
|
ind := wip.first;
|
|
while ind is not null
|
|
loop
|
|
dbms_output.put_line( wip(ind).loglinenumber || ' - ' || wip(ind).logtext || ' , Error Number - ' || wip(ind).errornumber );
|
|
|
|
ind := wip.next(ind);
|
|
end loop;
|
|
|
|
DBMS_DATAPUMP.detach(h1);
|
|
end;
|
|
/
|
|
|
|
@@footer |