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

From: Anurag Verma <anuragdba_at_gmail.com>
Date: Sat, 26 Nov 2011 19:48:38 -0800
Message-ID: <CAFJhYoBKOuMtqO0q9suN0fT2pbRzumeRvjj4HfCf9ZucvUGD+w_at_mail.gmail.com>

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

*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

If I can find out the segments, I can move them to another tablespace.

I know, in OEM, we can use tablespace map and find out different segments.

Unfortunately, for this database I have only sqlplus to check the data dictionary.

No Toad or Grid/database control :(

-- 

Anurag Verma,
Database Administrator
ERCOT(Electric Reliability Council of Texas),
Texas 76574


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

Received on Sat Nov 26 2011 - 21:48:38 CST