Fun with Tablespace quotas and DBA

Yesterday on one of our oracle databases (11g) we were faced with something, that was quite new to me:

All of a sudden, one database user was not able to insert data into its own default tablespace any more. Instead, we received a “ORA-01536: space quota exceeded for tablespace” Message (which, by the way, does not occur in the alertlog).

Strange, because we do not work with quotas in our database at all. Every user has granted the ressource role and therefore unlimited quota on its default tablespace.

To understand, was oracle does here, I created a ltitle test case:

First, create a new tablespace and a user and create some data:

SQL> create tablespace tts datafile '+DATA' size 1g;
Tablespace created.

SQL> create user tuser identified by x default tablespace tts;
User created.

SQL> create table tuser.tab (a number);
Table created.

SQL> create table tuser.tab as select * from user_objects;
create table tuser.tab as select * from user_objects
                                        *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TTS'

Of course, per default, no one is allowed anything in oracle. So, give the user permission:

SQL> alter user tuser quota unlimited on tts;
User altered.

SQL> create table tuser.tab as select * from user_objects;
Table created.

We can see, the quota also appears in dba_ts_quotas:

SQL> select * from dba_ts_quotas where username='TUSER';

TABLESPACE_NAME                USERNAME                            BYTES MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---
TTS                            TUSER                               65536        -1          8         -1 NO

“-1” actually means unlimited.

And here comes the tricky part. In our live system we granted our user the dba role a couple of days ago, did a few tests with it and revoked the dba role again.

Let’s reproduce this:

SQL> grant dba to tuser;
Grant succeeded.

SQL> revoke dba from tuser;
Revoke succeeded.

SQL> select * from dba_ts_quotas where username='TUSER';
no rows selected

So, after revoking dba, the unlimited quota seems to be gone. Check:

SQL> create table tuser.tab2 as select * from user_objects;
create table tuser.tab2 as select * from user_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'TTS'

After giving it back, everything is fine again:

SQL> alter user tuser quota unlimited on tts;
User altered.

SQL> create table tuser.tab2 as select * from user_objects;
Table created.

So, by revoking dba, oracle implicitly also revokes any tablespace quotas for that user.

edit:
Same goes for role “resource”.
This is actually documented behaviour. See Metalink Note 465737.1 “ORA-01536 After Revoking DBA Role”:

Note: If you grant or revoke the RESOURCE or DBA role to or from a user, Oracle implicitly grants or revokes the UNLIMITED TABLESPACE system privilege to or from the user.

Good to know.

16 thoughts on “Fun with Tablespace quotas and DBA

  1. You answered my doubt which was there past three days … i was wondering why application jobs are failing with tablespace quota issues. I did grant and revoke DBA role for one activity.

    Thanks alot for your analysis.

  2. Hi,
    Actually I’m facing an issue. Problem is I created a tablespace and I also created a user and assigned the default tablespace which i was created before. Now i want to check the size of the default tablespace of the user which i was created . Can any one help me to solve this

  3. First check your default tablespace name by:

    SQL> select USERNAME,DEFAULT_TABLESPACE from dba_users where username=’LIKITH’;

    USERNAME DEFAULT_TABLESPACE
    ——————– ——————————
    LIKITH SUNMATRIX

    SQL> select sum(bytes/1024/1024) from dba_data_files where tablespace_name=’SUNMATRIX’;

    SUM(BYTES/1024/1024)
    ——————–
    50

  4. I had this issue recently. However, I had previously explicitly granted my users tablespace quotas to avoid this issue. But rather than simply revoking the implicit system privilege “UNLIMITED TABLESPACE” as per the oracle doco, oracle removed all the tablespace quotas, even when they were not “UNLIMITED”. Given this is removing more than the single privilege specified, I’m logging this as a bug with oracle.

  5. Update: This issue is actually caused by the removal of the UNLIMITED TABLESPACE privilege. This has been logged as a bug with Oracle for 11g where it currently has a status of “Not A Bug”.

    BUG 9975371 – ON REVOKING UNLIMITED TABLESPACE SYS PRIV QUOTA ON THE TABLESPACE IS GONE >>> for 11g

    I have raised a more pointed case for a Bug for 12c:
    BUG 21972758 – REVOKING DBA PRIVILEGE FROM USERS ALSO REMOVES ALL EXPLICIT TS QUOTAS IN 12C

    For the time being though, you will need to capture all explicitly granted TS quotas before revoking DBA/RESOURCE/UNLIMITED TABLESPACE system privilege and then reapply them afterwards.

  6. Thanks!!!!

    SQL> grant dba to tuser;
    Grant succeeded.

    SQL> revoke dba from tuser;
    Revoke succeeded.

  7. Amazing page! You are cooregauus for putting that hurtful word out there like that. People say and do some horribly mean things sometimes. We all have issues like this inside of us.

  8. I and my friends came examining the nice strategies found on your website then before long developed a terrible feeling I never expressed respect to the website owner for those secrets. All of the ladies were definitely consequently excited to study them and now have truly been making the most of those things. Thank you for genuinely simply thoughtful and then for making a choice on these kinds of remarkable useful guides millions of individuals are really desirous to understand about. Our sincere regret for not saying thanks to earlier.

  9. I am entranced by this picture. Keep coming back to look at it. I am not a pink person by any means. But imagine if you took everything off the top of the table but the crown and made the flowers rusty-orangey color to go with the tabletop. Add a contemporary lamp in metal and frosted glass and substitute a Mapplethorpe photo. Voila! A girl with gusto lives here now.

  10. Thanks a lot for giving everyone a very nice possiblity to check tips from here. It is usually so ideal plus stuffed with a lot of fun for me and my office colleagues to visit your web site no less than thrice per week to read through the latest guidance you will have. And of course, I’m also at all times contented for the stunning tricks you give. Selected 3 ideas in this posting are clearly the very best I have ever had.

  11. Oracle 11g windows,I hv created tablespace thru sqlplus,but unable to see tablespace in enterprise manager,its not reflecting,whatever user b

  12. Is there a way to revoke dba without revoking quota unlimited aswell?
    I.e create a role?

  13. Just desire to say your article is as surprising.
    The clearness in your put up is simply cool and i can assume you’re knowledgeable in this subject.
    Well with your permission let me to snatch your feed
    to keep updated with approaching post. Thank you a million and please carry on the enjoyable work.

Leave a Reply

Your email address will not be published. Required fields are marked *