Behavior on 11gr2 - revoke dba grant remove quotas too

   I ran into a non-expected behavior today when I removed the DBA role from a specific user and on 11g there were a lot of ORA-01536 on application. So, I've granted the quotas on tablespaces and realized that the problem occurred with the revoke command (the user had quotas on those tablespaces before the grant dba).

   The issue only happened just on 11gR2 (I tested on 11.2.0.3, 11.2.0.4 and 11.2.0.1) versions.  

Not happened on 10g

SQL> select version from v$instance;

VERSION
—————–
10.2.0.5.0

SQL> create user teste_mufalani identified by teste_mufalani quota unlimited on users;

SQL> grant dba to teste_mufalani;

SQL> select * from dba_ts_quotas where username=’TESTE_MUFALANI’;

TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
—————————— —————————— ———- ———- ———- ———- —
USERS                          TESTE_MUFALANI                          0         -1          0         -1 NO

SQL> revoke dba from teste_mufalani;

SQL> select * from dba_ts_quotas where username=’TESTE_MUFALANI’;

TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
—————————— —————————— ———- ———- ———- ———- —
USERS                          TESTE_MUFALANI                          0         -1          0         -1 NO

On 11g

SQL> select version from v$instance;

VERSION
—————–
11.2.0.4.0

SQL> create user teste_mufalani identified by teste_mufalani quota unlimited on users;

SQL> grant dba to teste_mufalani;

SQL> select * from dba_ts_quotas where username=’TESTE_MUFALANI’;

TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
—————————— —————————— ———- ———- ———- ———- —
USERS                          TESTE_MUFALANI                          0         -1          0         -1 NO

SQL> revoke dba from teste_mufalani;

SQL> select * from dba_ts_quotas where username=’TESTE_MUFALANI’;

The tbs quotas were disappeared.

Not happened on 12c

SQL> select version from v$instance;

VERSION
—————–
12.1.0.2.0

SQL> create user teste_mufalani identified by teste_mufalani quota unlimited on users;

SQL> grant dba to teste_mufalani;

SQL> select * from dba_ts_quotas where username=’TESTE_MUFALANI’;

TABLESPACE_NAME                USERNAME                                                                                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
—————————— ————————————————————————————————— ———- ———- ———- ———-
USERS                          TESTE_MUFALANI                                                                                    0         -1          0         -1 NO