Quota is UNLIMITED, but encountering ORA-01536: space quota exceeded ...
AldrichJan 24 2012 — edited Jan 24 2012I need some help figuring out why we have this issue. I attempt an insert statement and get ORA-01536 on a table to which my user (BLF) has unlimited quota on that table's default tablespace.
The issue is resolved when I grant unlimited quota to the table owner (XXPCK) which previously had no quota of any kind granted to it. Take note, that this worked without error for months and now it does not. This table has a few hundred rows added each month (not a ton of data going in). This is on Oracle 11.2.0.1.
********************
Error encountered below:
DBNAME> show user
USER is "BLF"
DBNAME> insert into xxpck.xxpck_package_lines (package_line_id, line_num, package_id, line_status, created_by, creation_date,last_updated_by, last_update_date, entity_id) values (111,10,873,'NEW',-1,sysdate,-1,sysdate, 37);
insert into xxpck.xxpck_package_lines
*+
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'INTFD'
********************
DBNAME> show user
USER is "SYSTEM"
DBNAME> select TABLESPACE_NAME, BLOCKS,MAX_BLOCKS,BYTES,MAX_BYTES from dba_ts_quotas where USERNAME ='BLF';
TABLESPACE_NAME BLOCKS MAX_BLOCKS BYTES MAX_BYTES
------------------------------ ---------- ---------- ---------- ----------
INTFD 1536128 -1 1.2584E10 -1
DBNAME> @quotaupdate XXPCK UNLIMITED INTFD
old 1: alter user &1 quota &2 on &3
new 1: alter user XXPCK quota UNLIMITED on INTFD
User altered.
********************
Ignore the unique constraint error below, just take note, that we no longer encounter the ORA-01536 error. Why is this?
DBNAME> show user
USER is "BLF"
DBNAME> insert into xxpck.xxpck_package_lines (package_line_id, line_num, package_id, line_status, created_by, creation_date,last_updated_by, last_update_date, entity_id) values (111,10,873,'NEW',-1,sysdate,-1,sysdate, 37);
insert into xxpck.xxpck_package_lines
*+
ERROR at line 1:
ORA-00001: unique constraint (XXPCK.XXPCK_PACKAGE_LINES_U1) violated
********************
We now set quota to 0 for XXPCK to see if it makes a difference.
DBNAME> @quotaupdate xxpck 0 INTFD
old 1: alter user &1 quota &2 on &3
new 1: alter user xxpck quota 0 on INTFD
User altered.
********************
We no longer get the ORA-01536 error even after setting the table owner (XXPCK) quota to 0.
DBNAME> insert into xxpck.xxpck_package_lines (package_line_id, line_num, package_id, line_status, created_by, creation_date,last_updated_by, last_update_date, entity_id) values (111,10,873,'NEW',-1,sysdate,-1,sysdate, 37);
insert into xxpck.xxpck_package_lines
*+
ERROR at line 1:
ORA-00001: unique constraint (XXPCK.XXPCK_PACKAGE_LINES_U1) violated
Edited by: Aldrich on Jan 24, 2012 9:49 AM