一、创建用户，分配配额
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
阅读(4036) | 评论(0) | 转发(0) |
