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

From: <rcordingley_at_my-deja.com>
Date: Thu, 08 Jul 1999 20:38:19 GMT
Message-ID: <7m327p$mje$1@nnrp1.deja.com>

run the following sql script from cron with a line like:

12 * * * * /bin/ksh -c '. /export/home/oracle/.cron-env; sqlplus -s / @/export/home/oracle/sql/cant_extend.sql'

put a .forward in your ~ or pipe the output of sqlplus to mailx and send it to an account that you check frequently.

################## cant_extend.sql ##################
set pagesize 6000 linesize 132 feedback off

col owner form a13
col segment_name form a30
col segment_type form a8
col tablespace_name form a6
col bytes form 999,999,999
col next_extent form 999,999,999
col avail form 999,999,999,999
select

    a.owner,
    a.segment_name,
    a.segment_type,
    a.tablespace_name,
    a.bytes,
    a.next_extent,
    b.bytes as avail

from

    dba_segments a,
    ( select tablespace_name,sum(bytes) as bytes

      from dba_free_space
      group by tablespace_name ) b

where

    a.tablespace_name = b.tablespace_name and     a.next_extent >= b.bytes
order by

    next_extent
/

exit
################## cant_extend.sql ##################

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jul 08 1999 - 15:38:19 CDT