quota 管理：对象的最大存储空间，用户在表空间上建立对象，必须在相应的tablespace 上获得quota。
1、查看用户磁盘配额使用信息
SQL> select TABLESPACE_NAME,USERNAME,BYTES/1024,MAX_BYTES/1024/1024 from dba_ts_quotas;
TABLESPACE_NAME USERNAME BYTES/1024 MAX_BYTES/1024/1024
-------------------- --------------- ---------- -------------------
USERS ROSE 0 10
USERS OPS$ORACLE 0 10
——BYTES 已经使用过的配额，MAX_BYTES所分配的配额
SQL> grant create table to rose;
Grant succeeded.
SQL> grant select on scott.emp to rose;
Grant succeeded.
SQL> conn rose/rose
Connected.
SQL> create table emp1 as select * from scott.emp;
Table created.
SQL> conn /as sysdba
Connected.
SQL> select TABLESPACE_NAME,USERNAME,BYTES/1024,MAX_BYTES/1024/1024 from dba_ts_quotas
2 where username='ROSE';
TABLESPACE_NAME USERNAME BYTES/1024 MAX_BYTES/1024/1024
-------------------- --------------- ---------- -------------------
USERS ROSE 64 10
2、回收磁盘配额
回收quota：只能回收用户未使用的磁盘配额。
SQL> alter user rose quota 0 on users;
User altered.
SQL> select TABLESPACE_NAME,USERNAME,bytes/1024,max_bytes/1024/1024 from dba_ts_quotas
2 where username='ROSE';
no rows selected
——已经回收，但仍然可以插入数据，因为之前已经使用的磁盘配额没用完
SQL> conn rose/rose
Connected.
SQL> insert into emp1 select * from emp1;
14 rows created.
SQL> /
28 rows created.
SQL> /
56 rows created.
SQL> /
112 rows created.
SQL> /
224 rows created.
SQL> /
insert into emp1 select * from emp1
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
10:31:58 SQL> analyze table emp1 compute statistics; —— 没磁盘配额了
Table analyzed.
SQL> select table_name,num_rows ,blocks,empty_blocks from user_tables;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
EMP1 448 8 0
查看本栏目更多精彩内容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/