Skip to Main Content
  • Questions

  • Alter script for shrinking space of a table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Astha.

Asked: February 16, 2023 - 12:08 pm UTC

Last updated: February 20, 2023 - 4:16 am UTC

Version: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Viewed 10K+ times! This question is

You Asked

Hello,

Is there any other way to run the alter scripts for shrinking space of particular tables in database other than manually running them.

Please find below scripts that I am referring to 

ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;
ALTER TABLE TABLE_NAME SHRINK SPACE CASCADE;
ALTER TABLE TABLE_NAME DISABLE ROW MOVEMENT;


there are many such tables for which I am archiving its data and moving/deleting it from the original one
So I need to run this alter scripts on regular basis after each archival process to clean up the space.

Kindly suggest.

and Chris said...

I'm struggling to see what the problem is here.

If you're running a process to archive data, you can include calls to do these operations as part of it at the end.

You could simplify by EXECUTE IMMEDIATEing these inside a procedure and the procedure. But ultimately you know when the archival is done and thus when it's beneficial to shrink the tables.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Maybe you don't want to shrink them

A reader, February 19, 2023 - 6:37 pm UTC

Hi Ashta,

You say you will do this at a regular basis, as part of an archiving process.
To me that sounds like you actually don't want to do it.

1. Table A at 100G
2. Your archiving runs, deletes 20G
3. You shrink table to 80G
4. Time goes, table A is back at 100G
5. Your archiving runs...
And so on

Just keep the table as is, the new rows will populate space from rows deleted.
Do not shrink it (Unless you have done some very special, one-off delete, and really need that space)

Else, and not knowing anything about your archiving process, but if you can - Partition your tables by some timestamp, drop oldest instead.

BR
Peter


Connor McDonald
February 20, 2023 - 4:16 am UTC

excellent points
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database