OraFAQ Forum: Server Administration » Reclaiming Space From Tables

Home » RDBMS Server » Server Administration » Reclaiming Space From Tables (Oracle 11gR2 Solaris10)

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

Reclaiming Space From Tables [message #668552]

Thu, 01 March 2018 03:57

dsslim
Messages: 4
Registered: March 2018

Junior Member

Hi,

I'm tasked to reclaim spaces from the database. I've ran OEM segment advisor against the tablespaces and so far it recommends me to shrink the indexes. I'm puzzled that the tables are not shown. Does it mean that all the tables are not fragmented ? Or it will only be displayed if a certain level of fragmentation has reached ?

Is there a script to find the fragmentation level in the tables ? I'm also looking at Alter table shrink cascade...but I need to acertain that I'll reclaim some spaces else not worth the effort.

Please advise. TIA !

Report message to a moderator

Re: Reclaiming Space From Tables [message #668560 is a reply to message #668552]

Thu, 01 March 2018 06:32

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

Senior Member

Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

I would just run SHRINK SPACE CASCADE against all your tables. If there is no space to be reclaimed, it will be very quick. Are you aware that this operation will not reduce the size of the database? All it will do is free up space within the database.

As for "a script to find the fragmentation level in the tables" you would need to define what you mean by "fragmentation".

Report message to a moderator

Re: Reclaiming Space From Tables [message #668562 is a reply to message #668560]

Thu, 01 March 2018 07:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> I need to acertain that I'll reclaim some spaces
Reclaim from where to where?
Total disk space remains constant (unless & until you physically add new disk drive).

Report message to a moderator

Re: Reclaiming Space From Tables [message #668567 is a reply to message #668562]

Thu, 01 March 2018 23:44

dsslim
Messages: 4
Registered: March 2018

Junior Member

I'm trying to reclaim unused space from the tablespaces' datafiles and return them to OS.

Report message to a moderator

Re: Reclaiming Space From Tables [message #668568 is a reply to message #668567]

Fri, 02 March 2018 00:13

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

Senior Member

ALTER DATABASE DATAFILE ... RESIZE ... ;

It usually doesn't work unless you do a lot of segment reorganization first. And often not then, either. You may be better off looking at clearing out the diagnostic destination.

Report message to a moderator

Re: Reclaiming Space From Tables [message #668594 is a reply to message #668567]

Tue, 06 March 2018 03:57

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

And what happens when your database next needs to grow? This sounds like a task given to you by a manager with more time than experience on his/her hands.

Report message to a moderator

Previous Topic:	DBMS_RLS.ADD_POLICY
Next Topic:	UNDO TABLESPACE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Oct 23 22:26:59 CDT 2025