oracle表空间配额（quota）与UNLIMITED TABLESPACE系统权限
渔夫数据库笔记 2014-05-20 21:13:28
分类专栏： ORACLE数据库 文章标签： quota dba_ts_quotas 表空间配额 UNLIMITED TABLESPACE
一：quota相关视图
1)dba_ts_quotas(查看所有用户的表空间配额)
BYTES字段表示用户已经使用的空间；MAX_BYTES如果为-1表示没有限制，其他值表示限制配额
--只有用alter user user_name quota on tablespace_name;命令显示的调整过用户的空间配额，该视图中才会有记录，否则该视图没有记录
--如果该视图没有信息，我们可以通过user_ts_quotas视图来了解用户的表空间限额信息
- user_ts_quotas(查看当前用户表空间配额)
--注意可能有时我们查询该视图，用户在某个表空间的配额为0，但是我们依然可以使用该表空间，那么说明用户被赋予了UNLIMITED TABLESPACE。
注：UNLIMITED TABLESPACE系统权限：此系统权限会覆盖所有的单个表空间限额，并向用户提供所有表空间（包括SYSTEM和SYSAUX）的无限制限额（注：授予resource角色的时候也会授予此权限）
--SQL> select *from user_sys_privs;查看用户是否具有UNLIMITED TABLESPACE权限
二：resource角色和quota
注意resource角色被赋予用户时，用户就自动被赋予了UNLIMITED TABLESPACE权限（虽然resource中并不包含该权限）如果我们把resource角色从用户收回，那么UNLIMITED TABLESPACE权限也会自动收回
注意如果你把resource角色赋予另外一个角色a，然后再把角色a赋予用户，那么用户不会被赋予UNLIMITED TABLESPACE权限
SQL> select * from role_sys_privs where role='RESOURCE'; >>resource角色中并不包含UNLIMITED TABLESPACE权限
ROLE PRIVILEGE ADM
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
SQL> grant connect,resource to test1;
SQL> conn test1/oracle1234;
SQL> select * from session_privs; >>可以看到被赋予resource角色以后，自动拥有了UNLIMITED TABLESPACE权限
PRIVILEGE
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
三：配额管理
1)建用户时指定配额
SQL> create user java identified by oracle default tablespace users temporary tablespace temp quota 100m on users quota 100m on system;
--注意temp表空间不支持表空间配额
2)更改用户表空间配额
grant unlimited tablespace to java;--不对用户作配额限制
alter user java quota unlimited on users;--对指定表空间不做限额
alter user java quota 1000m on users;--指定用户在某表空间限制
revoke unlimited tablespace from java;--收回用户的unlimited tablespace权限
alter user java quota 0 on users;--针对alter … quota unlimited …;
--如果用户已经被授予了unlimited tablespace权限的话，那么alter user java quota 0 on users;限制是不起作用的
四：表空间已删除，关于表空间配额信息还存在
表空间被删除以后但是关于该表空间的配额信息在视图中依然能够查到
这个是正常的,因为表空间被drop后,ts$和tsq$中的信息并没有被清理,如果你重建一个同名的tablespace,并限制quota 为0,tsq$中的记录就被删除了.
从10gR2开始,你可以使用
drop tablespace mytsincluding contents and datafiles drop quota;
这样的语法,一并将quota信息删除,默认是keep quota.
trace的时候你会发现,如果加上drop quota这样的options clause,那么就会有
select user# from tsq$ wherets#=:1
delete from tsq$ where ts#=:1and user#=:2
...
这样的动作.而dba_ts_quotas视图的定义正是使用了tsq$中的信息
create or replace viewsys.dba_ts_quotas
(tablespace_name, username, bytes, max_bytes, blocks, max_blocks, dropped)
as
select ts.name, u.name, nvl(spc.bytes, 0),
decode(q.maxblocks, -1, -1, q.maxblocks *ts.blocksize),
nvl(spc.blocks, 0), q.maxblocks, decode(ts.online$,3, 'YES', 'NO')
from sys.tsq$ q, sys.ts$ ts, sys.tbs_space_usagespc, sys.user$ u
where q.ts# = ts.ts#
and q.user# = u.user#
and spc.tsn = ts.ts#
and spc.owner = u.name
and q.maxblocks != 0
本文出自 “技术” 博客，请务必保留此出处http://589985.blog.51cto.com/1609992/1336658