Unlimited Tablespace 权限 及 “延迟段”特性

1、UNLIMITED TABLESPACE 的含义

一个用户如果被赋予这个权限，那就获得了在所有数据表空间上不限制空间使用的权限。这个权限包含在RESOURCE这个ROLE中。

2、实验

（1）创建测试tablespace

1. SQL> show user
   
2. USER is "SYS"
3. 
   
4. SQL> create tablespace tbs1
   
5.   2 datafile '/oradat/TRANS/tbs1_01.dbf' size 100M
   
6.   3 logging
   
7.   4 extent management local
   
8.   5 segment space management auto;
   
9. 
   
10. Tablespace created.
    
11. 
    
12. SQL> create tablespace tbs2
    
13.   2 datafile '/oradat/TRANS/tbs2_01.dbf' size 100M
    
14.   3 logging
    
15.   4 extent management local
    
16.   5 segment space management auto;
    
17. 
    
18. Tablespace created.

（2）创建测试用户，授予基本角色

1. SQL> create user mth identified by mth default tablespace tbs1 temporary tablespace TEMP;
   
2. 
   
3. User created.

4. SQL> grant connect,resource to mth;

   Grant succeeded.

1. SQL> conn mth/mth
   
2. Connected.
   
3. SQL> select * from user_role_privs;
   
4. 
   
5. USERNAME GRANTED_ROLE ADM DEF OS_
   
6. --------------- ------------ --- --- ---
   
7. MTH CONNECT NO YES NO
   
8. MTH RESOURCE NO YES NO
   
9. SQL> select privilege from user_sys_privs;
   
10. 
    
11. PRIVILEGE
    
12. ----------------------------------------
    
13. UNLIMITED TABLESPACE
    
14. 
    
15. SQL> conn / as sysdba
    
16. Connected.
    
17. SQL> revoke resource from mth;
    
18. 
    
19. Revoke succeeded.
    
20. 
    
21. SQL> conn mth/mth
    
22. Connected.
    
23. SQL> select * from user_role_privs;
    
24. 
    
25. USERNAME GRANTED_ROLE ADM DEF OS_
    
26. --------- ------------ --- --- ---
    
27. 
    
28. MTH CONNECT NO YES NO
    
29. 
    
30. SQL> select privilege from user_sys_privs;
    
31. 
    
32. no rows selected

可以看到，UNLIMITED TABLESPACE是包含在在resource中的。

1. SQL> conn / as sysdba
   
2. Connected.
   
3. SQL> grant resource to mth;
   
4. 
   
5. Grant succeeded.

(3)建立测试表,插入测试数据

1. SQL> conn mth/mth
   
2. Connected.
   
3. SQL> create table t1(id number);
   
4. 
   
5. Table created.
   
6. 
   
7. SQL> insert into t1 values(1);
   
8. 
   
9. 1 row created.
   
10. 
    
11. SQL> commit;
    
12. 
    
13. Commit complete.
    
14. 
    
15. SQL> create table t2(id number) tablespace tbs2;
    
16. 
    
17. Table created.
    
18. 
    
19. SQL> insert into t2 values(2);
    
20. 
    
21. 1 row created.
    
22. 
    
23. SQL> commit;
    
24. 
    
25. Commit complete.

可以看到，在tbs1以及tbs2上该用户都有创建表和插入数据的权限。这就是UNLIMITED TABLESPACE的作用。

(4)收回UNLIMITED TABLESPACE权限，再做测试。

1. SQL> conn / as sysdba
   
2. Connected.
   
3. SQL> revoke unlimited tablespace from mth;
   
4. 
   
5. Revoke succeeded.
   
6. 
   
7. SQL> conn mth/mth;
   
8. Connected.
   
9. SQL> create table t3(id number);
   
10. 
    
11. Table created.
    
12. 
    
13. SQL> insert into t3 values(3);
    
14. insert into t3 values(3)
    
15.             *
    
16. ERROR at line 1:
    
17. ORA-01950: no privileges on tablespace 'TBS1'
    
18. 
    
19. SQL> create table t4(id number) tablespace tbs2;
    
20. 
    
21. Table created.
    
22. 
    
23. SQL> insert into t4 values(4);
    
24. insert into t4 values(4)
    
25.             *
    
26. ERROR at line 1:
    
27. ORA-01950: no privileges on tablespace 'TBS2'

可以看到，收回UNLIMITED TABLESPACE后，不论是用户默认的tablespace还是其他tablespace，该用户都可以创建表，但是不能插入数据。这跟11gR2的新特性“延迟段”有关。下面介绍“延迟段”特性。

延迟段：默认将表、索引、LOB的物理空间分配推迟到第一条记录插入表中时。即有实际的数据插入表中时，再为每个对象按照定义的空间大小，为其分配空间 。此时，才能在相关视图中看见该表的相关存储信息。

1. SQL> select * from v$version where rownum<2;
   
2. 
   
3. BANNER
   
4. --------------------------------------------------------------------------------
   
5. 
   
6. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Oracle控制此特性的参数为：DEFERRED_SEGMENT_CREATION
当DEFERRED_SEGMENT_CREATION=TRUE时，启用延迟段特性；反之，不启用。默认的，数据库启用该参数。

1. SQL> show parameter defer
   
2. 
   
3. NAME                         TYPE        VALUE
   
4. ---------------------------- ----------- ----------------
   
5. 
   
6. deferred_segment_creation    boolean     TRUE

(1)全局禁用

1. 
   
   1. SQL> alter system set deferred_segment_creation=false;
      
   2. 
      
   3. System altered.
      
   4. 
      
   5. SQL> show parameter defer
      
   6. 
      
   7. NAME                        TYPE        VALUE
      
   8. --------------------------- ----------- ----------------
      
   9. 
      
   10. deferred_segment_creation   boolean     FALSE
   
   

这样，数据库中任何一个用户创建表时，延迟段特性都将被关闭。

如果只想限制某个用户使用该特性，就需要在会话层进行禁用。

(2)会话层禁用

1. SQL> alter session set deferred_segment_creation=false;
   
2. 
   
3. System altered.
   
4. 
   
5. SQL> show parameter defer
   
6. 
   
7. NAME                        TYPE        VALUE
   
8. --------------------------- ----------- ----------------
   
9. 
   
10. deferred_segment_creation   boolean     FALSE

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30776559/viewspace-2121398/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30776559/viewspace-2121398/