Home » SQL & PL/SQL » SQL & PL/SQL » How to check inserted records so far while executing a insert statement.
How to check inserted records so far while executing a insert statement. [message #328319] Thu, 19 June 2008 14:04 Go to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
Hello,
I am running an Insert into tableA select * from my_table
It takes around 30 minutes to load all of the data.
How to check how many records were inserted while the query is
still in process?

[Updated on: Thu, 19 June 2008 14:05]

Report message to a moderator

Re: How to check inserted records so far while executing a insert statement. [message #328321 is a reply to message #328319] Thu, 19 June 2008 14:10 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>How to check how many records were inserted while the query is still in process?
You do NOT.
Unless & until a COMMIT is issued, no other session can "see" these records.

[Updated on: Thu, 19 June 2008 14:10] by Moderator

Report message to a moderator

Re: How to check inserted records so far while executing a insert statement. [message #328322 is a reply to message #328321] Thu, 19 June 2008 14:15 Go to previous messageGo to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
yes there is commit issued.
Re: How to check inserted records so far while executing a insert statement. [message #328325 is a reply to message #328322] Thu, 19 June 2008 14:54 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
victory_nag wrote on Thu, 19 June 2008 12:15
yes there is commit issued.

COMMIT can only be issued after Insert into tableA select * from my_table is 100% complete.
The session doing the INSERT does not get control back until after all the rows have been successfully inserted & has chance to COMMIT
So my initial response is still valid & correct.

[Updated on: Thu, 19 June 2008 14:55] by Moderator

Report message to a moderator

Re: How to check inserted records so far while executing a insert statement. [message #328327 is a reply to message #328322] Thu, 19 June 2008 15:11 Go to previous messageGo to next message
JackyShu
Messages: 25
Registered: May 2008
Junior Member
victory_nag wrote on Thu, 19 June 2008 15:15
yes there is commit issued.

are you going something like this?
loop
fetch into bulk
insert into table a
commit;
exit of loop when end of cursor
end of loop;

if this is the case, you should be able to see those commited rows. but this is not a good approach, try SQL instead of PL/SQL if possible.
also, if you really want to know how many rows inserted, try change ISOLATION level to see dirty data.
Re: How to check inserted records so far while executing a insert statement. [message #328334 is a reply to message #328319] Thu, 19 June 2008 15:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>try change ISOLATION level to see dirty data.
Please post reproducible coded example that demonstrates this can be done using Oracle, because I don't believe it can be done.
Re: How to check inserted records so far while executing a insert statement. [message #328338 is a reply to message #328334] Thu, 19 June 2008 15:42 Go to previous messageGo to next message
JackyShu
Messages: 25
Registered: May 2008
Junior Member
anacedent wrote on Thu, 19 June 2008 16:32
because I don't believe it can be done.

you are right, forgot Oracle different than ANSI SQL, there is no READ UNCOMMITTED in Oracle.
my bad.
Re: How to check inserted records so far while executing a insert statement. [message #328350 is a reply to message #328338] Thu, 19 June 2008 17:10 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
search asktom.oracle.com for v$session_longops
Re: How to check inserted records so far while executing a insert statement. [message #328351 is a reply to message #328350] Thu, 19 June 2008 18:10 Go to previous message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
thanks for all the replies...
Previous Topic: Equivalent of dateadd(MS Acess) in oracle
Next Topic: Trim Out Special Character...
Goto Forum:
  


Current Time: Fri Dec 09 23:35:52 CST 2016

Total time taken to generate the page: 0.07864 seconds