apart from the fact the is does no work ...
No matter if I type the jobname in a litteral I get and erro from the ATTACH (job not found)
with
FUNCTION get_worker_status (
p_job_name varchar2, P_owner_name varchar2, p_worker number
) RETURN varchar2
AS
ind NUMBER;
h1 NUMBER;
percent_done NUMBER;
job_state VARCHAR2(30);
js ku$_JobStatus;
ws ku$_WorkerStatusList;
sts ku$_Status;
w_result varchar2(300) := 'No Workers found';
BEGIN
dbms_output.put_line('Here we go ... ' || p_job_name || '-' ||p_owner_name || '-' || p_worker);
h1 := DBMS_DATAPUMP.attach(p_job_name, p_owner_name);
dbms_output.put_line('We dont here at all');
dbms_datapump.get_status(h1,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip, 0, job_state, sts
);
js := sts.job_status;
ws := js.worker_status_list;
dbms_output.put_line('*** Job percent done = ' || to_char(js.percent_done));
dbms_output.put_line('restarts - '||js.restart_count);
ind := ws.first;
while ind is not null loop
dbms_output.put_line(ws(ind).worker_number);
if ws(ind).worker_number = p_worker then
w_result :=
ws(ind).worker_number || ','
|| ws(ind).state || ','
|| ws(ind).schema || ','
|| ws(ind).NAME || ','
|| ws(ind).object_type || ','
|| ws(ind).degree || ','
|| ws(ind).percent_done || ','
|| ws(ind).COMPLETED_ROWS
;
end if;
ind := ws.next(ind);
end loop;
DBMS_DATAPUMP.detach(h1);
return w_result;
/*
exception
when others then
if sqlcode = -31626 then return 'SQL code - 31626 ?';
else raise;
end if; */
end;
select ss.sid
, get_worker_status(s.job_name, s.owner_name, to_number(substr(r.name,instr(r.name,'.',-1)+1,2))) worker_status
, to_number(substr(r.name,instr(r.name,'.',-1)+1,2)) worker
, s.job_name
, j.state
, to_char(ss.logon_time,'dd/mm HH24:mi:ss') job_started
--, j.job_mode
--, j.degree
, dbms_rcvman.Sec2DisplayTime(l.time_remaining) remain
, round((l.sofar/l.totalwork)*100,2) "done%"
--, s.owner_name
, sq.sql_text
, l.message
--, l.totalwork
--, l.sofar
--, round((l.sofar/l.totalwork)*100,2) done
--, l.sql_id
, ss.event
, ss.state
, ss.seconds_in_wait
from dba_datapump_jobs j
left outer join dba_datapump_sessions s on (s.job_name = j.job_name)
left outer join v$session ss on (ss.saddr = s.saddr)
left outer join v$sql sq on (ss.sql_id = sq.sql_id)
left outer join v$session_longops l on (l.sql_id = ss.sql_id)
left outer join dba_resumable r on (r.session_id = ss.sid)
WHERE 1=1
and ss.module='Data Pump Worker'
--and l.time_remaining > 0
order by job_name, sid, l.start_time
;