- 21 Dec, 2023
- 0 Comments
- 2 Mins Read
ORA-01536: Space Quota Exceeded For Tablespace
Problem :
While creating a table or inserting data into a table, user may get this error : ORA-01536: space quota exceeded for tablespace.Solution :
Tablespace quota is the storage allocated for an user in a tablespace. Once the user reaches the max allocated space it will throw an error like this.1 2 3 4 5 | SQL> select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username='LEARNOMATE';TABLESPACE_NAME USERNAME BYTES/1024/1024 MAX_BYTES/1024/1024----------------------- -------- --------------- -------------------USER LEARNOMATE 12 20 |
1 2 3 4 5 | SQL> create table TEST tablespace USER as select * from dba_objects;create table TEST tablespace USER as select * from dba_objects*ERROR at line 1:ORA-01536: space quota exceeded for tablespace 'USER' |
1 2 3 4 5 6 7 8 9 | SQL> alter user LEARNOMATE quota 50M on USER;User altered.SQL> select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username='LEARNOMATE';TABLESPACE_NAME USERNAME BYTES/1024/1024 MAX_BYTES/1024/1024----------------------- -------- --------------- -------------------USER LEARNOMATE 12 50 |
1 2 3 4 5 6 7 8 9 10 | SQL> create table TEST tablespace USER as select * from dba_objects;Table created.SQL> select tablespace_name,username,bytes/1024/1024,max_bytes/1024/1024 from dba_ts_quotas where username='LEARNOMATE';TABLESPACE_NAME USERNAME BYTES/1024/1024 MAX_BYTES/1024/1024----------------------- -------- --------------- -------------------USER LEARNOMATE 24 50 |