Re: Initial segment badly initialized

"Frederic Payant" <fpayant_at_club-internet.fr> wrote in message news:vp6teucorhvtuak04ca3uv1v61ddflk4jb_at_4ax.com...
> Hi
> My database was designed for all tables using default storage clause
> of tablespace.
> But after all the DB installed I've discovered that the values of
> INITIAL and NEXT was false for some of my tablespaces.
> In general, my segments are created at the tenth of what they should
> be ( ie 20M instead of 200M).
> My questions are :
>
> 1) Is that really a problem, I mean : will my Database be slower
> because of fragmentation (I computed the default size so that I
> planned to have between 50 and 500 segments for a table or a
> partition; with that mistake I'll have between 500 and 5000 segment
> per table/partition) ?

for selects: zero impact. On most O/Ses Oracle will read at max 64k or 1M in one I/O anyway.
If these objects are in a dictionary managed tablespace and they are frequently extended, there might be an issue though, however during insert and update only

>
> 2) I know that the second point is not evident, but is it possible to
> estimate the loss in performances due to fragmentation ? 0, 1%, 10%
> 50% ?
>

Other than by sticking a thumb up in the air, or look in a crystall ball: no.

> 3) How can I correct the error without droping and recreating tables ?

Not

>
> I first thought to altering storage values in tablespace. It surely
> won't make anythin to segments allready created, but will the new ones
> be created with new values (I mean next extents) or will they use the
> default values used when I created the table ?
> I've a lot of partitions which are drop and recreate regularly (every
> day I drop an old partition per table and I recreate another). Will my
> partitions be created with tablespace new values or old values ?
>

The default storage clause of the tablespace will be used at create time, in absence of a storage clause in the create <object> statement. Daily partition drops sound quite awful to me.

>
> I thank all of you for your advices ( or pointers to doc issues or Web
> pages ). Don't hesitate to ask me any further information
>
> My server is SUN Solaris 8
> Oracle 8.1.7
> Databases (they are 2) are 350 GB each
>
>
> Regards
> Frederic PAYANT - junior DBA ;-)

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address