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.

These are tablespace settings. What the above is telling you is that you have a locally manged tablespace next extent is therefore undefined. However you seem to have some confusion about what these storage parameters mean. they do not apply *to* the tablespace but are defined (as defaults) for objects created in that tablespace. Thus your tables get space allocated in extents. The error that you are getting indicates that the table cannot grap te next x kb of space that it needs. This most likely means that the tablespace is full.

>
> Does the fact that my next_extent and pct_increase are blank indicate
> that my tablespace will not auto-increase in size?

Tablespaces are logical collections of datafiles. Thus tablespaces do not autoincrease; datafiles do. My best guess would be that your datafiles are indeed defined as not auto-extensible. To correct this condition you need to do one of two things

1. add a new datafile to the tablespace. ALTER TABLESPACE ADD DATAFILE '<FILENAME>' size 1025M;
2. change one of the existing datafiles to autoextend. ALTER DATAFILE '<FILENAME>' AUTOEXTEND ON NEXT 10M;

The alternative is of course that you have filled your disk by autoextending datafiles. On the whole I'd recommend course 1.

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

take a read of the oracle concepts manual (http://tahiti.oracle.com) which explains Oracle's data storage mechanisms. Received on Sun Jun 09 2002 - 15:37:27 CDT