222 lines
12 KiB
SQL
222 lines
12 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display datapump job status
|
|
* Parameter : 1 - Job owner name , this could also be passed as OWNER.JOB_NAME
|
|
* 2 - Datapump Job Name
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ ------------------------------------------------------------
|
|
* 12-FEB-16 Vishal Gupta Added conditional display of days to duration field
|
|
* 23-SEP-14 Vishal Gupta Added logic to auto-calculate max LPADING length for various outputs
|
|
* 14-FEB-13 Vishal Gupta Added more details and some formating
|
|
* 17-Dec-12 Vishal Gupta Modified to accept job owner and name separated by comma
|
|
* 26-Mar-12 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
set serveroutput on
|
|
|
|
VARIABLE owner VARCHAR2(30);
|
|
VARIABLE job_name VARCHAR2(30);
|
|
|
|
set term off
|
|
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 term 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
|
|
wip ku$_LogEntry; -- WIP
|
|
jd ku$_JobDesc; -- Job Description
|
|
js ku$_JobStatus; -- The job status from get_status
|
|
wsl ku$_WorkerStatusList; -- Worker status
|
|
error ku$_LogEntry;
|
|
|
|
max_completed_objects_length NUMBER;
|
|
max_completed_rows_length NUMBER;
|
|
max_completed_bytes_length NUMBER;
|
|
|
|
col_pad_len NUMBER := 50;
|
|
BEGIN
|
|
h1 := DBMS_DATAPUMP.attach(job_name => :job_name
|
|
, job_owner => CASE :owner WHEN '' THEN USER ELSE :owner END
|
|
);
|
|
|
|
/* Fetch Datapump Job Status */
|
|
dbms_datapump.get_status( handle => h1
|
|
, mask => dbms_datapump.ku$_status_wip
|
|
+ dbms_datapump.ku$_status_job_desc
|
|
+ dbms_datapump.ku$_status_job_status
|
|
+ dbms_datapump.ku$_status_job_error
|
|
, timeout => 0
|
|
, job_state => job_state
|
|
, status => sts);
|
|
|
|
wip := sts.wip;
|
|
jd := sts.job_description;
|
|
js := sts.job_status;
|
|
wsl := js.worker_status_list;
|
|
error := 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(RPAD('Job Owner : ' || jd.owner,col_pad_len) || RPAD('Remote Link : ' || jd.remote_link,col_pad_len) );
|
|
dbms_output.put_line(RPAD('Job Name : ' || jd.job_name,col_pad_len) || RPAD('Exp Start Time : ' || TO_CHAR(jd.exp_start_time,'DD-Mon-YY hh24:mi:ss'),col_pad_len) );
|
|
dbms_output.put_line(RPAD('Operation : ' || jd.operation,col_pad_len) || RPAD('Exp Global Name : ' || jd.exp_global_name,col_pad_len) );
|
|
dbms_output.put_line(RPAD('Job Mode : ' || jd.job_mode,col_pad_len) || RPAD('Exp Platform : ' || jd.exp_platform,col_pad_len) );
|
|
dbms_output.put_line(RPAD('Max Degree : ' || jd.max_degree,col_pad_len) || RPAD('Exp DB Version : ' || jd.exp_db_version,col_pad_len) );
|
|
dbms_output.put_line(RPAD('Instance : ' || jd.instance,col_pad_len) || RPAD('Creator Privs : ' || jd.creator_privs,col_pad_len) );
|
|
dbms_output.put_line(RPAD('Global Name : ' || jd.global_name,col_pad_len) || RPAD('SCN : ' || jd.scn,col_pad_len) );
|
|
dbms_output.put_line(RPAD('Platform : ' || jd.platform,col_pad_len) );
|
|
dbms_output.put_line(RPAD('DB Version : ' || jd.db_version,col_pad_len) );
|
|
dbms_output.put_line(RPAD('Job StartTime : ' || TO_CHAR(jd.start_time,'DD-Mon-YY hh24:mi:ss'),col_pad_len) );
|
|
dbms_output.put_line(RPAD('Duration : ' || CASE WHEN FLOOR(sysdate - jd.start_time) > 0 THEN FLOOR(sysdate - jd.start_time) || 'd ' ELSE '' END
|
|
|| FLOOR(MOD((sysdate - jd.start_time) , 1) * 24 ) || 'h '
|
|
|| FLOOR(MOD((sysdate - jd.start_time) * 24 , 1) * 60 ) || 'm '
|
|
|| FLOOR(MOD((sysdate - jd.start_time) * 24 * 60 , 1) * 60 ) || 's '
|
|
,col_pad_len) );
|
|
dbms_output.put_line(RPAD('Termin Reason : ' || jd.term_reason,col_pad_len) );
|
|
dbms_output.put_line('Logfile : ' || jd.log_file );
|
|
dbms_output.put_line('SQL File : ' || jd.sql_file );
|
|
|
|
ind := jd.params.first;
|
|
while ind is not null
|
|
loop
|
|
dbms_output.put_line('Parameter ' || ind || ' :'
|
|
|| ' Op - ' || jd.params(ind).param_op
|
|
|| ', ' || RPAD(jd.params(ind).param_name,25)
|
|
|| ' = ' || NVL(jd.params(ind).param_value_t ,jd.params(ind).param_value_n)
|
|
);
|
|
ind := jd.params.next(ind);
|
|
end loop;
|
|
|
|
dbms_output.put_line('------------------------------------------' );
|
|
dbms_output.put_line('Datapump Job Status' );
|
|
dbms_output.put_line('------------------------------------------' );
|
|
dbms_output.put_line(RPAD('State : ' || js.state,col_pad_len ) || RPAD('Total Bytes : ' || to_char(js.total_bytes,'999,999,999,999,999'),col_pad_len ) );
|
|
dbms_output.put_line(RPAD('Degree : ' || js.degree,col_pad_len ) || RPAD('Bytes Processed : ' || to_char(js.bytes_processed,'999,999,999,999,999'),col_pad_len ) );
|
|
dbms_output.put_line(RPAD('% Done : ' || js.percent_done || ' % <----- *****',col_pad_len ) || RPAD('Restart Count : ' || js.restart_count,col_pad_len ) );
|
|
dbms_output.put_line(RPAD('Phase : ' || js.phase,col_pad_len ) || RPAD('Error Count : ' || js.error_count,col_pad_len ));
|
|
|
|
ind := js.files.first;
|
|
dbms_output.put_line('DumpFiles : ' || SUBSTR(js.files(ind).file_name,1,CASE INSTR(js.files(ind).file_name,'/',-1)
|
|
WHEN 0 THEN INSTR(js.files(ind).file_name,'\',-1) - 1
|
|
ELSE INSTR(js.files(ind).file_name,'/',-1) -1
|
|
END
|
|
)
|
|
);
|
|
while ind is not null
|
|
loop
|
|
IF jd.operation = 'EXPORT' THEN
|
|
dbms_output.put_line('. : '
|
|
|| SUBSTR(js.files(ind).file_name,INSTR(js.files(ind).file_name,'/',-1)+1)
|
|
|| ' ( Size - ' || LPAD(to_char(ROUND(js.files(ind).file_bytes_written/1024/1024),'999,999,999'),13) || ' MB )'
|
|
);
|
|
END IF;
|
|
IF jd.operation = 'IMPORT' THEN
|
|
-- Odd file number
|
|
IF MOD(ind,3) = 1 THEN
|
|
dbms_output.put('. : ' || SUBSTR(js.files(ind).file_name,INSTR(js.files(ind).file_name,'/',-1)+1) );
|
|
END IF;
|
|
IF MOD(ind,3) = 2 THEN
|
|
dbms_output.put(', ' || SUBSTR(js.files(ind).file_name,INSTR(js.files(ind).file_name,'/',-1)+1) );
|
|
END IF;
|
|
IF MOD(ind,3) = 0 THEN
|
|
dbms_output.put_line(', ' ||SUBSTR(js.files(ind).file_name,INSTR(js.files(ind).file_name,'/',-1)+1) );
|
|
END IF;
|
|
END IF;
|
|
ind := js.files.next(ind);
|
|
end loop;
|
|
|
|
dbms_output.put_line(' ');
|
|
dbms_output.put_line('------------------------------------------' );
|
|
dbms_output.put_line('Datapump Worker Status' );
|
|
dbms_output.put_line('------------------------------------------' );
|
|
|
|
max_completed_objects_length := 1;
|
|
max_completed_rows_length := 1;
|
|
max_completed_bytes_length := 1;
|
|
ind := wsl.first;
|
|
while ind is not null
|
|
loop
|
|
IF LENGTH(wsl(ind).completed_objects) > max_completed_objects_length THEN
|
|
max_completed_objects_length := LENGTH(wsl(ind).completed_objects);
|
|
END IF;
|
|
IF LENGTH(ROUND(wsl(ind).completed_rows/1000)) > max_completed_rows_length THEN
|
|
max_completed_rows_length := LENGTH(ROUND(wsl(ind).completed_rows/1000));
|
|
END IF;
|
|
IF LENGTH(ROUND(wsl(ind).completed_bytes/1024/1024)) > max_completed_bytes_length THEN
|
|
max_completed_bytes_length := LENGTH(ROUND(wsl(ind).completed_bytes/1024/1024));
|
|
END IF;
|
|
ind := wsl.next(ind);
|
|
end loop;
|
|
|
|
ind := wsl.first;
|
|
while ind is not null
|
|
loop
|
|
|
|
/*
|
|
percent_done := 0;
|
|
BEGIN
|
|
SELECT *
|
|
into percent_done
|
|
FROM (select ROUND(NVL(l.sofar / l.totalwork,0) *100, 2) pct_done
|
|
from dba_datapump_sessions d
|
|
LEFT OUTER JOIN gv$session s ON s.inst_id = d.inst_id AND s.saddr = d.saddr
|
|
LEFT OUTER JOIN gv$process p ON s.inst_id = p.inst_id AND s.paddr = p.addr
|
|
LEFT OUTER JOIN gv$session_longops l ON l.inst_id = s.inst_id AND l.sid = s.sid
|
|
WHERE d.owner_name = jd.owner
|
|
AND d.job_name = jd.job_name
|
|
AND d.session_type = 'WORKER'
|
|
AND s.program like '%' || TRIM(wsl(ind).process_name) || '%'
|
|
ORDER BY l.start_Time desc)
|
|
WHERE ROWNUM=1
|
|
;
|
|
EXCEPTION
|
|
WHEN Others THEN
|
|
NULL;
|
|
END;
|
|
*/
|
|
dbms_output.put_line('Worker ' || LPAD(wsl(ind).worker_number,2) || ' :'
|
|
|| ' ' || wsl(ind).process_name -- || '(Inst:' || wsl(ind).instance_id || ')'
|
|
|| ' , ' || LPAD(wsl(ind).percent_done,2) || '% Done'
|
|
--|| ' ,' || LPAD(percent_done,6) || '% Done'
|
|
|| ' , Deg ' || wsl(ind).degree
|
|
|| ' , ' || RPAD(wsl(ind).state,9)
|
|
|| ' , Completed Objects ' || LPAD(wsl(ind).completed_objects,max_completed_objects_length) || '/' || TRIM(wsl(ind).total_objects)
|
|
|| ' , ' || LPAD(TRIM(to_char(wsl(ind).completed_rows/1000,'999999999')) , max_completed_rows_length) || ' K rows'
|
|
|| ' , Size ' || LPAD(TRIM(to_char(wsl(ind).completed_bytes/1024/1024,'99999999')), max_completed_bytes_length) || ' MB'
|
|
|| ' , ' || wsl(ind).schema || '.' || wsl(ind).name
|
|
|| CASE WHEN wsl(ind).partition IS NULL THEN '' ELSE '(' || wsl(ind).partition || ')' END
|
|
|| ' , ' || wsl(ind).object_type
|
|
);
|
|
ind := wsl.next(ind);
|
|
end loop;
|
|
DBMS_DATAPUMP.detach(h1);
|
|
end;
|
|
/
|
|
|
|
UNDEFINE 1
|
|
UNDEFINE 2
|
|
|
|
@@footer |