> Hello,
>
> I am trying to maintain a database where its tables are constantly
> reaching max extents. This is caused by bad sizing when the database
> was originally created. (Not me, honest). I am using a package which
> allows me to re-organise the troublesome tables. I set the initial
> size to the current size of the table and set the next extent to a
> larger figure. This as you will already know will bring the current
> extent back down to 1.
>
> Please can you help? - I want to write an SQL script that I can run
> every day on the base that will inform me of any table that is
> approaching its max extents. Hopefully this will give me a chance to
> be proactive rather than reactive.
>
> Do you have any handy hints or SQL script that may help?
>
> Thankyou,
>
> Martin.
>
Martin,
I do have a script that will report any objects that are near the MAXEXTENT
limit. The MAXEXTENT will be 121 by default if the block size is 2K. It will be
249 if the block size is 4K. The previous person may have overwriten the default
for any of the objects.
To find objects that need defragmenting, use the following script:
SELECT SUBSTR(owner||'.'||segment_name,1,50) OBJECT,
EXTENTS
FROM DBA_SEGMENTS
WHERE EXTENTS > 5
ORDER BY EXTENTS;
Now, to find all objects that are nearing the MAXEXTENTS, you can do the
following (assuming it is 3 extents or less away from MAXEXTENTS):
SELECT SUBSTR(owner||'.'||segment_name,1,50) OBJECT,
EXTENTS, MAX_EXTENTS
FROM DBA_SEGMENTS
WHERE MAX_EXTENTS - EXTENTS < 4
ORDER BY EXTENTS;
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 115+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page