1、UNLIMITED TABLESPACE 的含义
一个用户如果被赋予这个权限,那就获得了在所有数据表空间上不限制空间使用的权限。这个权限包含在RESOURCE这个ROLE中。
2、实验
(1)创建测试tablespace
- SQL> show user
- USER is "SYS"
-
- SQL> create tablespace tbs1
- 2 datafile '/oradat/TRANS/tbs1_01.dbf' size 100M
- 3 logging
- 4 extent management local
- 5 segment space management auto;
-
- Tablespace created.
-
- SQL> create tablespace tbs2
- 2 datafile '/oradat/TRANS/tbs2_01.dbf' size 100M
- 3 logging
- 4 extent management local
- 5 segment space management auto;
-
- Tablespace created.
- SQL> create user mth identified by mth default tablespace tbs1 temporary tablespace TEMP;
-
- User created.
-
SQL> grant connect,resource to mth;
Grant succeeded.
- SQL> conn mth/mth
- Connected.
- SQL> select * from user_role_privs;
-
- USERNAME GRANTED_ROLE ADM DEF OS_
- --------------- ------------ --- --- ---
- MTH CONNECT NO YES NO
- MTH RESOURCE NO YES NO
- SQL> select privilege from user_sys_privs;
-
- PRIVILEGE
- ----------------------------------------
- UNLIMITED TABLESPACE
-
- SQL> conn / as sysdba
- Connected.
- SQL> revoke resource from mth;
-
- Revoke succeeded.
-
- SQL> conn mth/mth
- Connected.
- SQL> select * from user_role_privs;
-
- USERNAME GRANTED_ROLE ADM DEF OS_
- --------- ------------ --- --- ---
-
- MTH CONNECT NO YES NO
-
- SQL> select privilege from user_sys_privs;
-
- no rows selected
可以看到,UNLIMITED TABLESPACE是包含在在resource中的。
- SQL> conn / as sysdba
- Connected.
- SQL> grant resource to mth;
-
- Grant succeeded.
(3)建立测试表,插入测试数据
- SQL> conn mth/mth
- Connected.
- SQL> create table t1(id number);
-
- Table created.
-
- SQL> insert into t1 values(1);
-
- 1 row created.
-
- SQL> commit;
-
- Commit complete.
-
- SQL> create table t2(id number) tablespace tbs2;
-
- Table created.
-
- SQL> insert into t2 values(2);
-
- 1 row created.
-
- SQL> commit;
-
- Commit complete.
(4)收回UNLIMITED TABLESPACE权限,再做测试。
- SQL> conn / as sysdba
- Connected.
- SQL> revoke unlimited tablespace from mth;
-
- Revoke succeeded.
-
- SQL> conn mth/mth;
- Connected.
- SQL> create table t3(id number);
-
- Table created.
-
- SQL> insert into t3 values(3);
- insert into t3 values(3)
- *
- ERROR at line 1:
- ORA-01950: no privileges on tablespace 'TBS1'
-
- SQL> create table t4(id number) tablespace tbs2;
-
- Table created.
-
- SQL> insert into t4 values(4);
- insert into t4 values(4)
- *
- ERROR at line 1:
- ORA-01950: no privileges on tablespace 'TBS2'
延迟段:默认将表、索引、LOB的物理空间分配推迟到第一条记录插入表中时。即有实际的数据插入表中时,再为每个对象按照定义的空间大小,为其分配空间 。此时,才能在相关视图中看见该表的相关存储信息。
- SQL> select * from v$version where rownum<2;
-
- BANNER
- --------------------------------------------------------------------------------
-
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
当DEFERRED_SEGMENT_CREATION=TRUE时,启用延迟段特性;反之,不启用。默认的,数据库启用该参数。
- SQL> show parameter defer
-
- NAME TYPE VALUE
- ---------------------------- ----------- ----------------
-
- deferred_segment_creation boolean TRUE
-
- SQL> alter system set deferred_segment_creation=false;
-
- System altered.
-
- SQL> show parameter defer
-
- NAME TYPE VALUE
- --------------------------- ----------- ----------------
-
- deferred_segment_creation boolean FALSE
- SQL> alter system set deferred_segment_creation=false;
如果只想限制某个用户使用该特性,就需要在会话层进行禁用。
(2)会话层禁用
- SQL> alter session set deferred_segment_creation=false;
-
- System altered.
-
- SQL> show parameter defer
-
- NAME TYPE VALUE
- --------------------------- ----------- ----------------
-
- deferred_segment_creation boolean FALSE
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30776559/viewspace-2121398/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30776559/viewspace-2121398/