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

From: John Seitz <seitz_at_pobox.com>
Date: Sat, 10 Jul 1999 15:26:09 GMT
Message-ID: <lCJh3.831$ri.25160@newse3.tampabay.rr.com>

Here is a statement that will find any objects that will not be able to extend. Since I'm really a newbie when it comes to Oracle DBA, I would welcome anyone who has a comment to the script. My current database has 400 tables (includes all the SYS and DEMO users) and it took about 4 seconds. I know I could have does a subselect, but then I don't get to see the max freespace for a tablespace. Which I want to know.

John Seitz

SELECT
  owner,
  segment_name,
  seg.tablespace_name ,
  next_extent,
  max(fs.bytes) FreeSpace
FROM
  dba_segments seg,
  dba_free_space fs
WHERE
  seg.tablespace_name = fs.tablespace_name group by
  owner,
  segment_name,
  seg.tablespace_name ,
  next_extent
having
  next_extent > max(fs.bytes)
order by
  owner,
  segment_name

Andy Hardy <aph_at_ahardy.demon.co.uk> wrote in message news:9L9tQMAJ5Kh3EwqQ_at_ahardy.demon.co.uk...
> Hi,
>
> 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?
>
> Andy
> --
> Andy Hardy. PGP key available on request
> ===============================================================
Received on Sat Jul 10 1999 - 10:26:09 CDT