How to check the progress of long inserts in oracle

Question

I am trying to insert 1 million records after doing some calculations on every row in oracle. 15 hours have passed and it is still working. When I write a select query in this table, it shows nothing. I do not know where my inserted data goes for each insert.

So my question is, is there any way to check how many rows are still inserting by doing a long insert into the oracle table, thanks.

Answer 1

It depends on whether you are embedding in SQL or PL/SQL . When using PL/SQL , you have your own ways of getting the number of rows already processed; you can, of course, write your own program.

Coming to SQL , I can present two ways:

• V $ SESSION_LONGOPS
• V $ TRANSACTION

Most GUI based tools would have a good graphical representation for representing long operations. You can request -

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

In the V$TRANSACTION view, you can indicate whether any transaction is waiting. If your INSERT complete and COMMIT released, the transaction will complete. You can join with v$session . You can request -

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