How to find how many rows are inserted

[prince_jr]

Hi, I am inserting rows into table using " Insert into .....select * from table".
How do I know how many rows are inserted when still the insert statement is running.
I appriciate your help
Thanks

[tamilselvan]

One way is:
Use PL/SQL with BULK COLLECTION option.
You come to know how many rows inserted by using dbms_output.put_line in a FORALL loop.

Tamil

[bhallar]

dbms_output.putline() within FORALL loop will not display the output during insertion, it will show at the end of insertions, which is same as with insert into .. select *

[stecal]

Why does it matter that you know how many rows have been inserted while the insert is taking place?

[abhaysk]

Originally posted by prince_jr
Hi, I am inserting rows into table using " Insert into .....select * from table".
How do I know how many rows are inserted when still the insert statement is running.
I appriciate your help
Thanks

V$Sqlarea

[jmodic]

Originally posted by abhaysk
V$Sqlarea

?????

[tamilselvan]

The rows_processed column value in v$sqlarea is posted after insert operation completes its entire work.
This may not help to figure out how many rows are inserted so far when the sql is running.

Tamil

[slimdave]

You might get something useful from V_$SESSION_LONGOPS

[adewri]

Originally posted by slimdave
You might get something useful from V_$SESSION_LONGOPS

Excellent...

[julian]

Originally posted by slimdave
You might get something useful from V_$SESSION_LONGOPS

Yes, here is how:

Assume that before you start the "INSERT INTO TABLE SELECT ..." statement you check the number of rows you are supposed to insert, say 21411 (you run first select count(*) from table).

select round(SOFAR*21411/TOTALWORK)
from V$SESSION_LONGOPS
where username = 'SCOTT' and TIME_REMAINING > 0