Processed number of rows for an ongoing SQL statement

Question

We copy big tables from one Oracle database and merge them to another database. Thus we have very long running insert statements.

I would like to know have many rows have already been processed. I know this can be done with Oracle, since PL/SQL-Developer can show the statistic in a HTML report.

I tried following SQL:

-- currently executing SQLs
select sm.SID, sm.SQL_ID, sm.STATUS, sm.SQL_TEXT, sm.LAST_REFRESH_TIME, sm.FIRST_REFRESH_TIME, 
       sp.OPERATION, sp.TIMESTAMP, sp.CARDINALITY as EST_ROWS,
       sp.OBJECT_OWNER, sp.OBJECT_NAME, sp.OBJECT_TYPE, sp.COST, sp.BYTES, sp.CPU_COST
  from V$SQL_MONITOR sm 
  inner join V$SQL_PLAN sp
    on sm.SQL_ID = sp.SQL_ID
 where sm.STATUS = 'EXECUTING'
 order by sm.LAST_REFRESH_TIME desc, sp.DEPTH;

I already tried also fields V$SQLSTATS.ROWS_PROCESSED, V$SESSTAT.VALUE (with STATISTIC# = 308), but nothing showed me the currently processed number of rows.

Does anyone know, how the currently processed number of rows of a specific SQL statement can be determined in Oracle 11g?

Thanks in advance.

Answer 1

See comment above, here's an example from www.dba-oracle.com showing amount of work done so far:

select
   sid,
   message
from
   v$session_longops
where
   sid = 13
order by
   start_time;

Here is a sample of the output, showing the progress of a long running CREATE INDEX statement.

SID MESSAGE
--- -------------------------------------------------------------------
 11 Table Scan:  CUST.PK_IDX: 732 out of 243260 Blocks done