Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Quota is UNLIMITED, but encountering ORA-01536: space quota exceeded ...

AldrichJan 24 2012 — edited Jan 24 2012
I 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
This post has been answered by Centinul on Jan 24 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 21 2012
Added on Jan 24 2012
1 comment
4,216 views