Oracle Datapump SQL minitoring w用SQL (非PL/SQL)

我在11.2和12.1上，想用纯SQL监视datapump会话 (我不允许在数据库上创建对象)。

我可以使用以下方式监控:

select ss.sid
, s.job_name
, j.state
--, 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 join dba_datapump_sessions s on (s.job_name = j.job_name)
left join v$session ss            on (ss.saddr = s.saddr)
left join v$sql sq                on (ss.sql_id = sq.sql_id)
left join v$session_longops l     on (l.sql_id = ss.sql_id)
WHERE 1=1
and ss.module='Data Pump Worker'
--and l.time_remaining > 0
order by sid, l.start_time
;

-----------

但是我想从各个工人那里获得信息-例如在ie中。状态命令从附加到datadump会话。我可以使用PL/SQL这样得到它，但我正在寻找纯SQL而不创建对象:

DECLARE
  ind NUMBER;              
  h1 NUMBER;               
  percent_done NUMBER;     
  job_state VARCHAR2(30);  
  js ku$_JobStatus;        
  ws ku$_WorkerStatusList; 
  sts ku$_Status;          
BEGIN
h1 := DBMS_DATAPUMP.attach('SYS_IMPORT_FULL_01', 'SYSTEM');
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 || ',' 
        || ws(ind).state || ',' 
        || ws(ind).schema || ',' 
        || ws(ind).NAME || ',' 
        || ws(ind).object_type || ',' 
        || ws(ind).degree || ',' 
        || ws(ind).percent_done || ',' 
        || ws(ind).COMPLETED_ROWS
        );

 --   dbms_output.put_line('rows completed - '|| ws(ind));
    ind := ws.next(ind);
  end loop;
DBMS_DATAPUMP.detach(h1);
end;
/

我可以在12.1做一个内联函数，它的api调用？或者我可以使用内部视图来获取工人的身份？

关于DK的梅特

你的例子没有创建任何对象？这只是一个匿名的街区。

无论如何，从12.1开始，您可以在w跟子句中定义PL/SQL函数。所以你可以做这样的事情:

with 
FUNCTION get_job ( 
  job_name varchar2, owner_name varchar2 
) RETURN INT AS
  ind NUMBER;              
  h1 NUMBER;               
  percent_done NUMBER;     
  job_state VARCHAR2(30);  
  js ku$_JobStatus;        
  ws ku$_WorkerStatusList; 
  sts ku$_Status;          
BEGIN
h1 := DBMS_DATAPUMP.attach(job_name, owner_name);
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 || ',' 
        || ws(ind).state || ',' 
        || ws(ind).schema || ',' 
        || ws(ind).NAME || ',' 
        || ws(ind).object_type || ',' 
        || ws(ind).degree || ',' 
        || ws(ind).percent_done || ',' 
        || ws(ind).COMPLETED_ROWS
        );

 --   dbms_output.put_line('rows completed - '|| ws(ind));
    ind := ws.next(ind);
  end loop;
  DBMS_DATAPUMP.detach(h1);
  return 1;
exception
  when others then
    if sqlcode = -31626 then return 1;
    else raise;
    end if;
end;
  select job_name , owner_name, get_job ( job_name , owner_name ) 
  from   dba_datapump_jobs;
/

JOB_NAME                OWNER_NAME   GET_JOB(JOB_NAME,OWNER_NAME)   
SYS_IMPORT_SCHEMA_04    CHRIS                                     1 
SYS_IMPORT_TABLE_01     CHRIS                                     1 
SYS_IMPORT_SCHEMA_11    CHRIS                                     1 
SYS_IMPORT_TABLE_04     CHRIS                                     1 
IMPORT_JOB_SQLDEV_206   CHRIS                                     1 
SYS_IMPORT_TABLE_05     CHRIS                                     1 
SYS_IMPORT_TABLE_03     CHRIS                                     1 
SYS_IMPORT_TABLE_06     CHRIS                                     1 
SYS_IMPORT_SCHEMA_10    CHRIS                                     1