OraFAQ Forum: Server Administration » How To Calculate/Compute Space Size To Increase for the Undo Tabspace

Home » RDBMS Server » Server Administration » How To Calculate/Compute Space Size To Increase for the Undo Tabspace (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How To Calculate/Compute Space Size To Increase for the Undo Tabspace [message #690250]

Fri, 24 January 2025 14:34

wtolentino
Messages: 422
Registered: March 2005

Senior Member

Code: [Select all] [Show/ hide]

SQL> select tablespace_name, sum(bytes) free_space from dba_free_space where tablespace_name like 'UNDO%' group by tablespace_name;

TABLESPACE_NAME                                FREE_SPACE
---------------------------------------- ----------------
UNDOTBS1                                      16520052736

In a given table there's about 223,782,314 rows. About 37,194,314 rows will be deleted. I was advised by some of my dba colleagues to somehow increase the size of the UNDO to lessen the burden. Is there a way to calculate or compute the size to increase or added?

Thanks.

Thanks in advance.

[Updated on: Fri, 24 January 2025 14:41]

Report message to a moderator

Re: How To Calculate/Compute Space Size To Increase for the Undo Tabspace [message #690251 is a reply to message #690250]

Fri, 24 January 2025 16:30

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

I would think it would be more convenient and efficient to use AUTOEXTEND.

Report message to a moderator

Re: How To Calculate/Compute Space Size To Increase for the Undo Tabspace [message #690252 is a reply to message #690250]

Sat, 25 January 2025 00:56

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

If you ALTER SESSION ENABLE RESUMABLE before starting the delete, then if you do run out of undo space the deletion will just wait until you add more space.

Report message to a moderator

Re: How To Calculate/Compute Space Size To Increase for the Undo Tabspace [message #690255 is a reply to message #690252]

Mon, 27 January 2025 03:55

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

Do you need the delete to run as fast as possible?
If not you may be better off deleting the rows in chunks, say 1m at a time.

Report message to a moderator

Previous Topic:	How to find server process/scripts thats locking an account
Next Topic:	Slow DataPump with broken DB Links

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 18:02:56 CDT 2025