Thanks for the question, Kurt.
Asked: May 10, 2004 - 10:05 am UTC
Last updated: September 30, 2009 - 7:07 am UTC
Version: 9.2.0
Viewed 10K+ times! This question is
You Asked
Hi Tom,
i have a tablespace (lmt) with about 1000 objects (500 tables, 500 indexes). the tablespace consists of 3 datafiles (3 * 4 gb). after some reorganization one datafile is almost empty. but resizing this datafile fails, because some objects have extents at the end of the datafile.
is there a simple way to find the object(s) to which these extents belong?
i could write a sql script to move all tables of this tablespace and another to rebuild all unusable indexes, but i am sure i can reclaim the space of the datafile, if i move only some objects.
thanks
and Tom said...
ops$tkyte@ORA9IR2> select *
2 from (
3 select owner, segment_name, segment_type, block_id
4 from dba_extents
5 where file_id = ( select file_id
6 from dba_data_files
7 where file_name = '/home/ora9ir2/oradata/ora9ir2/system.dbf' )
8 order by block_id desc
9 )
10 where rownum <= 5
11 /
OWNER SEGMENT_NAME SEGMENT_T BLOCK_ID
----- ------------------------------ --------- ----------
SYS I_AUD1 INDEX 73625
SYS AUD$ TABLE 73617
MDSYS SYS_IL0000026154C00041$$ LOBINDEX 73609
SYS IDL_CHAR$ TABLE 73481
SYS IDL_UB2$ TABLE 73353
finds the 5 "outer most" segments in a file.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment