Re: Exceeded user quota on Index Tablespace

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);

But as I say, don't. Not the storage clause bit anyway.

Regards
HJR
> I created these PK not in line so I could use the Index space.
> Does the same ammount of Space get allocated if I use an Inline
> unnamed constraint?
>
> Thanks
> Rob
>
Received on Wed Dec 08 2004 - 14:09:36 CST