Re: Very Large Next Extent

From: Frank van Bortel <f.van.bortel_at_vnl.nl>
Date: Fri, 01 Oct 1999 11:09:47 -0700
Message-ID: <37F4F8EB.64B765E3@vnl.nl>

Pete Sharman wrote:

> There is no OPTIMAL clause for indexes, only for rollback segments, so this
> suggestion won't work.
>
> Pete
>
> tjmxyz_at_my-deja.com wrote:
>
> > A few suggestions...
> > Us ethe storage clause in the create table statement.
> > I would make a temp table with the same struct and use a different
> >
> > SOMETHING LIKE
> > CREATE TABLE TEMP AS SELECT *
> > from mytable STORAGE (INITIAL XX NEXT XX PCTFREE 1 MINEXTENT XX
> > MAXEXTENT XX);
> >
> > Thus you could in theopry create a table with all the rows locate in
> > consecutive blocks (maybe 1 huge extent.)
> >
> > As for the index I would recreat it using the OPTIMAL Clause..
> >
> > Sorry as to not be more helpful but I assume
> > you could figure it out from here...
> >
> > Hint the extent size should be both a multiple of the block size and
> > perhaps large enough to place the table in 1 or 2 extents...
> >
> > This is completely in my opinion.
> >
> > (You'll have to look up the storage clause I can't remember)
> >
> > In article <37F29320.F337F23D_at_abbnm.com>,
> > Tapan Trivedi <tapan.trivedi_at_abbnm.com> wrote:
> > > This is a multi-part message in MIME format.
> > > --------------28EC1BFD4731F69854710893
> > > Content-Type: text/plain; charset=us-ascii
> > > Content-Transfer-Encoding: 7bit
> > >
> > > Hey Guys,
> > > I ran the following query on one of my instances and I got the
> > following
> > > results. I have a table with a next extent of 52428800 which is
> > really
> > > very big. What do I do about this as export/import is not an option
> > for
> > > this table (9 million rows ) and always being written to and other
> > > application constraints. Any hints , suggestions, experiences,
> > comments
> > > welcome.
> > >
> > > Thank
> > you.
> > >
> > Tapan H Trivedi
> > >
> > > select
> > >
> > SEGMENT_NAME,SEGMENT_TYPE,BYTES,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTEN
> > T,MAX_EXTENTS,PCT_INCREASE
> > > from dba_segments
> > > where extents > 20
> > > order by extents desc;
> > >
> > > SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS EXTENTS
> > > INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE
> > >
> > ------------------------------------------------------------------------
> > ---------------------------------------------------
> > > SYS_C0026295 INDEX 172134400 84050
> > > 82 2097152 2097152 121 0
> > > ESDATA TABLE 195174400 95300
> > > 69 2097152 52428800 121 0
> > > EVTMSG 100771840 49205
> > > 49 10240 2099200 121 0
> > > EVTAUG 73482240 35880
> > > 36 10240 2097152 121 0
> > > EVTJUL 56688640 27680
> > > 28 10240 2097152 121 0
> > > --------------28EC1BFD4731F69854710893
> > > Content-Type: text/x-vcard; charset=us-ascii;
> > > name="tapan.trivedi.vcf"
> > > Content-Transfer-Encoding: 7bit
> > > Content-Description: Card for Tapan Trivedi
> > > Content-Disposition: attachment;
> > > filename="tapan.trivedi.vcf"
> > >
> > > begin:vcard
> > > n:Trivedi;Tapan
> > > tel;pager:281 262 5866
> > > tel;home:713 218 8133
> > > tel;work:281 274 5193
> > > x-mozilla-html:FALSE
> > > adr:;;;;;;
> > > version:2.1
> > > email;internet:tapan.trivedi_at_abbnm.com
> > > fn:Tapan Trivedi
> > > end:vcard
> > >
> > > --------------28EC1BFD4731F69854710893--
> > >
> > >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Well, what's the problem? You already used 69 extents, so it looks like you need the space.
Assuming pct_increase hasn't been touched (and reset to 0 in the meantime), that is.
You could always
alter table esdata storage(next 25M);
if that would put your mind at ease. Take care you don't run into maxextents. Increase to unlimited,
or another value, based on tablespace settings.

--
Met vriendelijke groet/kind regards,

Frank van Bortel
Technical consultant Oracle

Work:                                Home:
----------------------------------   ----------------------------
V&L Informatica BV                   Hunzestraat 4
Palatijn 3, 7521 PN Enschede         7555 WB Hengelo
PoBox 545, 7500 AM Enschede          (31)074-2425046

053-4341500 Received on Fri Oct 01 1999 - 13:09:47 CDT