Hi all,
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
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
All the best,
<http://www.mufalani.com.br/>Rodrigo Mufalani - Diretor Técnico |
rodrigo@xxxxxxxxxxxxxxx <mailto:rodrigo@xxxxxxxxxxxxxxx> | +55 21 988 994 817
Mufalani - +55 21 3193 0326 | Rua Alm Grenfall, 405, Bl 3, Sl 310, Centro
Empresarial
Washington Luiz, Duque de Caxias, RJ | CEP 25085-009 | www.mufalani.com.br
<mailto:rodrigo@xxxxxxxxxxxxxxx>
<http://www.mufalani.com.br/> <http://www.mufalani.com.br/>
<http://www.mufalani.com.br/>
