ORA-01536

  正在开会，同事跑过来说数据库有问题，通讯程序不能入库，赶快获取一条insert into a values()语句后在toad工具中手动插入，发现报错：Ora-01536：超出了表空间users的空间限量。

  该表a的是用户A下的一个大表，表空是users，而非A用户的默认表空间。users表空间有大约70%的空闲空间，为什么a表就不能使用了呢？从网上搜索后终于明白：

  ora-1536 是指的你建表的那个user 所能使用的空间没有了，不是那个表所在的tablespace 没有free space 了。你需要做的是给那个user 对那个tablespace 有更多的space 可以使用。

  解决办法增大能够使用的表空间数或授予无限制的使用权限

   ALTER USER A QUOTA 50M ON users;

 ALTER USER  A  QUOTA UNLIMITED ON users;

  但为什么会出现这样的问题呢，原来设计的时候用户A，赋予DBA和resource权限，并没有设置表空间限额。但现在怎么会出现这么多的空间限额呢？

会不会是上次更改用户A的默认表空间造成的呀？

上次为了数据库便于维护，将用户A的表从别的表空间移到了表空间中，然后更改表空间TS_A 为用户A的默认表空间

 select 'alter table '||table_name||' move tablespace TS_A ;'

from user_tables

 where tablespace_name='LEE_TEST' ;

alter user A DEFAULT TABLESPACE TS_A;

把用户A下的表全部移到表空间TS_A下，但因为a是一个比较大的表，所以没有移动成功，a的表空间仍然是users；

 

估计跟这次操作有关系 ，改天进行测试。那天进行移动表的存储空间时还造成索引无效的错误，看来这次操作带来负面影响还是真不少。

 

 

创建一个用户,分配了400M的表空间,结果在用到13.3M时报错: 
ORA-01536: 超出表空间 '***' 的空间限额 

经查,表空间跟表空间限额两个值是不一样的. 
推测按默认的话oracle应该会给每个用户分配一个默认的表空间限额,具体比例待查,但这比例肯定远小于100%. 
所以说分配了400M的表空间未必能存储400M的数据. 

解决办法如下: 

查看用户表空间的限额 
select * from user_ts_quotas; 

max_bytes字段就是了 
-1是代表没有限制,其它值多少就是多少了. 

不对用户做表空间限额控制: 
GRANT UNLIMITED TABLESPACE TO ***(用户); 
这种方式是全局性的. 
或者 
alter user ***(用户名) quota unlimited on ***(表空间); 
这种方式是针对特定的表空间的. 

可以分配自然也可以回收了: 
revoke unlimited tablespace from ***(用户) 
或者 
alter user *** quota 0 on ***

 

表空间的大小与用户的配额大小是两种不同的概念。表空间的大小是指实际的用户表空间的大小，而配额大小指的是用户指定使用表空间的的大小

把表空间文件增大，还是出现这个问题，用户在使用表空间的同时使用空间的限额，如果超出限制，就算有空的地方，也不会让用户使用。

遇到ORA-01536错误，首先要查看用户的表空间的限额

   select * from dba_ts_quotas;

   select * from user_ts_quotas;

max_bytes字段-1是代表没有限制,其它值多少就是多少. 

dba_ts_quotas ：描述所有用户表空间的限额

user_ts_quotas ：描述当前用户表空间的限额。

如果查询结果中max_bytes字段不为-1，修改为无限制或者指定的大小。

不对用户做表空间限额控制:

    GRANT UNLIMITED TABLESPACE TOuser;

这种方式是全局性的。  或者

    alter user  user  quota unlimited on  user_tablespace;

 这种方式是针对特定的表空间的.

回收表空间限额控制:

    revoke unlimited tablespace from  user;

或者

    alter user  user  quota 0 on  user_tablespace;

报错：
ORA-01536: space quota exceeded for tablespace 'TS_HIS3'

 

根据网上资料，该问题是由于用户在使用表空间时候受到了权限的限制.
解决方案很简单,修改这个用户的使用这个表空间的空间的权限就可以了

 

oracle提供二种方法：
1. Increase the tablespace quota allocated to that user or role by using the following command(即增加具体的大小限额):
ALTER USER <username> QUOTA <integer> [K/M] ON <tablespacename>

2. suggest QUOTA UNLIMITED(即去掉配额限制，unlimited，推荐使用这种，否则当第一个方法用后，如果再不够，那岂不是要再进行分配限额！):
ALTER USER <username> QUOTA UNLIMITED ON <tablespacename>

