An example of ORA-01536 when quota exhaust on dependent objects

I am inspired to write this topic after getting a post inside the forum http://arju-on-it.com/blog/forum/server-administration/ora-01536-space-quota-exceeded-for-tablespace-mon_tbs.
In the example I have shown the reason why ORA-01536: space quota exceeded for tablespace error comes.

In this post I will give you the example when ORA-01536 occurs due to the dependent object on the table.
1)Create user test1 and grant unlimited quotes on default tablespace.
SQL> create user test1 identified by t default tablespace users;

User created.

SQL> grant create session, create table to test1;

Grant succeeded.

SQL> alter user test1 quota unlimited on users;

User altered.

2)Create user test2 and grant 1M quota.
SQL> create user test2 identified by t default tablespace users;

User created.

SQL> grant create session, create table, create trigger to test2;

Grant succeeded.

SQL> alter user test2 quota 1M on users;

User altered.

3)Create a table inside test1 schema.

SQL> conn test1/t
Connected.

SQL> create table tab1 as select level a1, level a2 from dual connect by level <10;

Table created.

SQL> conn test2/t
Connected.
SQL> create table tab1 as select level a1, level a2 from dual connect by level <1;

Table created.

SQL> conn test1/t
Connected.
SQL> grant select on tab1 to test2;

Grant succeeded.

5)Create a trigger inside test2 schema which inserts based on test1 insert.
SQL> conn / as sysdba
Connected.

SQL> create or replace trigger test2.t after insert on test1.tab1
2 begin
3 insert into test2.tab1 select * from test1.tab1;
4 end;
5 /

Trigger created.

6)Connect to test1 and insert more rows.
SQL> conn test1/t
Connected.

SQL> insert into tab1 select level a1, level a2 from dual connect by level <100;

99 rows created.

SQL> insert into tab1 select level a1, level a2 from dual connect by level <10000;

9999 rows created.

SQL> insert into tab1 select level a1, level a2 from dual connect by level <100000;
insert into tab1 select level a1, level a2 from dual connect by level <100000
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
ORA-06512: at "TEST2.T", line 2
ORA-04088: error during execution of trigger 'TEST2.T'

7)Log on as dba user and check for dependent objects. Grant quota of the owner of the dependent objects tablespaces.

SQL> conn / as sysdba
Connected.

SQL> select NAME,TYPE, OWNER from dba_dependencies where REFERENCED_NAME='TAB1' AND
2 REFERENCED_OWNER='TEST1';

NAME TYPE OWNER
------------------------------ ----------------- ------------------------------
T TRIGGER TEST2

See trigger definition and as trigger inserts into TAB1 table which resides in USERS tablespace so grant more quote to TEST2 user on tablespace USERS.

SQL> ALTER USER TEST2 QUOTA 10M on USERS;

User altered.

SQL> conn test1/t
Connected.

SQL> insert into tab1 select level a1, level a2 from dual connect by level <100000;

99999 rows created.

And it is ok now.

Related Documents

ORA-30041: Cannot grant quota on the tablespace

Changing a DBA user to a normal user in oracle

Create user in oracle

Minimum privilege needed to take data pump export

A user can do work in his schema with only Create Session Privilege.