Home » RDBMS Server » Server Administration » unlocking a object
unlocking a object [message #191473] Wed, 06 September 2006 08:25 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I was deleting around 4000000 records from a table. While deleting the there was a error in undo tablespace "Failure to extend rollback segment because of 30036 condition"

I've added a datafile to the undo-tablespace and killed the seesion which was deleting the records. Now the table has been locked. can I unlock the table.

Regards,
Ronald.
Re: unlocking a object [message #191480 is a reply to message #191473] Wed, 06 September 2006 09:01 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>can I unlock the table.
Apparently not.
If you are real desparate, bounce the DB.
Re: unlocking a object [message #191686 is a reply to message #191473] Thu, 07 September 2006 14:07 Go to previous messageGo to next message
rkl1
Messages: 97
Registered: June 2005
Member
Seems like after the failure of the undo, it was rolling back your delete transactions and even you kill the session or bounce it, oracle will rollback those delete transactions to maintain the transaction integrity.

Deletes like updates create massive undo generation. First figure out whether the delete restricted to this particular table or it is referred by another tables with FK-PK relationship with cascade delete. If that occurs, then 1 delete on your table could create many simultaneous deletes on other tables to maintain the referential integrity.

Instead of one delete command, although it is most efficient, you could run it from pl/sql block and commit after a certain number of rows that would protect you from undo running out of space.

Good Luck.
Re: unlocking a object [message #191835 is a reply to message #191686] Fri, 08 September 2006 05:28 Go to previous message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Suppose if I want to delete all the records can I use truncate. Will it use rollback???


Ronald.
Previous Topic: Rebuilding Indexes
Next Topic: Problem in dbw0 trace file
Goto Forum:
  


Current Time: Sun Dec 04 12:51:24 CST 2016

Total time taken to generate the page: 0.05162 seconds