创建用户(不明确指定quota)后，oracle给它的默认quota是多大呢

===============================================================================
1.问题
===============================================================================
创建用户(不明确指定quota)后，oracle给它的默认quota是多大呢？
为什么开始可写数据，但是到后来写数据报ora-01536错误？
-------------------------------------------------------------------------------

01536, 00000, "space quota exceeded for tablespace '%s'"
// *Cause:  The space quota for the segment owner in the tablespace has
//          been exhausted and the operation attempted the creation of a
//          new segment extent in the tablespace.
// *Action: Either drop unnecessary objects in the tablespace to reclaim
//          space or have a privileged user increase the quota on this
//          tablespace for the segment owner.

===============================================================================
2.环境
===============================================================================

DBSVR:
 RHEL 5.8 64bit
 11.1.0.6.0 64bit

===============================================================================
3.排查验证
===============================================================================
==>创建表空间和用户
-------------------------------------------------------------------------------

create tablespace quotatest
 datafile '/u01/app/oracle/oradata/db11g/quotatest.dbf' size 100m;

create user quotatest identified by quotatest
 default tablespace quotatest;

-------------------------------------------------------------------------------
==>用户所拥有权限、配额与授予角色测试
-------------------------------------------------------------------------------

select * from dba_ts_quotas where username = 'QUOTATEST';
select * from dba_sys_privs where grantee = 'QUOTATEST';
select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';

no rows selected
SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';
no rows selected

grant connect to quotatest;

select * from dba_ts_quotas where username = 'QUOTATEST';
select * from dba_sys_privs where grantee = 'QUOTATEST';
select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> grant connect to quotatest;
Grant succeeded.
SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
QUOTATEST                      CONNECT                        NO  YES

conn quotatest/quotatest
select * from user_ts_quotas;
select * from user_sys_privs;
select * from user_role_privs;

QUOTATEST@db11g> select * from user_ts_quotas;
no rows selected
QUOTATEST@db11g> select * from user_sys_privs;
no rows selected
QUOTATEST@db11g> select * from user_role_privs;
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
QUOTATEST                      CONNECT                        NO  YES NO

grant resource to quotatest;
select * from dba_ts_quotas where username = 'QUOTATEST';
select * from dba_sys_privs where grantee = 'QUOTATEST';
select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> grant resource to quotatest;
Grant succeeded.
SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
QUOTATEST                      UNLIMITED TABLESPACE                     NO
SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
QUOTATEST                      RESOURCE                       NO  YES
QUOTATEST                      CONNECT                        NO  YES

select * from user_ts_quotas;
select * from user_sys_privs;
select * from user_role_privs;

QUOTATEST@db11g> select * from user_ts_quotas;
no rows selected
QUOTATEST@db11g> select * from user_sys_privs;
USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
QUOTATEST                      UNLIMITED TABLESPACE                     NO
QUOTATEST@db11g> select * from user_role_privs;
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
QUOTATEST                      CONNECT                        NO  YES NO
QUOTATEST                      RESOURCE                       NO  YES NO

用户有 unlimited tablespace 权限，未明确设置 quota, quota = 0

-------------------------------------------------------------------------------
==>测试表空间配额
-------------------------------------------------------------------------------
用户有 unlimited tablespace 权限，未明确设置 quota

create table t1 as select * from all_objects;

select * from user_ts_quotas;
select * from user_sys_privs;
select * from user_role_privs;

QUOTATEST@db11g> create table t1 as select * from all_objects;
Table created.
QUOTATEST@db11g> select * from user_ts_quotas;
TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
QUOTATEST                         7340032          0        896          0 NO
QUOTATEST@db11g> select * from user_sys_privs;
USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
QUOTATEST                      UNLIMITED TABLESPACE                     NO
QUOTATEST@db11g> select * from user_role_privs;
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
QUOTATEST                      CONNECT                        NO  YES NO
QUOTATEST                      RESOURCE                       NO  YES NO

select * from dba_ts_quotas where username = 'QUOTATEST';
select * from dba_sys_privs where grantee = 'QUOTATEST';
select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
QUOTATEST                      UNLIMITED TABLESPACE                     NO
SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
QUOTATEST                      RESOURCE                       NO  YES
QUOTATEST                      CONNECT                        NO  YES

在插入数据后，dba_ts_quotas无记录，user_ts_quotas有记录。quota为0


用户有 unlimited tablespace 权限，设置 quota 为 0

alter user quotatest quota 0 on quotatest;

select * from dba_ts_quotas where username = 'QUOTATEST';
select * from dba_sys_privs where grantee = 'QUOTATEST';
select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> alter user quotatest quota 0 on quotatest;
User altered.
SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
QUOTATEST                      UNLIMITED TABLESPACE                     NO
SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
QUOTATEST                      RESOURCE                       NO  YES
QUOTATEST                      CONNECT                        NO  YES

