Home » SQL & PL/SQL » SQL & PL/SQL » delete progress
delete progress [message #255911] Thu, 02 August 2007 05:57 Go to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
can any one help me .....
I want to see the progress of a delete operation.or how many rows have been deleted when the delete operation is running.

thanx.
tanmoy.
Re: delete progress [message #255912 is a reply to message #255911] Thu, 02 August 2007 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.

Regards
Michel
Re: delete progress [message #255939 is a reply to message #255912] Thu, 02 August 2007 08:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If your delete will take longer than 6 seconds, and you've got timed_statistics set, and you've got gathered stats, then you can look in v$session_longops, which will show you how many blocks have been processed, and what the estimated total number of blocs to process is
Re: delete progress [message #256194 is a reply to message #255939] Fri, 03 August 2007 06:51 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Yea if you are doing delete for example using full scan on your table
see more Overview of long running operations in Oracle at http://www.gplivna.eu/papers/v$session_longops.htm

The other option is to query v$transaction and look at used_ublk (used blocks) and used_urec (used records) values.
They don't show precisely the deleted amount but at least you'll know the trend and if they are increasing it means delete is not completed yet.

You can try to delete for example 1000 rows and then you can estimate number for used_urec and used_ublk if you want to delete 1M or more rows. Of course for the same table in the same environment because I suspect indexes also affects content of above entioned columns in v$transaction.

Gints Plivna
http://www.gplivna.eu
Re: delete progress [message #256466 is a reply to message #256194] Sat, 04 August 2007 23:44 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
thanx .
Re: delete progress [message #256484 is a reply to message #255911] Sun, 05 August 2007 00:32 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.dbforums.com/showthread.php?t=1621017
Previous Topic: problem in updating table
Next Topic: Globalization support
Goto Forum:
  


Current Time: Sat Dec 10 16:34:40 CST 2016

Total time taken to generate the page: 0.11390 seconds