Re: Exceeded user quota on Index Tablespace

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 09 Dec 2004 07:17:09 +1100
Message-ID: <41b76143$0$20858$afc38c87@news.optusnet.com.au>

Howard J. Rogers wrote:
> Rob Williamson wrote:
>

>> On a small test Database I started creating my PK and Unique keys using
>> the Index Tablespace instead of the Default tablespace.  My quota for 
>> the user was set at 1M for Index_TS.  The Index_TS tablespace was 
>> created at 10M.  After only 3 PK and 12 Unique key the 5th PK got the
>> Exceeded quota error.  I altered the user to change the quota to 10M.
>>
>> My question is why did Oracle alocate so much space for each of these 
>> Keys.  250K / PK seems like alot.  I assume there is a way to use less 
>> space.  It looks like each key was allocated 32 blocks ( 8k each ).

>
>
> Things don't just get allocated like that. They are allocated for a
> reason. In this case, indexes are allocated extents just like any other
> segment: because a tablespace has a default storage clause; because a
> tablespace is LMT and has either a UNIFORM SIZE clause or an
> AUTOALLOCATE clause; or because the index demanded an allocation with
> its own STORAGE clause.
>
> You don't mention an Oracle version; and you don't provide any actual
> figures or out put from things like DBA_TABLESPACES, DBA_INDEXES,
> DBA_EXTENTS or DBA_SEGMENTS, so it's a bit difficult to get more specific.
>

>> Is there a storage clause that can be used with the CONSTRAINT clause
>> of the Create TAble or in the Create Table?

>
>
> Yes there is, but don't use it. The days of using storage clauses should
> be long behind us. You should create your tablespaces with default
> storage clauses and get the segments to use them; or you should create
> your tablespaces using appropriate locally-managed extent size clauses
> (eg, UNIFORM SIZE 64K); and in either case a storage-clauseless segment
> will then pick up whatever storage settings the tablespace has been
> configured with.
>
> If you use segment-specific storage clauses you either a) risk
> tablespace fragmentation or b) risk it being ignored anyway by a locally
> managed tablespace.
>
> All that said, if you are using 9i (and an Oracle version REALLY helps
> answer these sorts of questions), you can do this:
>
> create table X (
> col1 char(5) constraint XCOL1PK primary key deferrable using index
> (create index blah on X(col1) tablespace ZZZZ storage (initial 16K next
> 16K),
> col2 number,
> col3 date);

And there's a closing right bracket missing from that example, I now realise (I never can count the damn things properly!!)

Sorry.

create table X (
col1 char(5) constraint XCOL1PK primary key deferrable using index (create index blah on X(col1) tablespace ZZZZ storage (initial 16K next 16K)),
col2 number,
col3 date);

Regards
HJR Received on Wed Dec 08 2004 - 14:17:09 CST