Re: interpreting tablespace settings

In article <23ded962.0206091124.32f0b2e_at_posting.google.com>,  tstoneman4_at_hotmail.com (T Stoneman) wrote:

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

Your tablespace will not resize itself automatically if you haven't specified AUTOEXTEND ON in your tablespace's storage clause (Oracle 8.1+).

The ORA-01658 error indicates possible severe fragmentation within your tablespace, as Oracle needs to find CONTIGUOUS blocks in order to satisfy your CREATE statement. Failing to find 64K of contiguous space in your tablespace, Oracle throws this error.

> 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
Is this a 9i instance? I don't recognize the SEGMENT_SPC_MAN column. It also seems strange to me that you would have blank (are they NULL?) values.

My experience with Oracle has been that the defaults used for extent management when not specified during the CREATE stage is NEXT_EXTENT = INITIAL_EXTENT and PCT_INCREASE = 50. I can't say this is still true for 9i.

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

NEXT_EXTENT is the value, used in conjunction with PCT_INCREASE that tells Oracle how to allocate space for objects when the INITIAL extent has grown beyond its ability to store information. At object creation time, the object is created with the INITIAL_EXTENT value whenever possible. Then, when the object needs to extend, Oracle will use the value of NEXT_EXTENT for the second extent. Here's where it gets interesting... If the value of PCT_INCREASE is greater than 1, Oracle will then base future extent growth on the value of the LAST EXTENT created TIMES the value of PCT_INCREASE. While a hotly debated issue, most DBAs prefer to specify either zero or one for this parameter, thereby establishing control over the possible boundless growth extents can experience. There are other reasons, but for this discussion, it will suffice.

Back to your error: Oracle could not allocate space for the INITIAL extent. For this reason, the NEXT_EXTENT and PCT_INCREASE do not come into play.

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

Extent growth is controlled by the MIN_EXTENTS and MAX_EXTENTS parameters. The indication is that you've specified UNLIMITED for MAX_EXTENTS, but this does NOT mean that your tablespace will grow beyond its current size. That capability is controlled by the AUTOEXTEND ON clause in the TABLESPACE storage clause. You can turn this on using the ALTER TABLESPACE command. Personally, I don't like this capability, but that's my own problem. :-)

-- 
The underscore character does not belong in my address. You know the drill...
***
Anyone sufficiently smart enough to configure and use USEnet for research should
be smart enough to Read The Freakin' Documentation!