Home » RDBMS Server » Server Administration » Reclaiming Space From Tables (Oracle 11gR2 Solaris10)
Reclaiming Space From Tables [message #668552] Thu, 01 March 2018 03:57 Go to next message
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 !

Re: Reclaiming Space From Tables [message #668560 is a reply to message #668552] Thu, 01 March 2018 06:32 Go to previous messageGo to next message
John Watson
Messages: 7619
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".
Re: Reclaiming Space From Tables [message #668562 is a reply to message #668560] Thu, 01 March 2018 07:34 Go to previous messageGo to next message
BlackSwan
Messages: 26201
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).
Re: Reclaiming Space From Tables [message #668567 is a reply to message #668562] Thu, 01 March 2018 23:44 Go to previous messageGo to next message
dsslim
Messages: 4
Registered: March 2018
Junior Member
I'm trying to reclaim unused space from the tablespaces' datafiles and return them to OS.
Re: Reclaiming Space From Tables [message #668568 is a reply to message #668567] Fri, 02 March 2018 00:13 Go to previous messageGo to next message
John Watson
Messages: 7619
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.
Re: Reclaiming Space From Tables [message #668594 is a reply to message #668567] Tue, 06 March 2018 03:57 Go to previous message
gazzag
Messages: 993
Registered: November 2010
Location: Bristol, 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.
Previous Topic: DBMS_RLS.ADD_POLICY
Next Topic: UNDO TABLESPACE
Goto Forum:
  


Current Time: Sat Oct 20 07:30:28 CDT 2018