Transaction in the undo tablespace

[dba01]

The application is incredibly slow and is not allowing the users to commit any transactions The reason to this is that my undo tablespace has been at 100% for a couple of hours now. Earlier today one of the developers had run a huge delete statement which might have caused this scenario. I had to kill his process cause the archive log destination filled up.
What query could I run to see which are the transactions in the undo tablespace and also what could I do to get the that cleared out faster without adding more space to the undo tablespace?
Would a bounce to the database solve this issue (this would be my last alternative).

Your input would be greatly appreciated.

DBA01

[yxez]

the transactions are too slow rolling back affected by hanged processes...
yes aborting can help...since after u start it up rolling back gets
faster

[pareshjavkar]

Hi,

But Be Careful, shutdown immediate also might hang up ...

[yxez]

not immediate....abort...or refresh the entire server...to release all
hanged up process

[dba01]

Thanks for all your responses.

But what helped me bring the percentage on the undo tablespace down was that I altered the system to set the undo_retention parameter to 0. This way the undo tablespace no longer held on to any of the transactions and the SMON was able to clean it out. But it was not immediate it did take a couple of hours to do so.

Thanks once again,
DBA01

[waitecj]

There will be an entry in v$transaction for all active transactions (even ones that are rolling back). Look at the column USED_UBLK (assuming your database version is either 9x or 10x) to see how many undo blocks they are using.

Honestly, you should look to have enough space available for you to create a larger undo tablespace if need be. You shouldn't leave yourself in a position where you cannot expand any tablespace if the need arises.

[satheeshb]

Hi,
UNDO tablespace 100% is normal. Can you run statspack to find out where is the bottleneck is ?

Regards,
Satheesh Babu.S