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@xxxxxxxxxxxxx>
- To: <denis.sun@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>, "Anurag Verma" <anuragdba@xxxxxxxxx>
- Date: Mon, 28 Nov 2011 09:42:39 +0100
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Denis
Sent: maandag 28 november 2011 3:28
To: oracle-l@xxxxxxxxxxxxx; 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
