Oracle Tips   Donald K. Burleson

Oracle table/index max extent maximum

How can you tell if your objects are getting close to hitting their maximum extent limit?  This is quite easy to do.  If you are using version 7 of Oracle, then you can execute the maxext7.sqlscript, which will find all objects that are within five extents of their limit:

See Code depot for complete script

select

       owner,
       segment_name,
. . . .

       sys.dba_segments
where 

       max_extents - extents <= 5 and
       segment_type <> 'CACHE'
order by

      1,2,3;

If you are using version 8 or higher of Oracle, then you can use the maxext8.sql  script:

See Code depot for complete script

select

       owner,
       decode(partition_name,NULL,segment_name,segment_name ||

       '.' || partition_name) segment_name,
. . .   

       sys.dba_segments
where 

       max_extents - extents <= 5 and
       segment_type <> 'CACHE'
order by

      1,2,3;

The above is an excerpt from Oracle Performance Troubleshooting by Robin Schumacher.

It's only $19.95 and you can order it and get instant access to the Oracle scripts here:

http://www.rampant-books.com/book_2003_1_perf.htm

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.