Re: interpreting tablespace settings

"T Stoneman" <tstoneman4_at_hotmail.com> wrote in message news:23ded962.0206091124.32f0b2e_at_posting.google.com...
> I was wondering if someone could help me.
>
> I am trying to create a simple table and it returned the error:
>
> ORA-01658: unable to create INITIAL extent for segment in tablespace
> TEST
>
> I went through google, and it turns out that I needed to increase the
> size of my tablespace. So that's fine, but I was wondering if someone
> could help me understand why my tablespace did not automatically
> resize....
>
> My tablespace has the following settings:
> BLOCKSIZE 8192
> INITIAL_EXTENT 65536
> NEXT_EXTENT <blank>
> MIN_EXTENTS 1
> MAX_EXTENTS 2147483645
> PCT_INCREASE <blank>
> MIN_EXTLEN 65536
> STATUS ONLINE
> CONTENTS PERMANENT
> LOGGING LOGGING
> EXTENT_MAN LOCAL
> ALLOCATION_TYPE SYSTEM
> PLUGGED_IN NO
> SEGMENT_SPC_MAN MANUAL
>
> I'm having some problems grasping what exactly the NEXT_EXTENT column
> means...If the next_extent is blank, does that mean that I am still
> using my initial extent? Or if there is a value within next extent,
> does this mean that my tablespace grew beyond the initial extent and
> is now using a new extent. Or does it mean that *if* a new extent
> will be created, it will use this setting, but it doesn't necessarily
> mean that I in fact am using a new extent.
>
> Does the fact that my next_extent and pct_increase are blank indicate
> that my tablespace will not auto-increase in size? I read through my
> books, but they all tell me how to create tablespaces, and not how to
> diagnose or analyze tablespaces that already exist...
>
> Thanks in advance,
>
> Terry Stoneman

No version of course, as always.
The physical representation of a table/index or whatever is called a segment. A segment is located in a tablespace, a tablespace typically has multiple segments. A segment consists of multiple extents, an initial extent and next extent.
As soon as a segment runs out of space in an extent it will create a new extent.
The extent sizes you are referring to are the extent sizes of the *default storage* clause of the tablespace.
These settings can be overridden in a create table or a create index statement.
A tablespace can not be autoextended, a data file can. However, generally speaking this is a bad idea, as you are relinquishing control of the space allocation to the O/S and once you have filled up your disk you are in real trouble.
In this particular situation you have a locally managed tablespace. Generally speaking: the next extent of a segment will always have the same size of the initial extent of that segment. The size of the associated datafile needs to be a multiple of the uniform extent size (in your case 64k) + 64k for the bitmap maintaining administrative data. If that formula doesn't hold true for your tablespace, you will waste storage.

Hth

--
Sybrand Bakker
Senior Oracle DBA

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