How to check progress of long running insertions in oracle

Question

I am trying to insert 1 million record after performing some calculation on each row in oracle. 15 hours gone and it is still in works. When i write a select query on this table it shows nothing. I don't know where is my inserted data going on each insert.

So my question is that, is there any way to check how many rows insert till now while performing long running insertion in oracle table, thanks.

Answer 1

It depends whether you are doing the insertion in SQL or PL/SQL. While using PL/SQL you have your own ways to get the number of rows already been processed, you can of course write your own program.

Coming to SQL, I can think of two ways :

• V$SESSION_LONGOPS
• V$TRANSACTION

Most of the GUI based tools would have nice graphical representation for the long operations view. You can query -

SELECT ROUND(SOFAR*21411/TOTALWORK)
FROM V$SESSION_LONGOPS
WHERE username     = '<username>'
AND TIME_REMAINING > 0

The V$TRANSACTION view can tell you whether any transaction is still pending. If your INSERT is completed and COMMIT is issued, the transaction would be completed. You can join it with v$session. You can query -

SELECT     .... 
from       v$transaction t 
inner join v$session s 
ON         t.addr = s.taddr;