UNDO Tablespace problems

[patnams]

Hi,

I was trying to do benchmarking for clustered tables. I forgot a Where condidion on a update statement. So, this one completely filled my UNDO tablespace in Automatic Undo Management. I realized my mistake and killed the session/process that was still doing the update from Unix directly.

Now, when I see the Undo, it is full. I amnot able to do any other operations. I get ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO01'.

I restarted the database, still same problem.

I created another Automatically Managed TEMPUNDO and set this one as active. Then took the original one (UNDO01) offline and tried to drop it. It says that it has active segements.

I put the database in Manual Undo mode using SYSTEM TBS, took my original UNDO tablespace UNDO01 offline and tried to drop it, it says some segments are active, so cannot drop the tablespace.

The DB is running normally, I can perform "Selects" without problem.

I got back the DB to original state but the Undo tablespace is full.

Any ideas what I should do? Please help.

I am on Oracle 9.2.0.5, IBM AIX.

[davey23uk]

probably rolling back your large transaction - you will have to wait i think

[patnams]

U'r right. It resolved itself after almost 5 hours. Thanks