select * from user_ts_quotas;
select * from user_sys_privs;
select * from user_role_privs;

QUOTATEST@db11g> select * from user_ts_quotas;
TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
QUOTATEST                         7340032          0        896          0 NO
QUOTATEST@db11g> select * from user_sys_privs;
USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
QUOTATEST                      UNLIMITED TABLESPACE                     NO
QUOTATEST@db11g> select * from user_role_privs;
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
QUOTATEST                      CONNECT                        NO  YES NO
QUOTATEST                      RESOURCE                       NO  YES NO

create table t2 as select * from all_objects;

select * from user_ts_quotas;
select * from user_sys_privs;
select * from user_role_privs;

QUOTATEST@db11g> create table t2 as select * from all_objects;
Table created.
QUOTATEST@db11g> select * from user_ts_quotas;
TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
QUOTATEST                        14680064          0       1792          0 NO
QUOTATEST@db11g> select * from user_sys_privs;
USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
QUOTATEST                      UNLIMITED TABLESPACE                     NO
QUOTATEST@db11g> select * from user_role_privs;
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
QUOTATEST                      CONNECT                        NO  YES NO
QUOTATEST                      RESOURCE                       NO  YES NO

select * from dba_ts_quotas where username = 'QUOTATEST';
select * from dba_sys_privs where grantee = 'QUOTATEST';
select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
QUOTATEST                      UNLIMITED TABLESPACE                     NO
SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
QUOTATEST                      RESOURCE                       NO  YES
QUOTATEST                      CONNECT                        NO  YES

用户吊销了 unlimited tablespace 权限，设置 quota 为 0

revoke unlimited tablespace from quotatest;

select * from dba_ts_quotas where username = 'QUOTATEST';
select * from dba_sys_privs where grantee = 'QUOTATEST';
select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> revoke unlimited tablespace from quotatest;
Revoke succeeded.
SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
QUOTATEST                      RESOURCE                       NO  YES
QUOTATEST                      CONNECT                        NO  YES

select * from user_ts_quotas;
select * from user_sys_privs;
select * from user_role_privs;

QUOTATEST@db11g> select * from user_ts_quotas;
TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
QUOTATEST                        14680064          0       1792          0 NO
QUOTATEST@db11g> select * from user_sys_privs;
no rows selected
QUOTATEST@db11g> select * from user_role_privs;
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
QUOTATEST                      CONNECT                        NO  YES NO
QUOTATEST                      RESOURCE                       NO  YES NO

create table t3 as select * from all_objects;

select * from user_ts_quotas;
select * from user_sys_privs;
select * from user_role_privs;

QUOTATEST@db11g> create table t3 as select * from all_objects;
create table t3 as select * from all_objects
                                 *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'QUOTATEST'
此时quota起作用了，报1536错
QUOTATEST@db11g> select * from user_ts_quotas;
TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
QUOTATEST                        14680064          0       1792          0 NO
QUOTATEST@db11g> select * from user_sys_privs;
no rows selected
QUOTATEST@db11g> select * from user_role_privs;
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
QUOTATEST                      CONNECT                        NO  YES NO
QUOTATEST                      RESOURCE                       NO  YES NO

select * from dba_ts_quotas where username = 'QUOTATEST';
select * from dba_sys_privs where grantee = 'QUOTATEST';
select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
QUOTATEST                      RESOURCE                       NO  YES
QUOTATEST                      CONNECT                        NO  YES

用户吊销了 unlimited tablespace 权限，设置 quota 为 -1

alter user quotatest quota -1 on quotatest;

SYS@db11g> alter user quotatest quota -1 on quotatest;
alter user quotatest quota -1 on quotatest
                           *
ERROR at line 1:
ORA-02187: invalid quota specification

不能手工设置quota为 -1


用户吊销了 unlimited tablespace 权限，设置 quota 为 20m

alter user quotatest quota 20m on quotatest;

select * from dba_ts_quotas where username = 'QUOTATEST';
select * from dba_sys_privs where grantee = 'QUOTATEST';
select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> alter user quotatest quota 20m on quotatest;
User altered.
SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';
TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---
QUOTATEST                      QUOTATEST                        14680064   20971520       1792         2560 NO
SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
QUOTATEST                      RESOURCE                       NO  YES
QUOTATEST                      CONNECT                        NO  YES

quota设置为非0值，可在dba_ts_quotas中查到记录

select * from user_ts_quotas;
select * from user_sys_privs;
select * from user_role_privs;

QUOTATEST@db11g> select * from user_ts_quotas;
TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
QUOTATEST                        14680064   20971520       1792       2560 NO
QUOTATEST@db11g> select * from user_sys_privs;
no rows selected
QUOTATEST@db11g> select * from user_role_privs;
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
QUOTATEST                      CONNECT                        NO  YES NO
QUOTATEST                      RESOURCE                       NO  YES NO

