Oracle 用户配额

一、创建用户，分配配额
SQL> create user jerry identified by jerry default tablespace users temporary tablespace temp quota 10m on users;

User created

二、查看用户配额
SQL> SELECT * FROM DBA_TS_QUOTAS;

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------
USERS JERRY 0 10485760 0 1280 NO
SYSAUX APPQOSSYS 0 -1 0 -1 NO
SYSAUX FLOWS_FILES 0 -1 0 -1 NO
SYSAUX SYSMAN 88408064 -1 10792 -1 NO
SYSAUX OLAPSYS 7667712 -1 936 -1 NO

三、不对用户做表空间限额控制
SQL> grant unlimited tablespace to jerry;

Grant succeeded

四、对某个用户有某个表空间上不受限制，使用如下SQL
SQL> alter user jerry quota unlimited on users;

User altered

SQL> SELECT * FROM DBA_TS_QUOTAS where username='JERRY';

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------
USERS JERRY 0 -1 0 -1 NO

-1表示没有限制

五、回收用户对表空间的配额
SQL> SELECT * FROM DBA_TS_QUOTAS where username='JERRY';

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------
USERS JERRY 0 -1 0 -1 NO

SQL> revoke unlimited tablespace from jerry;

Revoke succeeded

SQL> SELECT * FROM DBA_TS_QUOTAS where username='JERRY';

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------
六、针对某个特定的表空间
SQL> alter user jerry quota 0 on users;

User altered