Study on the Extraction from ORA-01536: space quota exceeded for tablespace

 

R & D colleagues said the database error during table creation: ORA-1536

 

[Oracle @ localhost ~] $ Oerr ora 1, 1536

01536,000 00, "space quota exceededfor tablespace '% S '"

// * Cause: The space quota for the segment owner in the tablespace has

// Been exhausted and the operation attempted the creation of

// New segment extent in the tablespace.

// * Action: Either drop unnecessary objectsin the tablespace to reclaim

// Space or have a privileged user increase the quota on this

// Tablespace for the segment owner.

 

 

The user's tablespace quota is insufficient. It's a bit strange, but there are three ways to solve the problem first:

 

Method 1: SQL> alteruser user_name quota unlimited on tablespace_name;

Method 2: SQL> alteruser user_name quota 100 M on tablespace_name;

Method 3: SQL> grantunlimited tablespace to user_name;

 

For more information about quota, see:

Oracle user's table space quota (quota) Description

Http://blog.csdn.net/tianlesoftware/article/details/6412985

 

 

After I grant the user unlimited tablespace, the problem is solved. Now we need to study this problem. Previously, I assigned this user the "connect", "resource", and "dba" roles. For more information about user roles, see:

Oracle users and Roles

Http://blog.csdn.net/tianlesoftware/article/details/4786956

Then, the dba role is reclaimed, and the user reports the ORA-1536 error when creating the table.

 

When we create a user, the unlimited tablespace permission is granted by default. However, if we grant the DBA role to the user and the revokedba role, the unlimitedtablespace permission will also be revoke. Because the unlimited tablespace permission is under the DBA role. This is one of the reasons for ORA-1536.

 

After we grant the unlimitedtablespace permission, view the dba_ts_quotas table:

SQL> grant unlimited tablespace to dave;

Grant succeeded.

SQL> select * from dba_sys_privs where grantee = 'Dave ';

GRANTEE PRIVILEGE ADM

-------------------------------------------------------------------------

DAVE UNLIMITED TABLESPACE NO

 

SQL> select username, tablespace_name, bytes, max_bytes from dba_ts_quotas where username = 'Dave ';

No rows selected

 

The returned result is null. In view user_ts_quotas:

SQL> conn dave/dave;

Connected.

SQL> select tablespace_name, bytes, max_bytes from USER_TS_QUOTAS;

No rows selected

 

Or empty. We tested how to use the alter user command to modify the quota Configuration:

SQL> alter user dave quota unlimited on system;

User altered.

 

In view dba_ts_quots:

SQL> select username, tablespace_name, bytes, max_bytes from dba_ts_quotas where username = 'Dave ';

 

USERNAME TABLESPACE_NAME BYTES MAX_BYTES

--------------------------------------------------------------------------------

Dave system 0-1

 

Now you can view the related records. Here,-1 indicates unlimited.

 

Modify the quota:

SQL> alter user dave quota 50 M on system;

User altered.

 

SQL> select * from user_ts_quotas;

 

TABLESPACE_NAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO

-------------------------------------------------------------------------

SYSTEM 0 52428800 0 6400 NO

 

The test quota is changed to 50 MB.

 

 

Summary:

1. By default, the unlimited tablespace permission is granted to the created user. However, if a DBA role is assigned, the unlimited tablespace role is also revoked when the DBA role is revoked.

2. if you only grant the unlimited tablespace permission to the user, there are no relevant records in the user_ts_quotas and dba_ts_quotas tables. Only after the quota is modified will the related quota information be available in the two dictionaries.

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

WEAVER: http://weibo.com/tianlesoftware

Email: tianlesoftware@gmail.com

Skype: tianlesoftware