ORA-01536: space quota exceeded for tablespace 錯誤的深入分析

今天在使用者kf下面刪除一條資料時：
delete from kf_gongnengtx where texingid=2736;

報錯：
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 QUOTA [K/M] ON

2. suggest QUOTA UNLIMITED(即去掉配額限制，unlimited，推薦使用這種，否則當第一個方法用後，如果再不夠，那豈不是要再進行分配限額！):
ALTER USER QUOTA UNLIMITED ON

方法2的另外一種寫法：
grant unlimited tablespace to ;

於是在sqlplus下面以sysdba許可權登陸操作：
SQL> grant unlimited tablespace to kf;

Grant succeeded.

SQL> delete from kf_gongnengtx where texingid=2736;

1 row deleted

操作成功，問題解決！

本來到這裡也告一段落，結果在一篇文章上面（）發現產生這個問題可能與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 或者revoke resource from 時，如果空間不夠，就會產生該問題

下面是相關描述：

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

Example:

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:

Solution

To Resolve this issue you need to :

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

http://blog.csdn.net/rudyMatrix/article/details/4823445