Oracle 用户 对 表空间 配额(quota )

转载 2012年03月29日 20:31:57
 
一. 官网的说明
 
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 的安全管理这块搜到了一些内容。
            Managing Security for Oracle Database Users
            http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/users.htm#DBSEG10220
 
 
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          250USERS         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 <password>
  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 <password>
  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.
 
 
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/tianlesoftware/archive/2011/05/11/6412985.aspx

oracle表空间配额(quota)与UNLIMITED TABLESPACE系统权限

--先大概写一下,后面有时间会整理一下一:quota相关视图1)dba_ts_quotas(查看所有用户的表空间配额)BYTES字段表示用户已经使用的空间;MAX_BYTES如果为-1表示没有限制,其...
  • shaochenshuo
  • shaochenshuo
  • 2014年05月20日 21:13
  • 2874

Linux入门:用户磁盘配额quota

1、如果任何用户可以随意使用磁盘空间,也是一件很可怕的事情,磁盘被写满之后,各种应用也将无法运行,甚至启动。使硬盘空间被占满有很多种方法:dd if=/dev/zero方法,或者copy大量无用文件等...
  • foryouslgme
  • foryouslgme
  • 2016年11月03日 15:25
  • 2450

磁盘配额:NAS 用户 容量限制

磁盘配额:disk quota           通过实现磁盘配额可以限制磁盘空间,当一个用户消耗太多的磁盘空间或磁盘分区被写满时给系统管理员报警。           在linux中我们只能使用超...
  • cgm88s
  • cgm88s
  • 2015年12月17日 17:16
  • 1641

Oracle 用户 对 表空间 配额(quota ) 说明

  一. 官网的说明 Oracle 官网对quota的定义如下:            A limit on a resource, such as a limit on the amount of ...
  • tianlesoftware
  • tianlesoftware
  • 2011年05月11日 20:56
  • 21353

Oracle 用户 对 表空间 配额(quota ) 说明

一. 官网的说明   Oracle 官网对quota的定义如下:             A limit on a resource, such as a limit on the amount...
  • xujinyang
  • xujinyang
  • 2011年09月28日 11:52
  • 666

Oracle 用户 对 表空间 配额(quota ) 说明

Oracle 用户 对 表空间 配额(quota ) 说明 分类: Oracle 基础知识 2011-05-11 20:56 1505人阅读 评论(0) 收藏 举报     一....
  • launch_225
  • launch_225
  • 2012年03月16日 09:55
  • 645

Oracle 用户对表空间配额quota说明

一、解决问题:解决表空间很大,总显示空间不足的问题ORA-01536:space   quota   exceeded   for   table   space   AAA 二、三个解决办法:   ...
  • TheoGo
  • TheoGo
  • 2011年06月24日 09:28
  • 1497

ORACLE中查找所有表,查找用户权限,修改表空间配额的命令

——————————————————————查看用户权限—————————————————————— 在Oracle中,用户权限分为两种(本文不涉及dba或oper的权限,只讲解普通用户的权...
  • tianqi1332
  • tianqi1332
  • 2012年02月08日 12:16
  • 322

Oracle 创建用户、修改用户信息、为用户分配权限、为用户分配表空间配额、删除用户

在创建用户的时候,需要为用户分配默认表空间与临时表空间。可以通过静态字典dba_tablespaces查看数据库表空间信息。通过desc  dba_tablespaces;查看数据库表空间信息:如下图...
  • lori_xj
  • lori_xj
  • 2014年03月26日 12:46
  • 2485

使用QUOTA(磁盘配额)来限制用户空间

开启QUOTA支持 首先需要修改内核加入对quota的支持machine i386 cpu I686_CPU #ident GENERIC ident CNOSvhost maxusers ...
  • zkdkeen
  • zkdkeen
  • 2011年03月29日 00:25
  • 327
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle 用户 对 表空间 配额(quota )
举报原因:
原因补充:

(最多只允许输入30个字)