Dołącz do nas!

Wszystkie znaki interpunkcyjne za wyjątkiem kropek, łączników, znaków podkreśleń i spacji są niedozwolone.
Adres e-mail, koniecznie prawidłowy. Będą nań przesyłane wszystkie wiadomości e-mail od systemu. Adres nie jest udostępniany publicznie i będzie wykorzystywany jedynie w wypadku prośby o przesłanie nowego hasła lub do przesyłania informacji o nowościach czy innych komunikatów.
CAPTCHA
To pytanie ma na celu ochronę przed automatycznym spamowaniem oraz ustalenie, czy odwiedzający stronę jest człowiekiem.
Image CAPTCHA
Wpisz kod widoczny powyżej.

Logowanie

Nazwa użytkownika witryny Project Envision.
Hasło powiązane z nazwą użytkownika.
Prześlij nowe hasło

ORA-01536: space quota exceeded for tablespace

ORA-01536: space quota exceeded for tablespace is a common error in Oracle Database.
It means that users is trying to use more space than the Database Administrator assigned to him. Quota is a maximum number of bytes that a user can allocate in particular tablespace. Implementing quotas in database is a reasonable approach because it prevents buggy or malicious code to fill the tablespace.

It is worth to mentioned that prior to Oracle 11g quotas in Oracle could be assigned to both permanent and temporary tablespaces. Starting from Oracle 11g quotas can only be set on permanent tablespaces.
Database Administrators should remember that after they setup quotas for users they are responsible for constantly monitoring and extending quotes if required.

Below example presents how we can generate ORA-01535 in Oracle database, and how we can resolve it.

We start from creating a user with default tablespace set to USERS. Then we grant create session and create table privileges, to enable the user to connect to database and create a table. We also grant initially 1 MB quota on tablspace users.

 
SQL> conn / as sysdba 
Connected.
SQL> set linesize 160
SQL> create user user1 default tablespace users quota 1M on users temporary tablespace temp identified by pass1
2 /

User created.

SQL> grant create session, create table to user1
2 /

Grant succeeded.

Then we try to create a table using CTAS (create table as select) based on ALL_OBJECTS view. ALL_OBJECTS view
contains a list of ALL_OBJECTS in database.

SQL> conn user1/pass1
Connected.
SQL> set linesize 160
SQL> create table t1 as select * from all_objects
2 /
create table t1 as select * from all_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

The error occurred while we tried to create the table, due to the fact that table t1 would require around 7MB of space and user1 quote was set to just 1MB.

SQL> conn / as sysdba
Connected.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"
2 from dba_ts_quotas
3 where tablespace_name = 'USERS' and username = 'USER1'
4 /

TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 1

To resolve the problem we enlarge user1 quota to 10MB.

SQL> alter user user1 quota 10m on users
2 /

User altered.

SQL> conn user1/pass1
Connected.
SQL> create table t1 as select * from all_objects
2 /

Table created.

As we can see the table was created successfully. So the last thing to do is to check the quota usage.

SQL> conn / as sysdba
Connected.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"
2 from dba_ts_quotas
3 where tablespace_name = 'USERS' and username = 'USER1'
4 /

TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 7 10

It is worth to remember that instead of granting quota in megabytes, we can grant UNLIMITED QUOTA on tablespace for
a user. In my opinion it sometimes be a security whole, but on other ocasions it can be useful.

So let's start from dropping the table t1.

SQL> conn user1/pass1
Connected.
SQL> drop table t1
2 /

Table dropped.

SQL> conn / as sysdba
Connected.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"
2 from dba_ts_quotas
3 where tablespace_name = 'USERS' and username = 'USER1'
4 /

TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 7 10

SQL> show parameter recyclebin

NAME TYPE VALUE
------------------------------------ ----------- --------
recyclebin string on

After table drop you see that space used by the user has not been change.
You probably wondering why, the reson is simple, I used the statement DROP TABLE tableName; instead of DROP TABLE tableName PURGE; and I have RECYCLE_BIN ON. Below query allows be to confirm that my table is in RECYCLEBIN.

SQL> set linesize 160
SQL> column owner format a15
SQL> column original_name format a20
SQL> column can_purge format a10
SQL> column type format a10
SQL> select owner, object_name, original_name, operation, type, can_purge
2 from dba_recyclebin
3 where owner = 'USER1' and type = 'TABLE'
4 /

OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE CAN_PURGE
USER1 BIN$2JQWhCy4RIiMi4/epuYlSQ==$0 T1 DROP TABLE YES

To purge the table from recyclebin we will login as user1 and execute PURGE TABLE statement.

SQL> conn user1/pass1
Connected.

SQL>select object_name, original_name, operation, type, can_purge
1 from user_recyclebin 2 where type = 'TABLE'
3 /

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE CAN_PURGE
BIN$2JQWhCy4RIiMi4/epuYlSQ==$0 T1 DROP TABLE YES
SQL> purge table "BIN$2JQWhCy4RIiMi4/epuYlSQ==$0"
2 /

Table purged.

Confirmation of successful purge operation.

SQL> conn / as sysdba
Connected.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"
2 from dba_ts_quotas
3 where tablespace_name = 'USERS' and username = 'USER1'
4 /

TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 10

Please note that above behavior with RECYCLEBIN will only work is instance parameter recyclebin is set to on, and if you are working on Oracle 10g or later. The above experiment was performed on Oracle 11g.

We can now replay our initial test and check that UNLIMITED QUOTA WORKS.

SQL> conn / as sysdba
Connected.
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"
2 from dba_ts_quotas
3 where tablespace_name = 'USERS' and username = 'USER1'
4 /

TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 10
SQL> alter user user1 quota 1M on users
2 /

User altered.

SQL> conn user1/pass1
Connected.
SQL> create table t1 as select * from all_objects
2 /
create table t1 as select * from all_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

SQL> conn / as sysdba
Connected.
SQL> alter user user1 quota unlimited on users
2 /

User altered.

Unlimited Quota is denoted by -1 in MAX_BYTES column of DBA_TS_QUOTAS view.

 
SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB",
  2    decode(max_bytes,-1,'UNLIMITED',max_bytes / 1024 / 1024) as "Max MB"
3 from dba_ts_quotas
4 where tablespace_name = 'USERS' and username = 'USER1'
5 /

TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 UNLIMITED

Finally we can run our CREATE TABLE statement.

SQL> conn user1/pass1
Connected.
SQL> create table t1 as select * from all_objects
2 /

Table created.

Categories: