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.

11 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. Oracle 11g windows,I hv created tablespace thru sqlplus,but unable to see tablespace in enterprise manager,its not reflecting,whatever user b

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

  9. is there any possibility to check the privious quota size of user on table space

Leave a Reply

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