Re: Exceeded user quota on Index Tablespace

"Rob Williamson" <robw_at_physics.umd.edu> wrote in message news:cp756q$jrp$1_at_grapevine.wam.umd.edu...
> 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 ).
> Is there a storage clause that can be used with the CONSTRAINT clause
> of the Create TAble or in the Create Table?
> 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?

You don't say if there is any data in the tables, but I suspect not. If you read the Concepts guide, you'll see that Oracle allocates space not in blocks but in groups of blocks called extents, when you specify the storage clause for the tablespace the size of these extents is one of the parameters you can set. When you create the object one of the parameters you can set is the minimum number of extents that it has. My best guess is that you have an extent size of either 256k or 128k (and minextents 2 in each object).

You can specify the storage of an index supporting a constraint in the table creation statement - the syntax should be online at http://tahiti.oracle.com The allocation in extents isn't going to change though however you specify the segments creation. I wouldn't be concerned myself - those extents are available for the indexto use, they aren't full of data yet (well unless you have a bunch of data in the underlying tables).

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com