管理用户（四）--用户磁盘配额的管理

 

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