How to find how many rows are inserted
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 41

Thread: How to find how many rows are inserted

  1. #1
    Join Date
    Oct 2003
    Posts
    38

    How to find how many rows are inserted

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  3. #3
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    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 *

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Why does it matter that you know how many rows have been inserted while the insert is taking place?

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: How to find how many rows are inserted

    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
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Re: How to find how many rows are inserted

    Originally posted by abhaysk
    V$Sqlarea
    ?????
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You might get something useful from V_$SESSION_LONGOPS
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by slimdave
    You might get something useful from V_$SESSION_LONGOPS
    Excellent...
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

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