Re: Wierd tablespace quota behavior

Medic Alert wrote:
>
> I've got 7.2.3 running on an HP-UX 10.01 box.
> During the creation of all of the tablespaces,
> tables, users, indexes, etc. At no time were
> quotas specified, yet there are quotas in the
> sys.dba_ts_quotas table for the primary user
> on nearly every tablespace and they are all
> UNLIMITED quotas.
>
> So the questions are:
>
> 1) How did these quotas get established? Was it
> a side-effect of granting the user a priviledge?

     The quota assigned is according to the DEFAULT user profile when you do not specify the quota on a specific tablespace. Granting UNLIMITED TABLESPACE system privilege to an user can make the space utilization out of control.

> 2) There are two tablespaces with no quotas indicated,
> yet the primary user was able to create indexes in
> these tablespaces, however, when the indexes went
> to extend Oracle generated a 1536 "quota exceeded"
> error. How can a user create an index without a
> quota, yet the index they created cannot extend
> without a quota!? Is there special quota behavior
> for the initial vs. subsequent extents?

     You can grant a larger quota on the index tablespace to the user or grant UNLIMITED  TABLESPACE system privilege to the user on the index tablespace.

>
> 3) In all my other 7.2.x databases on various platforms,
> I've never specified quotas and no quotas were created
> automatically (sys.dba_ts_quotas is empty), yet there
> is no problem creating tables, indexes, or extending
> them. Is this a side effect of particular roles?

     The side -effect is mentioned in point (1). However, please remember that the default user tablespace is SYSTEM. It is also true for temporary tablespace. As a result, it will increase the fragmentation on SYSTEM tablespace. Moreover, it will impact the performance.

---
Name   : Lun Wing San (Certified Oracle Database Administrator)

Title  : Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841

This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.