Oracle的表空间quota详解

  当你创建用户的时候， 如下：

SQL> CREATE USERuser01
IDENTIFIED BY oracle
DEFAULT TABLESPACE tbs1
TEMPORARY TABLESPACE temp
PROFILE default
SQL> GRANT create session, create table TOuser01;
While executing the command to create a table, the user gets thefollowing error message and the CREATE
TABLE.. command fails.
ERROR at line 1:
ORA-01950: no privileges on tablespace

 

   解释： defaulttablespace是定义了用户在不写明表空间时,使用的表空间,而因表空间管理的要求,oracle必须要能管理他(用户)的可用大小,就有了quota子句.

        GRANT create session, createtable TO user01 是指user01拥有了建表的权限 ,oracle也知道了他也有了默认的表空间,但默认的表空间没有给他分配空间,所以出错。你可以在建用户时加上quota 200M on tbs1(给他200M 空间) 或直接 alter user user01 unlimited on tbs1(让他随意使用tbs1表空间)。所以在建用户的过程中，需要让默认的表空间给该用户分配空间（也可以分配无限使用），当此用户用到表空间的配额后，再也不能使用空间，除非再次申请。

 

表空间quota概述

Oracle 官网对quota的定义如下： A limit ona resource, such as a limit on the amount of database storage usedby a database user. A database administrator can set tablespacequotas for each Oracle Database username
有关Oracle Quota 这块可以参考Oracle官方文档
http://download.oracle.com/docs/cd/E11882_01/network.112/e16543

quota的日常管理

常见问题

ORA-01536:space  quota  exceeded  for  table  space   'CYYD'

ORA-01950:no privileges ontablespace 
解决办法：
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 thefollowing:
Users with privileges to create certain types of objects can createthose objects in the specified tablespace.
Oracle Database limits the amount of space that can be allocatedfor storage of a user's objects within the specified tablespace tothe amount of the quota.
By default, a user has no quotaon any tablespace in the database.If the user has the privilege tocreate a schema object, then you must assign a quota to allow theuser to create objects. At a minimum, assign users a quota for thedefault tablespace, and additional quotas for other tablespaces inwhich they can create objects.

可以使用下列语句来创建用户
CREATE USERjward
 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 quotasfor a specific amount of disk space in each tablespace or anunlimited amount of disk space in all tablespaces. Specific quotasprevent a user's objects from using too much space in thedatabase.
You can assign quotas to a user tablespace when you create theuser, or add or change quotas later. (You can find existing userquotas by querying the USER_TS_QUOTAS view.) 。
 If a new quota is less than the old one, then thefollowing conditions remain true:
（1）If a user has already exceeded a new tablespace quota, then theobjects of a user in the tablespace cannot be allocated more spaceuntil the combined space of these objects is less than the newquota.
（2）If a user has not exceeded a new tablespace quota, or if thespace used by the objects of the user in the tablespace falls undera new tablespace quota, then the user's objects can be allocatedspace up to the new quota.
Restricting the QuotaLimits for User Objects in a Tablespace
You can restrict the quota limits for user objectsin a tablespace by using the ALTER USER SQL statement to change thecurrent quota of the user to zero.
 After a quota of zero is assigned, the objects ofthe user in the tablespace remain, and the user can still createnew objects, but the existing objects will not be allocated any newspace.
For example, you could not insert data into one of this user'sexiting tables. The operation will fail with an ORA-1536 spacequota exceeded for tables error.
Granting Users the UNLIMITEDTABLESPACE System Privilege
To permit a user to use an unlimited amount of any tablespace inthe database, grant the user the UNLIMITED TABLESPACE systemprivilege. This overrides all explicit tablespace quotas for theuser. If you later revoke the privilege, then you must explicitlygrant quotas to individual tablespaces. You can grant thisprivilege only to users, not to roles.
Before granting the UNLIMITED TABLESPACE system privilege, you mustconsider the consequences of doing so.
Advantage:
You can grant a user unlimited access to all tablespaces of adatabase with one statement.
Disadvantages:
（1）The privilege overrides all explicit tablespace quotas for theuser.
（2）You cannot selectively revoke tablespace access from a user withthe UNLIMITED TABLESPACE privilege. You can grant selective orrestricted access only after revoking the privilege.
Listing All Tablespace Quotas
Use the DBA_TS_QUOTAS view to list all tablespace quotasspecifically 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 isindicated in the MAX_BYTES column. This number is always amultiple of the database block size, so if you specify a tablespacequota that is not a multiple of the database block size, then it isrounded up accordingly.Unlimited quotas are indicatedby -1.

 

注意当对用户赋予resource角色时将同时赋予unlimitedtablespace的系统权限。详情见下文

 

 

创建用户

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

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

查询resource角色所具有的系统权限

SQL> select * from dba_sys_privs whereGRANTEE='RESOURCE';

GRANTEE                       PRIVILEGE                               ADM
---------------------------------------------------------------------- ---
RESOURCE                      CREATETRIGGER                          NO
RESOURCE                      CREATESEQUENCE                         NO
RESOURCE                      CREATETYPE                             NO
RESOURCE                      CREATEPROCEDURE                        NO
RESOURCE                      CREATECLUSTER                          NO
RESOURCE                      CREATEOPERATOR                         NO
RESOURCE                      CREATEINDEXTYPE                        NO
RESOURCE                      CREATETABLE                            NO
查看用户所具有的角色
SQL> select * from dba_role_privs whereGRANTEE='TEST_PRIVS';
GRANTEE                       GRANTED_ROLE                  ADM DEF
------------------------------ ------------------------------ ------
TEST_PRIVS                    RESOURCE                      NO  YES
查询用户所具有的系统权限

SQL> select * from dba_sys_privs whereGRANTEE='TEST_PRIVS';
GRANTEE                       PRIVILEGE                               ADM
---------------------------------------------------------------------- ---
TEST_PRIVS                    UNLIMITEDTABLESPACE                    NO

可以看到，Oracle默认的把unlimitedtablespace的系统权限赋予了用户

 

 

查询表空间

SQL> selectTABLESPACE_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
可以看到对于具有unlimitedtablespace系统权限的用户，在dba_ts_quota上没有体现。

这里补充说一句，一般创建用户时，如果没有特殊需求只要将resource和connect角色赋予用户即可。
SQL>select * fromdba_sys_privs where GRANTEE='CONNECT';
ROLE                          PRIVILEGE                               ADM
---------------------------------------------------------------------- ---
CONNECT                       CREATESESSION                          NO

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