oracle的表空間quota詳講

表空間quota概述

Oracle 官網對quota的定義如下： A limit on a resource, such as a limit on the amount of database storage used by a database user. A database administrator can set tablespace quotas for each Oracle Database username
有關Oracle Quota 這塊可以參考Oracle官方文件

quota的日常管理

常見問題

ORA-01536:space   quota   exceeded   for   table   space   'CYYD' 
解決辦法： 
alter   user   USERNAME   quota   100M   on   TABLESPACENAME;  
alter   user   USERNAME   quota   unlimited   on   TABLESPACENAME; 
grant   unlimited   tablespace   to   USERNAME; 
quota是為了限制使用者對錶空間的使用,比如你限制使用者Guotu在tablespace  CYYD中的quota為10m，當使用者Guotu在tablespace   CYYD中的資料量達到10m後，無論你的tablespace   CYYD中有多少空間，Guotu都無法再使用tablespace   CYYD了。
所以你需要： 
alter   user   aGuotu  quota   1000M   on   CYYD; 
alter   user   Guotu   quota   unlimited   on   CYYD; 
grant   unlimited   tablespace   to   Guotu

dba_ts_quotas

與quota相關的資料字典檢視為dba_ts_quotas，以下是相關的資訊
Assigning a Tablespace Quota for the User
You can assign each user a tablespace quota for any tablespace (except a temporary tablespace). Assigning a quota accomplishes the following:
Users with privileges to create certain types of objects can create those objects in the specified tablespace.
Oracle Database limits the amount of space that can be allocated for storage of a user's objects within the specified tablespace to the amount of the quota.
By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, then you must assign a quota to allow the user to create objects. At a minimum, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they can create objects.

可以使用下列語句來建立使用者
CREATE USER jward
 IDENTIFIED BY password
 DEFAULT TABLESPACE data_ts
 QUOTA 100M ON test_ts
 QUOTA 500K ON data_ts
 TEMPORARY TABLESPACE temp_ts
 PROFILE clerk;

配額的指定可以禁止使用者的物件使用過多的表空間

You can assign a user either individual quotas for a specific amount of disk space in each tablespace or an unlimited amount of disk space in all tablespaces. Specific quotas prevent a user's objects from using too much space in the database.
You can assign quotas to a user tablespace when you create the user, or add or change quotas later. (You can find existing user quotas by querying the USER_TS_QUOTAS view.) 。
 If a new quota is less than the old one, then the following conditions remain true:
（1）If a user has already exceeded a new tablespace quota, then the objects of a user in the tablespace cannot be allocated more space until the combined space of these objects is less than the new quota.
（2）If a user has not exceeded a new tablespace quota, or if the space used by the objects of the user in the tablespace falls under a new tablespace quota, then the user's objects can be allocated space up to the new quota.
Restricting the Quota Limits for User Objects in a Tablespace 
You can restrict the quota limits for user objects in a tablespace by using the ALTER USER SQL statement to change the current quota of the user to zero.
 After a quota of zero is assigned, the objects of the user in the tablespace remain, and the user can still create new objects, but the existing objects will not be allocated any new space.
For example, you could not insert data into one of this user's exiting tables. The operation will fail with an ORA-1536 space quota exceeded for tables error.
Granting Users the UNLIMITED TABLESPACE System Privilege 
To permit a user to use an unlimited amount of any tablespace in the database, grant the user the UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the user. If you later revoke the privilege, then you must explicitly grant quotas to individual tablespaces. You can grant this privilege only to users, not to roles.
Before granting the UNLIMITED TABLESPACE system privilege, you must consider the consequences of doing so.
Advantage:
You can grant a user unlimited access to all tablespaces of a database with one statement.
Disadvantages:
（1）The privilege overrides all explicit tablespace quotas for the user.
（2）You cannot selectively revoke tablespace access from a user with the UNLIMITED TABLESPACE privilege. You can grant selective or restricted access only after revoking the privilege.
Listing All Tablespace Quotas
Use the DBA_TS_QUOTAS view to list all tablespace quotas specifically assigned to each user. For example: 
SELECT * FROM DBA_TS_QUOTAS;
 TABLESPACE    USERNAME    BYTES     MAX_BYTES    BLOCKS    MAX_BLOCKS
----------    ---------  --------   ----------   -------   ----------
USERS         JFEE              0       512000         0          250
USERS         DCRANNEY          0           -1         0           -1 
When specific quotas are assigned, the exact number is indicated in the MAX_BYTES column . This number is always a multiple of the database block size, so if you specify a tablespace quota that is not a multiple of the database block size, then it is rounded up accordingly. Unlimited quotas are indicated by -1. 
注意當對使用者賦予resource角色時將同時賦予unlimited tablespace的系統許可權。詳情見下文

建立使用者 

SQL> create user test_privs identified by test_privs default tablespace users;
User created.

SQL> select * from dba_sys_privs where GRANTEE='TEST_PRIVS';
no rows selected 
賦予resource角色 
SQL> grant resource to TEST_PRIVS;
Grant succeeded. 

查詢resource角色所具有的系統許可權

SQL> select * from dba_sys_privs where GRANTEE='RESOURCE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO
檢視使用者所具有的角色 
SQL> select * from dba_role_privs where GRANTEE='TEST_PRIVS';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
TEST_PRIVS                     RESOURCE                       NO  YES
查詢使用者所具有的系統許可權 

SQL> select * from dba_sys_privs where GRANTEE='TEST_PRIVS';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
TEST_PRIVS                     UNLIMITED TABLESPACE                     NO 
可以看到，Oracle預設的把unlimited tablespace的系統許可權賦予了使用者

查詢表空間

SQL> select TABLESPACE_NAME,USERNAME,BYTES,MAX_BYTES from dba_ts_quotas;

TABLESPACE_NAME                USERNAME        BYTES  MAX_BYTES
------------------------------ ---------- ---------- ----------
INDX                           HR              65536   10485760
SYSAUX                         OLAPSYS      16318464         -1
USERS                          HR             196608         -1
SYSAUX                         SYSMAN       54460416         -1
SYSAUX                         DMSYS          262144  209715200
TRANS                          TRANS               0   10485760
可以看到對於具有unlimited tablespace系統許可權的使用者，在dba_ts_quota上沒有體現。

這裡補充說一句，一般建立使用者時，如果沒有特殊需求只要將resource和connect角色賦予使用者即可。
SQL> select * from dba_sys_privs where GRANTEE= 'CONNECT';
ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO 

SQL> grant resource,connect to test_privs;
Grant succeeded.