用户表空间配额（User tablespace Quota）

真题1、什么是用户的表空间配额（User tablespace Quota）？

答案：用户的表空间配额也叫表空间限额，指的是用户可以使用指定表空间的最大大小。控制用户的表空间配额也就等于控制用户所占用的表空间大小。在默认情况下，需要对用户赋予RESOURCE角色，虽然该角色没有UNLIMITED TABLESPACE权限，但是对用户赋予了该角色后，系统将会默认给用户赋予UNLIMITED TABLESPACE的系统权限，因此，新建的用户对所有表空间都是没有配额的，即不受空间的限制。表空间配额可以在创建用户的时候指定，也可以在创建用户后再修改用户的配额。关于表空间配额需要注意以下几点：

①　DBA用户是具有UNLIMITED TABLESPACE的权限的，若是回收DBA角色，则会导致UNLIMITED TABLESPACE权限被连带回收，易引起生产事故，所以，在回收DBA角色时需特别注意。

②　当用户使用空间超出限额的时候会报“ORA-01536”和“ORA-01950”的错误。

③　目标用户必须不能含有UNLIMITED TABLESPACE的系统权限，否则空间配额对用户的设置无效，也就会出现在DBA_TS_QUOTAS中的BYTES大于MAX_BYTES的情况。

常用命令如下所示：

CREATE USER LHR_TS_QUOTAS IDENTIFIED BY LHR DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 10M ON USERS; --USERS表空间限额10M

ALTER USER LHR_TS_QUOTAS QUOTA UNLIMITED ON USERS;--修改用户LHR_TS_QUOTAS无配额限制

ALTER USER LHR_TS_QUOTAS QUOTA 5M ON USERS;--修改用户在USERS表空间上为5M限额

SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES FROM DBA_TS_QUOTAS WHERE USERNAME='LHR_TS_QUOTAS';--查询用户的表空间限额

REVOKE UNLIMITED TABLESPACE FROM LHR_TS_QUOTAS;--回收无限制的表空间权限

ALTER USER LHR_TS_QUOTAS QUOTA 0 ON USERS;--执行这条语句后LHR_TS_QUOTAS用户在DBA_TS_QUOTAS视图中就查不到了

测试示例如下所示：

SYS@orclasm > CREATE USER LHR_TS_QUOTAS IDENTIFIED BY LHR DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA 10M ON USERS;

User created.

SYS@orclasm > SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES FROM DBA_TS_QUOTAS WHERE USERNAME='LHR_TS_QUOTAS';

TABLESPACE_NAME USERNAME MAX_BYTES

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

USERS LHR_TS_QUOTAS 10485760

SYS@orclasm > ALTER USER LHR_TS_QUOTAS QUOTA UNLIMITED ON USERS;

User altered.

SYS@orclasm > SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES FROM DBA_TS_QUOTAS WHERE USERNAME='LHR_TS_QUOTAS';

TABLESPACE_NAME USERNAME MAX_BYTES

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

USERS LHR_TS_QUOTAS -1 <--- -1表示没有空间配额限制

SYS@orclasm > ALTER USER LHR_TS_QUOTAS QUOTA 5M ON USERS;

User altered.

SYS@orclasm > SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES FROM DBA_TS_QUOTAS WHERE USERNAME='LHR_TS_QUOTAS';

TABLESPACE_NAME USERNAME MAX_BYTES

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

USERS LHR_TS_QUOTAS 5242880

SYS@orclasm > ALTER USER LHR_TS_QUOTAS QUOTA 0 ON USERS;

User altered.

SYS@orclasm > SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES FROM DBA_TS_QUOTAS WHERE USERNAME='LHR_TS_QUOTAS';

no rows selected

一. 官网的说明

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 quotas for each Oracle Database .

有关Oracle Quota 这块，只在Oracle 的安全管理这块搜到了一些内容。

Managing Security for Oracle Database Users

1.1 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:

