RE: How to find out the segments (whether it is table or index) at the end of each datafile in a tablespace.

From: Holvoet, Jo <jo.holvoet_at_thomascook.be>
Date: Mon, 28 Nov 2011 09:42:39 +0100
Message-ID: <CF9A39CD0F65EA49ADF70FCBF9BC2FF702AA0391_at_SW-GNETCW-MBX02.tcads.thomascook.com>

Something else to remember that I don't think anyone has mentioned yet : the recyclebin, if turned on, can also get in your way in these situations.

Table sys.recyclebin$ will help you there (unless there's a better way ?) :

select file#                   f,
       max(block# + space - 1) hwm

from sys.recyclebin$
group by file#

Of course if you're reorganizing, just purging the recyclebin shouldn't be an issue.

mvg / regards,
Jo Holvoet    

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Denis Sent: maandag 28 november 2011 3:28
To: oracle-l_at_freelists.org; Anurag Verma Subject: Re: How to find out the segments (whether it is table or index) at the end of each datafile in a tablespace.

Recently I have a need to reorganize a 7T database. We estimated we can save about 2-3 T after reorg. I used several scripts to guide the reorg, including the one to find the segments at the end of each datafile. The query was obtained from an Oracle-L member originally ( I forgot from who). the idea is to list the segment order by the max(block_id), the segment with high max(block_id) should be at the end of the datafile.  I blogged my experience and  scripts here: http://oracle-study-notes.blogspot.com/2011/11/reorganize-table-and-index.html. The reorg is still going on since we need several downtime to finally release the space at OS level.  Hope this helps.
 

Denis
 
 

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Anurag Verma Sent: Saturday, November 26, 2011 10:49 PM To: ORACLE-L
Subject: How to find out the segments (whether it is table or index) at the end of each datafile in a tablespace. how to find out the segments (whether it is table or index) at the end of each datafile in a tablespace.
I am trying to reduce the sizes of several datafiles, but getting the below error message.

alter database datafile '/u01/oradata/testdb/users01.dbf' resize 12415139840

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Nov 28 2011 - 02:42:39 CST