how to count uncommitted inserts
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: how to count uncommitted inserts

  1. #1
    Join Date
    Mar 2001
    Posts
    3
    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?

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    check for the locks that are being held on the table also v$transactions view for more information

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    (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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jul 2000
    Posts
    243
    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.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jul 2000
    Posts
    243
    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.

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Jul 2000
    Posts
    243

    Wink

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width