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

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 10 Jul 1999 15:55:53 GMT
Message-ID: <37876be5.335928@news.demon.nl>

As such the select is correct. It should be faster to use inline views, at least it avoids group on columns you don't really need to group on

select owner

     , segment_name
     , seg.tablespace_name
     , next_extent
     , fs.maxbytes

from dba_segments seg
, (select max(bytes) maxbytes

from dba_free_space fs
group by tablespace_name) fs
where seg.tablespace_name = fs.tablespace_name and seg.next_extent> fs.maxbytes

Hopefully this is not only for demonstrating a new feature, but it will run also faster.

Best regards,

Sybrand Bakker, Oracle DBA

On Sat, 10 Jul 1999 15:26:09 GMT, "John Seitz" <seitz_at_pobox.com> wrote:

>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:55:53 CDT