create table t4 as select * from all_objects;

select * from user_ts_quotas;
select * from user_sys_privs;
select * from user_role_privs;

QUOTATEST@db11g> create table t4 as select * from all_objects;
create table t4 as select * from all_objects
                                 *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'QUOTATEST'
QUOTATEST@db11g> select * from user_ts_quotas;
TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
QUOTATEST                        14680064   20971520       1792       2560 NO
QUOTATEST@db11g> select * from user_sys_privs;
no rows selected
QUOTATEST@db11g> select * from user_role_privs;
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
QUOTATEST                      CONNECT                        NO  YES NO
QUOTATEST                      RESOURCE                       NO  YES NO

select * from dba_ts_quotas where username = 'QUOTATEST';
select * from dba_sys_privs where grantee = 'QUOTATEST';
select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';
TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---
QUOTATEST                      QUOTATEST                        14680064   20971520       1792         2560 NO
SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
QUOTATEST                      RESOURCE                       NO  YES
QUOTATEST                      CONNECT                        NO  YES

用户重新授予特定表空间 unlimited quota 权限

alter user quotatest quota unlimited on quotatest;

select * from dba_ts_quotas where username = 'QUOTATEST';
select * from dba_sys_privs where grantee = 'QUOTATEST';
select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> alter user quotatest quota unlimited on quotatest;
User altered.
SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';
TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---
QUOTATEST                      QUOTATEST                        14680064         -1       1792           -1 NO
SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
QUOTATEST                      RESOURCE                       NO  YES
QUOTATEST                      CONNECT                        NO  YES

select * from user_ts_quotas;
select * from user_sys_privs;
select * from user_role_privs;

QUOTATEST@db11g> select * from user_ts_quotas;
TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
QUOTATEST                        14680064         -1       1792         -1 NO
QUOTATEST@db11g> select * from user_sys_privs;
no rows selected
QUOTATEST@db11g> select * from user_role_privs;
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
QUOTATEST                      CONNECT                        NO  YES NO
QUOTATEST                      RESOURCE                       NO  YES NO

授予unlimited 后，quota值为-1

create table t5 as select * from all_objects;

select * from user_ts_quotas;
select * from user_sys_privs;
select * from user_role_privs;

QUOTATEST@db11g> create table t5 as select * from all_objects;
Table created.
QUOTATEST@db11g> select * from user_ts_quotas;
TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
QUOTATEST                        22020096         -1       2688         -1 NO
QUOTATEST@db11g> select * from user_sys_privs;
no rows selected
QUOTATEST@db11g> select * from user_role_privs;
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
QUOTATEST                      CONNECT                        NO  YES NO
QUOTATEST                      RESOURCE                       NO  YES NO

select * from dba_ts_quotas where username = 'QUOTATEST';
select * from dba_sys_privs where grantee = 'QUOTATEST';
select * from dba_role_privs where grantee = 'QUOTATEST';

SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';
TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---
QUOTATEST                      QUOTATEST                        22020096         -1       2688           -1 NO
SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
QUOTATEST                      RESOURCE                       NO  YES
QUOTATEST                      CONNECT                        NO  YES

-------------------------------------------------------------------------------
==>清理
-------------------------------------------------------------------------------

drop user quotatest cascade;
drop tablespace quotatest including contents and datafiles drop quota;
select * from dba_ts_quotas where username = 'QUOTATEST';
select * from dba_sys_privs where grantee = 'QUOTATEST';
select * from dba_role_privs where grantee = 'QUOTATEST';

User dropped.
SYS@db11g>
Tablespace dropped.
SYS@db11g> SYS@db11g> select * from dba_ts_quotas where username = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_sys_privs where grantee = 'QUOTATEST';
no rows selected
SYS@db11g> select * from dba_role_privs where grantee = 'QUOTATEST';
no rows selected

===============================================================================
4.小结
===============================================================================
创建用户(不明确指定quota)后，oracle给它的默认quota是多大呢？
 是0。

为什么开始可写数据，但是到后来写数据报ora-01536错误？
 用户开始可能有unlimited tablespace权限，而到后来吊销了该权限。

当用户拥有 unlimited tablespace权限时，quota限制不起作用。
而授予resource角色，会同时隐含授予unlimited tablespace权限，
吊销resource时，会同时吊销unlimited tablespace权限。
(12.1.0.1起，已取消此隐含授权。)

===============================================================================
5.参考信息
===============================================================================
侯圣文
【Quota】获取及调整用户表空间配额方法
http://blog.itpub.net/519536/viewspace-664347/

shaochenshuo
oracle表空间配额（quota）
http://blog.51cto.com/589985/1336658

Ask TOM
User Quota


Ask TOM
Resource Role and "Unlimited Tablespace" privilege


docs.oracle.com
Database Security Guide