how to count uncommitted inserts

[gcruz]

user1 ran an sql insert which took a long time. which table/view/column does user2 query on how many rows the user1's sql inserted so far?

[sambavan]

check for the locks that are being held on the table also v$transactions view for more information

Sam

[jmodic]

(Generaly) there is no way to get the number of rows that have been inserted so far if the insert command is not finished yet.

However there are some exceptions (as always) if you use some tricks.

One such a trick would be to incorporate row level trigger on the inserting table that would incremet a counter stored in some other table. Of course you would have to use autonomous transactions in your trigger so that the running value in that counter should be visible from other sessions. But I belive this whole idea is not very realistic as it would slow down your insert operation considerably.

A better idea:
If your primary key in your inserting table gets populated from a sequence you could query the SYS.V$_SEQUENCES which will tell you exactly how many rows have been inserted so far without any additional overhead to your inserts.

You should connect as SYS (no other user can see V$_SEQUENCES) and run the following query periodicaly:

SELECT nextvalue FROM v$_sequences
WHERE sequence_owner = 'MY_USER' AND equence_name = 'MY_SEQ';

This tells you exactly how many numbers have been fetched from a sequence and if your mas insert is the only one on the system taht fetches from that sequence you can tell exactly how many rows have been inserted so far. Of course, if you are sattisfied with *aproximate* number and if you don't mind if your PK values have some "holes" (missing numbers) you can simply periodicaly do:

SELECT my_user.my_sequence.NEXTVAL FROM dual;

to keep track of the insert progress.

[shawish_sababa]

Hi

you can use utl_file to write to a log file on evry insert that you are doing. this way comes with its own overhead, i/o and resource overhead.

[jmodic]

Huh, this would *realy* be an overkill. Immagine doing an insert of 1.000.000 records. You would have to a) open file, b) write to it and c) close it - each of this operation would be repeated 1 MIO times!

[shawish_sababa]

hi jmodic

you are more then right. as i have writen, useing utl_file carrys it's own overhead, and not a light one i must say. any way, useing any option is for gcruz to decide.
by the way gcruz, you can create a counter in your programe and use dbms_output to print the status very x number of lines you fill like.

[jmodic]

Originally posted by shawish_sababa
by the way gcruz, you can create a counter in your programe and use dbms_output to print the status very x number of lines you fill like.

It's me again, and I don't want to be hair splitting, but the above won't work. DBMS_OUTPUT will show the output only when the whole insert operation is finished (to be more precise, when the PL/SQL block containing the insert is exited), not during the operation.

[shawish_sababa]

Hi jmodic

sorry, you are more then right!!! i'll revert on this answer!!! do'nt know what gut into me???

thank for pionting to me my mistake. hope your "hair splitting" can be reverted as well... (i'v tryed to cope the smily with one eye colsed, but invaine)