ORA-01536: space quota exceeded for tablespace

[mooks]

Hi

I have encountered the error ORA-01536 and I checked that the tablespace was only 50% used.

I have earlier grant sysdba & unlimited tablespace to user A and I revoked the sysdba role from user A a couple of days later. After that, I encountered the error ORA-01536 when trying to create a table as User A.

My question is once the dba role has been assigned then revoke, will it affect the quota assigned? I have no clue what caused the error ORA-01536 until i tried to grant unlimited tablespace to user A and everything was ok after that!

Any advices please? Thanks!

[davey23uk]

seems obvious to me, you revoked the privlege from the user so he had no quota anymore, you needed to put it back to make it work

[mooks]

Revoke of just the dba assigned temporarily will removed all quota assigned? My understanding is that when you assigned a role, it should not affect the quota for the default tablespace. Wrong??

How do I check what's the quota for individual users? There are no rows in dba_ts_quotas...

[mooks]

One question : Is it because the role assigned is DBA? Other roles assigned and revoked will not caused this error?

[pando]

no rows in dba_ts_quota means he either have unlimited tablespace privilege or you have not specifically done

alter user XX quota etc etc

[jmodic]

UNLIMITED TABLESPACE is kind of "wierd" system privilege. It is not granted to any role (and in fact it CAN NOT BE GRANTED to any role!), yet when you grant some powerfull roles (like DBA and I think RESOURCE too - it might even be version dependand AFAIK) to user, Oracle "silently" adds that UNLIMITED TABLESPACE privilege to that user directly. And when you revoke such role from user, Oracle silently revokes UNLIMITED TABLESPACE privilege from user too!

Now, when someone gets UNLIMITED TABLESPACE granted, it doesn't meen taht quotas on all tablespaces are set to unlimited to him. His quotas on tablespaces remain exactly the same - if he had quota 0 on tablespace USERS before, he still has quota 0 on that tablespace now. But when he has UNLIMITED TABLESPACE privilege, this takes priority over any quota settings and therefore he will be able to use space in that tablespace for new extents. However, if you later revoke UNLIMITED TABLESPACE from that user, his quota 0 will be put in effect and he will no longer be able to use any additional available space from tablespace USERS.

[mooks]

1. The value -1 is unlimited tablespace. I will alter the user. Thanks!

2. Same Question : Will revoke of roles remove quotas assigned to user?

[mooks]

Originally posted by jmodic
UNLIMITED TABLESPACE is kind of "wierd" system privilege. It is not granted to any role (and in fact it CAN NOT BE GRANTED to any role!), yet when you grant some powerfull roles (like DBA and I think RESOURCE too - it might even be version dependand AFAIK) to user, Oracle "silently" adds that UNLIMITED TABLESPACE privilege to that user directly. And when you revoke such role from user, Oracle silently revokes UNLIMITED TABLESPACE privilege from user too!

Thank you jmodic!

So in conclusion only role DBA and RESOURCE will be granted unlimited tablespace to user!

I will be careful with these 2 roles. Thank you!