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: help sizing extents

Re: help sizing extents

From: Martin Haltmayer <Martin_Haltmayer_at_in.augsburg.net>
Date: Mon, 20 Sep 1999 18:29:25 +0200
Message-ID: <37E660E4.5E3DA647@in.augsburg.net>


Hi,

I am sorry but this is only true if you have only one freelist group.

Please try this script:

create table test_freelgr (
 n number
)
storage (freelist groups 2 initial 1M next 1m) /

select initial_extent, next_extent
from user_tables
where table_name = 'TEST_FREELGR'
/

insert into test_freelgr (n) values (5) /

commit
/

Then do an export and an import with indexfile=abc.tab. Then abc.tab contains the following in Oracle 8.1.5 on NT 4.0 SP 4:

REM CREATE TABLE "TEST"."TEST_FREELGR" ("N" NUMBER) PCTFREE 10 REM PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 1064960 REM NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 REM FREELISTS 1 FREELIST GROUPS 2 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ; REM ... 1 rows

So the initial extent is no longer 1M after that import. By the way, before reimporting that table select initial_extent, next_extent from user_table where table_name = 'TEST_FREELGR' both report correctly one megabyte (1048576).

We found this error in 7.1.6.2.5 Sun Solaris 2.4 Parallel Server and it stll exists. I do not have our Oracle folder at hand so I do not know the Oracle bug number.

Perhaps you or Thomas Kyte can fresh up that bugfix effort.

Martin

David Sisk wrote:

> Hi:
>
> I agree with Jerry's post. I'll add that, post 7.3, Oracle actually
> recommends picking a single extent size and sticking with it. Tablespace
> fragmentation = 0.
>
> There's an article on the site below under Performance -> Storage that you
> might want to read.
>
> Regards,
>
> --
> David C. Sisk
> Need tech info on Oracle? Visit The Unofficial ORACLE on NT site at
> http://www.ipass.net/~davesisk/oont.htm
> Like original music? Listen to song samples and buy a CD at
> http://www.mp3.com/disparityofcult
>
> Deborah Felmey wrote in message <7psfa5$oia$1_at_autumn.news.rcn.net>...
> >I'm an oracle newbie. We are moving our current databases to a new
> >platform - we are placing the current datafiles into one datafile when we
> >do the conversion - my question is - is there a formula for figuring out
> >what the initial extent and next extent will be? How large is too large?
> >
> >Also - we are running version 7.3.4 - what is the best way to run exports
> on
> >existing data - for import into the new database?
> >
> >Any help is greatly appreciated.
> >lost in pasadena - deb
> >
> >
Received on Mon Sep 20 1999 - 11:29:25 CDT

Original text of this message

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