Re: Script to find segment that have just ONE more extent

From: Syltrem <syltrem_at_videotron.ca>
Date: Mon, 8 Jan 2001 10:25:30 -0500
Message-ID: <YQk66.4486$Jh3.132392@wagner.videotron.net>

I use this. Is that what your seek?

/*
tells if table's next extent is exceeding the free space available for next extension
*/
select substr(tab.tablespace_name,1,15) "Tablespace",

        substr(tab.owner || '.' || tab.table_name,1,45) "Table",
        empty_blocks*p.value/1024 "Unused Space (K)",
        (tab.blocks + empty_blocks)*p.value/1024 "Total Space (K)",
        tab.next_extent/1024 "Next Extent (K)",
        max(free.bytes)/1024 "Available (K)"

from dba_tables tab, dba_free_space free, v$parameter p where tab.tablespace_name = free.tablespace_name   and p.value = 'db_block_size'
group by tab.tablespace_name, owner, table_name,

        next_extent, empty_blocks, tab.blocks, p.value having tab.next_extent > max(free.bytes)

   and (empty_blocks * p.value) < tab.next_extent;

select substr(idx.tablespace_name,1,15) "Tablespace",

        substr(idx.owner || '.' || idx.index_name,1,45) "Index",
        idx.next_extent/1024 "Next Extent (K)",
        max(free.bytes)/1024 "Available (K)"

from dba_indexes idx, dba_free_space free where idx.tablespace_name = free.tablespace_name group by idx.tablespace_name, owner, index_name,

        next_extent
having idx.next_extent > max(free.bytes);

--
Syltrem
http://pages.infinit.net/syltrem (OpenVMS Web Site)




"Luis Santos" <lsantos_at_pobox.com> wrote in message
news:93cc8o$db5$1_at_nnrp1.deja.com...


>    Hello!

>

>    There are several scripts on the Web (and itīs easy to write one!)

> that show the segments that canīt be extended anymore, due to

> maxextents reached or no more contiguous space on the segmentīs

> tablespace.

>

>    But itīs not easy to find or write a script that shows all the

> segments that can allocate more one, and only one, extent.

>

>    Does someone has such a script?

>

>    Best regards

>

> --

> Luis Santos

> Oracle DBA

>

>

> Sent via Deja.com

> http://www.deja.com/

Received on Mon Jan 08 2001 - 09:25:30 CST