Re: Tablespace quota when user not in DB_TS_QUOTAS?

On Jan 23, 5:02 am, DG problem <skatef..._at_gmail.com> wrote:
> on HP-UX 9.2.0.8
>
> If a user has not been granted UNLIMITED TABLESPACE and the user doe
> not appear in DB_TS_QUOTAS then what determines how much tablespace
> quota a user has?
>
> I recently had a user experience ORA-01536 and thus was not able to
> insert any records. The problem was fixed by granting UNLIMITED
> TABLESPACE, however, how can I check that this doesn't happen to
> another user?
>
> Also, only the user received the error message. Nothing appeared in
> the alert logs which was frustrating as the problem wasn't not
> apparent to me until the user called me. Is it possible to get this
> error output to the alert logs?

If a user doesn't have UNLIMITED TABLESPACE privilege and was not explicitly granted some quota for certain tablespace, then the quota is 0 bytes - the user will be unable to create any objects in that tablespace. ALTER USER xxx QUOTA {size|UNLIMITED} ON tablespace_name should've taken care of the issue, UNLIMITED TABLESPACE allows the grantee to consume space in *any* tablespace.

Afaik, you can't force ORA-01950 or ORA-01536 to go into the alert log, so you need to rely on end user's experience for their detection and resolution.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Wed Jan 23 2008 - 01:34:04 CST