DBA_TS_QUOTAS (ORA-00959,ORA-01536)

How much space user can consume in particular tablespace is showed in DBA_TS_QUOTAS dictionary view. Quota can be assigned to any user on any tablespace. More about quotas can be found in Oracle documentation...

I will concentrate on small demo case in this blog post.

Oracle 10.2.0.4
Linux x86


Create tablespace TEST_TBS and TESTUSR.

SQL> create tablespace test_tbs;
Tablespace created.

SQL> create user testusr identified by test;
User created.

SQL> grant create session to testusr;
Grant succeeded.

SQL> grant create table to testusr;
Grant succeeded.

By default, user has no quota on any tablespace.

-- system user
SQL> set lines 200
SQL> col tablespace_name for a30
SQL> col username for a20
SQL> col dropped for a15
SQL>
SQL> select * from dba_ts_quotas
 2  where username='TESTUSR';

no rows selected

-- testusr user
SQL> connect testusr/test
Connected.

SQL> create table testtab(a number) tablespace test_tbs;
create table testtab(a number) tablespace test_tbs
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TEST_TBS'

Now I will grant RESOURCE role to TESTUSR and check DBA_TS_QUTAS view.

SQL> grant resource to testusr;
Grant succeeded.

SQL> select * from dba_ts_quotas
 2  where username='TESTUSR';

no rows selected

Still no quota entries for TESTUSR (in dba_ts_quotas), but user is able to create table in TEST_TBS tablespace.

SQL> create table testtab1(a number) tablespace test_tbs;

Table created.

When I granted RESOURCE role to TESTUSR, “UNLIMITED TABLESPACE” privilege was granted automatically.
More about “UNLIMITED TABLESPACE” privilege:
http://msutic.blogspot.com/2008/07/oracle-roles-unlimited-tablespace.html


When I revoke RESOURCE role, “CREATE TABLE” statement fails.

SQL> revoke resource from testusr;
Revoke succeeded.

SQL> create table testtab2(a number) tablespace test_tbs;
create table testtab2(a number) tablespace test_tbs
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'TEST_TBS'

Add some quota for user.

SQL> alter user testusr quota 50M on test_tbs;
User altered.

SQL> select tablespace_name,username,max_bytes,dropped
  2  from dba_ts_quotas
  3  where username='TESTUSR';

TABLESPACE_NAME                USERNAME              MAX_BYTES DROPPED
------------------------------ -------------------- ---------- ---------------
TEST_TBS                       TESTUSR                52428800 NO


SQL> create table testtab2(a number) tablespace test_tbs;
Table created.

Now I managed to create table in TEST_TBS tablespace.

I will drop TEST_TBS tablespace and check DBA_TS_QUOTAS.

SQL> drop tablespace test_tbs including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name,username,max_bytes,dropped
  2  from dba_ts_quotas
  3  where username='TESTUSR';

TABLESPACE_NAME                USERNAME              MAX_BYTES DROPPED
------------------------------ -------------------- ---------- ---------------
TEST_TBS                       TESTUSR                52428800 YES



SQL> alter user testusr quota 0 on test_tbs;
alter user testusr quota 0 on test_tbs
*
ERROR at line 1:
ORA-00959: tablespace 'TEST_TBS' does not exist

User quota entry is still there with DROPPED = YES (tablespace has been dropped). Quota cannot be removed from DBA_TS_QUOTES if tablespace does not exist.


I will create new tablespace with the same name as dropped one - “TEST_TBS”.

-- execute as SYSTEM
SQL> create tablespace test_tbs;
Tablespace created.

SQL> select tablespace_name,username,max_bytes,dropped
  2  from dba_ts_quotas
  3  where username='TESTUSR';

TABLESPACE_NAME                USERNAME              MAX_BYTES DROPPED
------------------------------ -------------------- ---------- ---------------
TEST_TBS                       TESTUSR                52428800 NO


-- execute as TESTUSR
SQL> create table testtab3(a number) tablespace test_tbs;
Table created.

Now column DROPPED = NO for TESTUSR and TESTUSR is able to create table in new tablespace. To prevent this you should remove quota entries before you drop tablespace.


Remove user quota entry.

SQL> alter user testusr quota 0 on test_tbs;

User altered.

SQL> select * from dba_ts_quotas
 2  where username='TESTUSR';

no rows selected

Cleanup!

SQL> drop tablespace test_tbs including contents and datafiles;

Tablespace dropped.

SQL> drop user testusr cascade;

User dropped.