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

From: Denis <denis.sun_at_yahoo.com>
Date: Sun, 27 Nov 2011 18:27:38 -0800 (PST)
Message-ID: <1322447258.53912.YahooMailNeo_at_web161805.mail.bf1.yahoo.com>



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
Received on Sun Nov 27 2011 - 20:27:38 CST

Original text of this message