Skip to Main Content
  • Questions
  • Finding objects with extents at the end of a datafile

Breadcrumb

Question and Answer

Tom Kyte

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

Comments

Kiro, May 11, 2004 - 7:45 am UTC

Nice and clear.
Thanks.

Objects in TABLESPACE

Rajeshwaran, Jeyabal, September 28, 2009 - 8:38 am UTC

rajesh@IRADSDB> SELECT default_tablespace, temporary_tablespace
  2  FROM dba_users
  3  WHERE username ='RAJESH'
  4  /

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TS_IRADS_RPT_D                 TEMP

rajesh@IRADSDB> SELECT COUNT(*) FROM USER_OBJECTS;

  COUNT(*)
----------
       341


Tom,

From the above two queries i know the DEFAULT Tablespace mapped to the user RAJESH and Total number of objects Created by that USER.
My Question , Is there any way to list all the objects Currently available in the TableSpace TS_IRADS_RPT_D?
If Possible can you please show me how that can be achieved?


Tom Kyte
September 30, 2009 - 7:07 am UTC

describe user_segments

query that view