Oracle Database Discussions

DB version:11.2.0.2

Developers have been creating/dropping objects in LANTS_ENTMT_NA_DEV_01 schema for several weeks now. Today, while trying to create a table in this schema they got

ORA-01536: space quota exceeded for tablespace 'LANTS_DATA'

So, I wanted to check the quota allocated to this user. But this user doesn't have an entry in dba_ts_quotas ! This means that this user was never granted any quota on any tablespace . Right ?


But, I am curious , without any QUOTA , how this user was able to create objects in its default tablespace until now and suddenly get "ORA-01536: space quota exceeded" error today.

SQL > select distinct tablespace_name from dba_segments where owner = 'LANTS_ENTMT_NA_DEV_01';

TABLESPACE_NAME
------------------------------
LANTS_DATA

SQL > select MAX_BYTES from dba_ts_quotas where username = 'LANTS_ENTMT_NA_DEV_01';

no rows selected

SQL > select username, default_tablespace from dba_users where username = 'LANTS_ENTMT_NA_DEV_01';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
LANTS_ENTMT_NA_DEV_01          LANTS_DATA

SQL > select count(*) from dba_ts_quotas where username = 'LANTS_ENTMT_NA_DEV_01';

  COUNT(*)
----------
         0

SQL > alter user LANTS_ENTMT_NA_DEV_01 quota 20g on LANTS_DATA;

User altered.

-- After setting the quota, the user has an entry in dba_ts_quotas


SQL > select MAX_BYTES from dba_ts_quotas where username = 'LANTS_ENTMT_NA_DEV_01';

 MAX_BYTES
----------
2.1475E+10

SQL > select TABLESPACE_NAME, MAX_BYTES/1024/1024/1024 from dba_ts_quotas where username = 'LANTS_ENTMT_NA_DEV_01';

TABLESPACE_NAME                          MAX_BYTES/1024/1024/1024
---------------------------------------- ------------------------
LANTS_DATA                                                     20

If LANTS_ENTMT_NA_DEV_01 didn't have any quota at all on this tablespace , this user should have got a different error ie. "ORA-01950: no privileges on tablespace" as shown in the test below.

SQL> create user testuser02 identified by abcd123 default tablespace FRCRS_TS;

User created.

SQL> grant create session to testuser02;

Grant succeeded.

SQL> grant select on dba_objects to testuser02;

Grant succeeded.

SQL> grant create table, create session to testuser02;

Grant succeeded.

SQL> select count(*) from dba_ts_quotas where username= 'TESTUSER02';

  COUNT(*)
----------
         0

SQL> conn testuser02/abcd123
Connected.

SQL> create table john as select * from dba_objects where rownum < 30;
create table john as select * from dba_objects where rownum < 30
                                   *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'FRCRS_TS'