quota的日常管理

常见问题

ORA-01536:space   quota   exceeded   for   table   space   'CYYD' 
解决办法： 
alter   user   USERNAME   quota   100M   on   TABLESPACENAME;  
alter   user   USERNAME   quota   unlimited   on   TABLESPACENAME; 
grant   unlimited   tablespace   to   USERNAME; 
quota是为了限制用户对表空间的使用,比如你限制用户Guotu在tablespace  CYYD中的quota为10m，当用户Guotu在tablespace   CYYD中的数据量达到10m后，无论你的tablespace   CYYD中有多少空间，Guotu都无法再使用tablespace   CYYD了。
所以你需要： 
alter   user   aGuotu  quota   1000M   on   CYYD; 
alter   user   Guotu   quota   unlimited   on   CYYD; 
grant   unlimited   tablespace   to   Guotu.

 

可以使用下列语句来创建用户
CREATE USER jward
 IDENTIFIED BY password
 DEFAULT TABLESPACE data_ts
 QUOTA 100M ON test_ts
 QUOTA 500K ON data_ts
 TEMPORARY TABLESPACE temp_ts
 PROFILE clerk;

 

Listing All Tablespace Quotas
Use the DBA_TS_QUOTAS view to list all tablespace quotas specifically assigned to each user. For example: 
SELECT * FROM DBA_TS_QUOTAS;
 TABLESPACE    USERNAME    BYTES     MAX_BYTES    BLOCKS    MAX_BLOCKS
----------    ---------  --------   ----------   -------   ----------
USERS         JFEE              0       512000         0          250
USERS         DCRANNEY          0           -1         0           -1 
When specific quotas are assigned, the exact number is indicated in the MAX_BYTES column . This number is always a multiple of the database block size, so if you specify a tablespace quota that is not a multiple of the database block size, then it is rounded up accordingly. Unlimited quotas are indicated by -1. 
注意当对用户赋予resource角色时将同时赋予unlimited tablespace的系统权限。详情见下文

创建用户 

SQL> create user test_privs identified by test_privs default tablespace users;
User created.

SQL> select * from dba_sys_privs where GRANTEE='TEST_PRIVS';
no rows selected 
赋予resource角色 
SQL> grant resource to TEST_PRIVS;
Grant succeeded. 

查询resource角色所具有的系统权限

SQL> select * from dba_sys_privs where GRANTEE='RESOURCE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO
查看用户所具有的角色 
SQL> select * from dba_role_privs where GRANTEE='TEST_PRIVS';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
TEST_PRIVS                     RESOURCE                       NO  YES
查询用户所具有的系统权限 

SQL> select * from dba_sys_privs where GRANTEE='TEST_PRIVS';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
TEST_PRIVS                     UNLIMITED TABLESPACE                     NO 
可以看到，Oracle默认的把unlimited tablespace的系统权限赋予了用户

查询表空间

SQL> select TABLESPACE_NAME,USERNAME,BYTES,MAX_BYTES from dba_ts_quotas;

TABLESPACE_NAME                USERNAME        BYTES  MAX_BYTES
------------------------------ ---------- ---------- ----------
INDX                           HR              65536   10485760
SYSAUX                         OLAPSYS      16318464         -1
USERS                          HR             196608         -1
SYSAUX                         SYSMAN       54460416         -1
SYSAUX                         DMSYS          262144  209715200
TRANS                          TRANS               0   10485760
可以看到对于具有unlimited tablespace系统权限的用户，在dba_ts_quota上没有体现。

这里补充说一句，一般创建用户时，如果没有特殊需求只要将resource和connect角色赋予用户即可。
SQL> select * from dba_sys_privs where GRANTEE= 'CONNECT';
ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO 

SQL> grant resource,connect to test_privs;
Grant succeeded.