0

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.

Franz
  • 119
  • 3
  • 8
  • you can get some idea with v$session_longops https://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_3021.htm#REFRN30227 - sofar. Also you mention inserting, but then use a query as an example. Not sure you can find how many rows you've queried so far but you may be able to get an idea of inserted number – kayakpim Jun 25 '15 at 09:55
  • try SQL%ROWCOUNT http://stackoverflow.com/questions/861983/number-of-rows-affected-by-an-update-in-pl-sql – Elisheva Wasserman Jun 25 '15 at 10:00
  • @Elisheva Wasserman: This is not my question - I would like to know the current rows processed **before** the statement has been finished (just like a status bar) – Franz Jun 26 '15 at 05:54

1 Answers1

0

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
kayakpim
  • 985
  • 2
  • 8
  • 28
  • Thanks for the tipp, but it does not help in my case. I guess, the main purpose of this view is to watch index rebuilding, gathering and such. The current executing SQL is not listed in V$SESSION_LONGOPS; the current operation is an anonymous PL/SQL-Block looping a very big table and inserting bit by bit (I do this to prevent getting ORA-01555) – Franz Jun 26 '15 at 06:02
  • plsql developer has a profiler for sql so you can see what is going on, you can also log to a table or file easily enough (maybe with an autonomous transaction) to see where you are so in this case it'll be far easier to log pl/sql than sql. Shame it doesn't mention this in your question. – kayakpim Jun 26 '15 at 08:26