方法2的另外一种写法：
grant unlimited tablespace to <username>;

 

于是在sqlplus下面以sysdba权限登陆操作：
SQL> grant unlimited tablespace to kf;

Grant succeeded.

SQL> delete from kf_gongnengtx where texingid=2736;

1 row deleted

操作成功，问题解决！

 

本来到这里也告一段落，结果在一篇文章上面（http://www.dbasupport.com/forums/archive/index.php/t-47410.html）发现产生这个问题可能与role是DBA和RESOURCE被revoke有关，

 

里面有一段关键的话：

UNLIMITED TABLESPACE is kind of "wierd" system privilege. It is not granted to any role (and in fact it CAN NOT BE GRANTED to any role!), yet when you grant some powerfull roles (like DBA and I think RESOURCE too - it might even be version dependand AFAIK) to user, Oracle "silently" adds that UNLIMITED TABLESPACE privilege to that user directly. And when you revoke such role from user, Oracle silently revokes UNLIMITED TABLESPACE privilege from user too!

Now, when someone gets UNLIMITED TABLESPACE granted, it doesn't meen taht quotas on all tablespaces are set to unlimited to him. His quotas on tablespaces remain exactly the same - if he had quota 0 on tablespace USERS before, he still has quota 0 on that tablespace now. But when he has UNLIMITED TABLESPACE privilege, this takes priority over any quota settings and therefore he will be able to use space in that tablespace for new extents. However, if you later revoke UNLIMITED TABLESPACE from that user, his quota 0 will be put in effect and he will no longer be able to use any additional available space from tablespace USERS.

 

继续深入：
在metalink上面找到ORA-01536 After Revoking DBA Role [ID 465737.1]的文档，原来grant 或者revoke role DBA 或者 RESOURCE会导致该问题，然后我做了metalink上面的实验，果然如此，当你revoke DBA from <username>或者revoke resource from <username>时，如果空间不够，就会产生该问题

 

下面是相关描述：

ORA-01536: space quota exceeded for tablespace '<Tablespace_Name>'
After revoking DBA or Resource Role from a user

Example:

SQL> conn /as sysdba
Connected.
SQL> create user testrights identified by testos;
User created.
SQL> grant connect, resource to testrights;
Grant succeeded.
SQL> connect testrights/testos;
Connected.

SQL> CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL 
, CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS" STORAGE ( INITIAL 64M) ;
Table created.

SQL> conn /as sysdba
Connected.
SQL> grant dba to testrights;
Grant succeeded.
SQL> revoke dba from testrights;
Revoke succeeded.
SQL> show user
USER is "SYS"
SQL> drop table testrights.testtab;
Table dropped.
SQL> conn testrights/testos;
Connected.
SQL> CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL 
, CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS"  STORAGE ( INITIAL 64M) ;

CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL , 
CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS" 
 STORAGE ( INITIAL 64M) 
*
ERROR at line 1:
ORA-1536: space quota exceeded for tablespace 'USERS' 

SQL> conn /as sysdba
Connected.
SQL> grant connect, resource to testrights;
Grant succeeded.

SQL> conn testrights/testos;
Connected.
SQL> 
SQL> CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL , CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS" 
STORAGE ( INITIAL 64M) ;

Table created.

 

Cause

This issue has been discussed in Bug 6494010.
The behavior seen in the above example is expected and not a bug

When roles were first introduced into Oracle in 7.0, the old Oracle V6 privileges of  RESOURCE and DBA were migrated to use the new role functionality. But because  the RESOURCE and DBA roles are not allowed to be granted UNLIMITED  TABLESPACE, in order to preserve the backwards compatibility with V6, the  parser automatically transforms statements such that "grant resource to abc" automatically becomes "grant resource, unlimited tablespace to abc" and  "revoke resource from abc" automatically becomes "revoke resource, unlimited  tablespace from abc". The same is true when granting and revoking the DBA  role. This behaviour used to be well documented in the SQL reference guide which read:

Note: If you grant or revoke the RESOURCE or DBA role to or from a  user, Oracle implicitly grants or revokes the UNLIMITED TABLESPACE system privilege to or from the user.

 

Solution

To Resolve this issue you need to :

1] Grant DBA or Resource Role back to the user from whom it was revoked.