Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Script to find segment that have just ONE more extent
If you want to also check the free, you can something like:
select owner, substr(segment_name,1,30) segment_name, segment_type, tablespace_name
from dba_segments seg
where exists /* space for one extent */
(select 1 from dba_free_space sp where sp.bytes >= seg.next_extent and sp.tablespace_name = seg.tablespace_name) and not exists /* space for a second extent */ (select 1 from dba_free_space sp2, dba_free_space sp1 where sp1.bytes >= seg.next_extent and sp1.tablespace_name = seg.tablespace_name and sp2.bytes >= (1+seg.pct_increase/100)*seg.next_extent and sp2.tablespace_name = seg.tablespace_name and ( sp2.block_id <> sp1.block_id or sp1.bytes >= (2+seg.pct_increase/100)*seg.next_extent))order by 1,2
-- Regards Michel "Michel Cadot" <micadot_at_netcourrier.com> a écrit dans le message news: 93ci97$q63$1_at_s1.read.news.oleane.net...Received on Mon Jan 08 2001 - 09:57:07 CST
> I'm not sure to understand what you want.
> Is it not this:
> select segment_name from user_segments where extents=max_extents-1;
>
> --
> Have a nice day
> Michel
>
>
> "Luis Santos" <lsantos_at_pobox.com> a écrit dans le 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/
>
>