quota在oracle,Oracle的表空间quota详解

转载至：http://czmmiao.iteye.com/blog/1291984

表空间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官方文档

http://download.oracle.com/docs/cd/E11882_01/network.112/e16543

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 TablespaceYou 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 -1When 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角色赋予用户即可。select * from dba_sys_privs where GRANTEE='CONNECT';

ROLE PRIVILEGE ADM

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

CONNECT CREATE SESSION NO

sql> grant resource,connect to test_privs;

Grant succeeded.

参考至:http://docs.oracle.com/cd/E11882_01/network.112/e16543/users.htm#DBSEG10220

http://tech.it168.com/o/2006-04-08/200604081532523.shtml

http://www.itpub.net/thread-345851-1-1.html

本文原创，转载请注明出处、作者

如有错误，欢迎指正

邮箱:czmcj@163.com