1、基本语法
create user abc
identified by abc
default tablespace data01
temporary tablespace temp
quota 1m on data01
2、Use the DBA_TS_QUOTAS view to list all tablespace quotas specifically assigned to each user. For example:
SQL> SELECT * FROM DBA_TS_QUOTAS where username='ABC';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---------- ---
DATA01 ABC 393216 1048576 48 128 NO
3、更改、回收磁盘配额
SQL> alter user abc quota 0 on data01;
User altered.
SQL> revoke unlimited tablespace from abc;
Revoke succeeded.
4、磁盘配额为0时
1)、10G,是不能在创建对象了
SQL> alter user abc quota 0 on data01;
User altered.
SQL> create table ac(x int,y int);
create table ac(x int,y int)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'DATA01'
2)、11G取决于参数 deferred_segment_creation(延时创建段),false的时候也是不能创建对象的,true启用了延时创建段(默认),这样就可以。
SQL> alter system set deferred_segment_creation=true scope=both;
系统已更改。
另外,配额更改为0之后,已经分配的区还是可以插入数据(10G,11G),知道空间分配为止。