Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Script to find segment that have just ONE more extent

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

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Mon, 8 Jan 2001 16:57:07 +0100
Message-ID: <93co35$ths$1@s1.read.news.oleane.net>

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...

> 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/
>
>
Received on Mon Jan 08 2001 - 09:57:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US