Re: Anyone got a simple script to detect full tables?

In article <9L9tQMAJ5Kh3EwqQ_at_ahardy.demon.co.uk>, Andy Hardy <aph_at_ahardy.demon.co.uk> writes:
>Have you got a simple script that can be run against a database that
>highlights those tables that are either currently full (and probably
>causing insert failures) or getting close to full?

I used to have a 3rd-party tool (Patrol) warn me when an object could not allocate the next extent or if an object was approaching max extents. Alas, no more, so I have these simple scripts running periodically via the NT scheduler to warn me of possible doom.

rem Objects with extents of 50 or higher - modify to whatever rem you want to watch for.
select segment_name, segment_type, sum(extents) from dba_segments
where extents > 49
group by segment_name, segment_type;

rem Query against each tablespace... Example below will show if any rem objects in the SYSTEM tablespace cannot allocate their rem next extent. Modify for your tablespace names. select tablespace_name, segment_name, segment_type, next_extent from dba_segments
where tablespace_name = 'SYSTEM'
and next_extent >
(select max(bytes) from dba_free_space where tablespace_name = 'SYSTEM');

Simple, but these work. I output it to a text file and run it hourly via AT so I can quick-check throughout the day. So far, I've caught one index that would not have been able to extend with this method.

Rose Received on Thu Jul 08 1999 - 13:47:20 CDT