Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kok Heng.

Asked: September 26, 2024 - 7:07 am UTC

Last updated: October 01, 2024 - 5:24 pm UTC

Version: 12.2.0.1, 19.1.0, 19.24.0

Viewed 1000+ times

You Asked

Hi Tom,

Can provide a detail guide of how to release unused space in tables /tablespaces to OS Disk, Is there any slowness or lagging if shrink tables / tablespaces online? How to know what percentage has it been shrinking? Shall i start with shrink tables then following shrink tablespaces then lastly shrink datafiles? Your advice is much appreciated. Thanks

and Connor said...

The only think that gives space back to the OS is shrinking the tablespace, so my general rule of thumb if I need to get space back to the OS is to move objects to a new tablespace. There's a couple of options to explore

Option 1 - complete move

- You could literally do an "alter table move online" and "alter index rebuild online" where appropriate grab every single table/index from tablespace "A" to move it to tablespace "B", where tablespace "B" is define will a small datafile set to autoextend.
- At the end of the exercise, tablespace "B" will be just big enough to hold the data and tablespace "A" is now empty and can be dropped

Option 2 - selective move

- Sometimes a tablespace is large only because there are a few tables/indexes up near the high watermark of the datafile(s), and there might be some large holes in the existing tablespace. In this instance,
its a waste of time/effort to move everything.
- So you would do something

select * from dba_extents order by file_id, block_id desc

to see if you only need to move a few segments about to free up a large chunk of datafile space out near the hwm.

In 23ai, this all gets much easier with a native shrink tablespace command.

Rating

  (1 rating)

Comments

A reader, October 01, 2024 - 9:00 am UTC

If choose to shrink the tablespace, is there any impact to the system performance, blocking and locking?

What are the steps to lower the table HWM, then later lower the tablespace HWM, then do a tablespace shrink and lastly do a datafile resize to release the free space back to OS Disk?
Chris Saxon
October 01, 2024 - 5:24 pm UTC