（1）Users with privileges to create certain types of objects can create those objects in the specified tablespace.

（2）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.

The following CREATE USER statement assigns the following quotas for the test_ts and data_ts tablespaces:

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.

1.2 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.

1.3 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.

1.4 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.

二. Quota 说明

配额大小指的是用户指定使用表空间的的大小。在1.1 节里提到，默认情况下，用户对所有表空间都是没有配额的，即不受空间的限制。查看几个用户的创建脚本来验证一下：

CREATE USER SYSTEM

IDENTIFIED BY

DEFAULT TABLESPACE SYSTEM

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

-- 2 Roles for SYSTEM

GRANT AQ_ADMINISTRATOR_ROLE TO SYSTEM WITH ADMIN OPTION;

GRANT DBA TO SYSTEM WITH ADMIN OPTION;

ALTER USER SYSTEM DEFAULT ROLE ALL;

-- 5 System Privileges for SYSTEM

GRANT GLOBAL QUERY REWRITE TO SYSTEM;

GRANT CREATE MATERIALIZED VIEW TO SYSTEM;

GRANT CREATE TABLE TO SYSTEM;

GRANT UNLIMITED TABLESPACE TO SYSTEM WITH ADMIN OPTION;

GRANT SELECT ANY TABLE TO SYSTEM;

CREATE USER DAVE

IDENTIFIED BY

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

-- 2 Roles for DAVE

GRANT CONNECT TO DAVE;

GRANT RESOURCE TO DAVE;

ALTER USER DAVE DEFAULT ROLE ALL;

-- 1 System Privilege for DAVE

GRANT UNLIMITED TABLESPACE TO DAVE;

从这2个脚本来看，默认情况下，都会对用户赋 unlimited tablespace 的权限。这是是在创建的时候指定的，当我们的用户创建好之后，我们也可以修改用户的配额。

有关用户的配额的操作说明

1. 创建用户时，指定限额

SQL> conn / as sysdba;

Connected.

SQL> create user anqing identified by anqing default tablespace users temporary tablespace temp quota 10M on users;

User created.

查询用户配额的信息：

SQL> select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='ANQING';

TABLESPACE_NAME USERNAME MAX_BYTES

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

USERS ANQING 10485760

2.更改用户的表空间限额：

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

SQL> grant unlimited tablespace to anqing;

Grant succeeded.

这种方式是全局性的. 即修改用户多所有表空间的配额。

如果我们想改某个具体的,即针对用户的某个特定的表空间，可以使用如下SQL：

SQL> alter user anqing quota unlimited on users;

User altered.

查看配额：

SQL> select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='ANQING';

TABLESPACE_NAME USERNAME MAX_BYTES

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

USERS ANQING -1

这时候max_bytes 为-1，即不受限制。

3. 回收用户对表空间的配额:

同样两种方式，

全局：

SQL> revoke unlimited tablespace from anqing;

Revoke succeeded.

在查看配额，已经没有了相关信息：

SQL> select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='ANQING';

no rows selected

针对某个特定的表空间：

SQL> alter user anqing quota 0 on users;

User altered.

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

SQL> CREATE USER user01

IDENTIFIED BY oracle

DEFAULT TABLESPACE tbs1

TEMPORARY TABLESPACE temp

PROFILE default

SQL> GRANT create session, create table TO user01;

While executing the command to create a table, the user gets the following error message and the CREATE

TABLE.. command fails.

ERROR at line 1:

ORA-01950: no privileges on tablespace

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

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

表空间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

quota的日常管理

常见问题

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

ORA-01950: no privileges on tablespace

解决办法：

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.

可以使用下列语句来创建用户

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 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:

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

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.

About Me

...............................................................................................................................

● 本文整理自网络

● 本文在itpub（）、博客园（）和个人微信公众号（）上有同步更新

● 本文itpub地址：

● 本文博客园地址：

● 本文pdf版及小麦苗云盘地址：

● 数据库笔试面试题库及解答：

● QQ群：230161599 微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。