UNLIMITED TABLESPACE 学习
来源:互联网 发布:部落冲突人王升级数据 编辑:程序博客网 时间:2024/06/14 03:41
1 UNLIMITED TABLESPACE
是指 用户对对表空间的空间资源的使用权限。并不是指 用户是否有权限在在某个表空间中创建表的权限。
该权限 在Oracle 10g 和 之后版本之间 有一些变化。
具体见:
具体 参考
When Revoking Unlimited Tablespace SystemPrivilege Tablespace Quota Also Removed (文档 ID 1169515.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=510858076816598&id=1169515.1&displayIndex=1&_afrWindowMode=0&_adf.ctrl-state=te7hcintu_273
2 /****脚本部分****/
初始化环境
create tablespace A1datafile '+ODATA' size 10M;
create tablespace A2datafile '+ODATA' size 10M;
create user A1identified by oracle default tablespace A1;
create user A2identified by oracle default tablespace A2;
grant connect,resourceto A1;
grant connect,resourceto A2;
create tablespace A1datafile '+DATA' size 10M;
create tablespace A2datafile '+DATA' size 10M;
create user A1identified by oracle default tablespace A1;
create user A2identified by oracle default tablespace A2;
grant connect,resourceto A1;
grant connect,resourceto A2;
查看权限
select grantee,privilegefrom dba_sys_privs where grantee='RESOURCE';
select grantee,privilegefrom dba_sys_privs where grantee='CONNECT';
select privilege fromdba_sys_privs where grantee='A1'
union
select privilege fromdba_sys_privs where grantee in (select granted_role from dba_role_privs wheregrantee='A1' );
select privilege fromdba_sys_privs where grantee='A2'
union
select privilege fromdba_sys_privs where grantee in (select granted_role from dba_role_privs wheregrantee='A2' );
创建object
conn A1/oracle
create table test1 (idvarchar2(3));
create table test2 (idvarchar2(5)) TABLESPACE A2;
create table test3 (idvarchar2(5)) TABLESPACE A2;
create table test4 (idvarchar2(5)) TABLESPACE A2;
插入数据
insert into test1 values(123);
insert into test2 values(123);
insert into test3 values(123);
insert into test4 values(123);
删除表
drop table test1;
drop table test2;
收回权限
revoke UNLIMITEDTABLESPACE from A1;
revoke UNLIMITEDTABLESPACE from A2;
quota unlimited权限
alter user A1 quotaunlimited on A1;
3 /****实验步骤****/
3.1 =======oracle 10G R2 环境中测试=======
SQL> select name fromv$datafile;
NAME
--------------------------------------------------------------------------------
+ODATA/racdb/datafile/system.256.850643109
+ODATA/racdb/datafile/undotbs1.258.850643109
+ODATA/racdb/datafile/sysaux.257.850643109
+ODATA/racdb/datafile/users.259.850643109
+ODATA/racdb/datafile/example.264.850643291
+ODATA/racdb/datafile/undotbs2.265.850643447
6 rows selected.
SQL> create tablespaceA1 datafile '+ODATA' size 10M;
create tablespace A2datafile '+ODATA' size 10M;
create user A1 identifiedby oracle default tablespace A1;
create user A2 identifiedby oracle default tablespace A2;
grant connect,resource toA1;
grant connect,resource toA2;
Tablespace created.
SQL>
Tablespace created.
SQL>
User created.
SQL>
User created.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
SQL>
SQL>
SQL> selectgrantee,privilege from dba_sys_privs where grantee='RESOURCE' order byprivilege;
GRANTEE PRIVILEGE
----------------------------------------------------------------------
RESOURCE CREATE CLUSTER
RESOURCE CREATE INDEXTYPE
RESOURCE CREATE OPERATOR
RESOURCE CREATE PROCEDURE
RESOURCE CREATE SEQUENCE
RESOURCE CREATE TABLE
RESOURCE CREATE TRIGGER
RESOURCE CREATE TYPE
8 rows selected.
SQL> selectgrantee,privilege from dba_sys_privs where grantee='CONNECT';
GRANTEE PRIVILEGE
----------------------------------------------------------------------
CONNECT CREATE SESSION
SQL> select privilegefrom dba_sys_privs where grantee='A1'
union
select privilege fromdba_sys_privs where grantee in (select granted_role from dba_role_privs wheregrantee='A1' ); 2 3
PRIVILEGE
----------------------------------------
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
UNLIMITED TABLESPACE
10 rows selected.
SQL> select privilegefrom dba_sys_privs where grantee='A2'
union
select privilege fromdba_sys_privs where grantee in (select granted_role from dba_role_privs wheregrantee='A2' ); 2 3
PRIVILEGE
----------------------------------------
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
UNLIMITED TABLESPACE
10 rows selected.
SQL> conn A1/oracle
Connected.
SQL> create table test1(id varchar2(3));
Table created.
SQL> selecttable_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------------------------------------
TEST1 A1
SQL> create table test2(id varchar2(5)) TABLESPACE A2;
Table created.
SQL> selecttable_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------------------------------------
TEST1 A1
TEST2 A2
------至此,创建了两个表 一个在默认表空间,一个在非默认表空间。
insert 数据:
SQL> insert into test1 values(123);
1 row created.
SQL> insert into test2 values(123);
1 row created.
------可以插入数据。
下面 我们收回UNLIMITEDTABLESPACE 权限,再继续进行测试:
SQL> drop table test1;
Table dropped.
SQL> drop table test2;
Table dropped.
[oracle@node1 ~]$ sqlplus/ as sysdba
SQL*Plus: Release10.2.0.4.0 - Production on Fri Dec 18 10:50:24 2015
Copyright (c) 1982, 2007,Oracle. All Rights Reserved.
Connected to:
Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning,Real Application Clusters, OLAP, Data Mining
and Real ApplicationTesting options
SQL> revoke UNLIMITEDTABLESPACE from A1;
revoke UNLIMITEDTABLESPACE from A2;
Revoke succeeded.
SQL>
2 ;
Revoke succeeded.
SQL>
SQL> select privilegefrom dba_sys_privs where grantee='A1'
union
select privilege fromdba_sys_privs where grantee in (select granted_role from dba_role_privs wheregrantee='A1' ); 2 3
PRIVILEGE
----------------------------------------
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
9 rows selected.
SQL> select privilegefrom dba_sys_privs where grantee='A2'
union
select privilege fromdba_sys_privs where grantee in (select granted_role from dba_role_privs wheregrantee='A2' ); 2 3
PRIVILEGE
----------------------------------------
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
9 rows selected. ---已经回收了 unlimited tablespace的权限
SQL> conn A1/oracle
Connected.
SQL> create table test1(id varchar2(3));
create table test1 (idvarchar2(3))
*
ERROR at line 1:
ORA-01536: space quotaexceeded for tablespace 'A1'
SQL> create table test2(id varchar2(5)) TABLESPACE A2;
create table test2 (idvarchar2(5)) TABLESPACE A2
*
ERROR at line 1:
ORA-01536: space quotaexceeded for tablespace 'A2'
---发现 无法在本地表空间中创建表,这是因为 在回收unlimited tablespace的权限时候,一并将用户对表空间的TablespaceQuota也回收了。
现在赋予A1用户 quotaunlimited on default tablespace的权限
alter user A1 quotaunlimited on A1;
之后 再继续创建表
SQL> alter user A1quota unlimited on A1;
User altered.
SQL> create table test1(id varchar2(3));
Table created.
SQL> insert into test1 values(123);
1 row created.
SQL> create table test2 (id varchar2(5))TABLESPACE A2;
create table test2 (id varchar2(5)) TABLESPACEA2
*
ERROR at line 1:
ORA-01536: space quotaexceeded for tablespace 'A2'
--- 可以在A1表空间中创建并且插入数据,不能再A2表空间中创建。
但是已经存在的表,是可以继续插入表的,只是不能创建新的表。
1、先创建表
SQL> conn a1/oracle
Connected.
SQL> create table test1(id varchar2(3));
Table created.
SQL> create table test2(id varchar2(5)) TABLESPACE A2;
Table created.
2、回收权限
SQL> revoke UNLIMITEDTABLESPACE from A1;
Revoke succeeded.
SQL> revoke UNLIMITEDTABLESPACE from A2;
Revoke succeeded.
3、插入新数据及创建新表
SQL> insert into test1 values(123);
1 row created.
SQL> exit
[oracle@node1 ~]$ sqlplus/ as sysdba
SQL*Plus: Release10.2.0.4.0 - Production on Fri Dec 18 15:20:34 2015
Copyright (c) 1982, 2007,Oracle. All Rights Reserved.
Connected to:
Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning,Real Application Clusters, OLAP, Data Mining
and Real ApplicationTesting options
SQL> conn a1/oracle
Connected.
SQL> insert into test1 values(123);
1 row created.
SQL> insert into test2 values(123);
1 row created.
SQL> create table test3(id varchar2(5));
create table test3 (idvarchar2(5))
*
ERROR at line 1:
ORA-01536: space quotaexceeded for tablespace 'A1'
SQL> create table test4(id varchar2(5)) TABLESPACE A2;
create table test4 (idvarchar2(5)) TABLESPACE A2
*
ERROR at line 1:
ORA-01536: space quotaexceeded for tablespace 'A2'
-----对于已经存在的表 是依然可以继续插入数据的。 为了保证 A1 用户的所有表都在A1 表空间中,建议将表test2 移动到A1中:
1、先给予a1 用户对a1 表空间的 qutoaunlimited
SQL> alter user A1quota unlimited on A1;
User altered.
SQL> alter table test2move tablespace a1;
Table altered.
SQL> selecttable_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------------------------------------
TEST1 A1
TEST2 A1
3.1.1 注意
如果 有索引也存在于A2 表空间中,需要使用 alterindex rebulid tablespace 命令重建索引。
参考命令:
alter index PK_PF_ACC rebuild tablespace ACC;
3.2 =======oracle 11G R2 11.2.0.4.7环境中测试=======
SQL> create tablespaceA1 datafile '+DATA' size 10M;
create tablespace A2datafile '+DATA' size 10M;
create user A1 identifiedby oracle default tablespace A1;
create user A2 identifiedby oracle default tablespace A2;
grant connect,resource toA1;
grant connect,resource toA2;
Tablespace created.
SQL>
Tablespace created.
SQL>
User created.
SQL>
User created.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL> selectgrantee,privilege from dba_sys_privs where grantee='RESOURCE';
GRANTEE PRIVILEGE
----------------------------------------------------------------------
RESOURCE CREATE TRIGGER
RESOURCE CREATE SEQUENCE
RESOURCE CREATE TYPE
RESOURCE CREATE PROCEDURE
RESOURCE CREATE CLUSTER
RESOURCE CREATE OPERATOR
RESOURCE CREATE INDEXTYPE
RESOURCE CREATE TABLE
8 rows selected.
SQL> selectgrantee,privilege from dba_sys_privs where grantee='CONNECT';
GRANTEE PRIVILEGE
----------------------------------------------------------------------
CONNECT CREATE SESSION
SQL> select privilegefrom dba_sys_privs where grantee='A1'
union
select privilege fromdba_sys_privs where grantee in (select granted_role from dba_role_privs wheregrantee='A1' ); 2 3
PRIVILEGE
----------------------------------------
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
UNLIMITED TABLESPACE
10 rows selected.
SQL> select privilegefrom dba_sys_privs where grantee='A2'
union
select privilege fromdba_sys_privs where grantee in (select granted_role from dba_role_privs wheregrantee='A2' ); 2 3
PRIVILEGE
----------------------------------------
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
UNLIMITED TABLESPACE
10 rows selected.
SQL> conn A1/oracle
Connected.
SQL> create table test1(id varchar2(3));
Table created.
SQL> create table test2 (id varchar2(5))TABLESPACE A2;
Table created.
SQL> selecttable_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------------------------------------
TEST1 A1
TEST2 A2
SQL> insert into test1values (123);
1 row created.
SQL> insert into test2values (123);
1 row created.
收回UNLIMITED TABLESPACE 权限,再继续进行测试:
SQL> drop table test1;
Table dropped.
SQL> drop table test2;
Table dropped.
SQL> revoke UNLIMITEDTABLESPACE from A1;
Revoke succeeded.
SQL> revoke UNLIMITEDTABLESPACE from A2;
Revoke succeeded.
SQL> select privilegefrom dba_sys_privs where grantee='A1'
union
select privilege fromdba_sys_privs where grantee in (select granted_role from dba_role_privs wheregrantee='A1' ); 2 3
PRIVILEGE
----------------------------------------
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
9 rows selected.
SQL> select privilegefrom dba_sys_privs where grantee='A2'
union
select privilege fromdba_sys_privs where grantee in (select granted_role from dba_role_privs wheregrantee='A2' ); 2 3
PRIVILEGE
----------------------------------------
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
9 rows selected.
SQL> conn A1/oracle
Connected.
SQL> create table test1(id varchar2(3));
Table created.
SQL>
SQL>
SQL> create table test2(id varchar2(5)) TABLESPACE A2;
Table created.
SQL> insert into test1values (123);
insert into test1 values(123)
*
ERROR at line 1:
ORA-01950: no privilegeson tablespace 'A1'
SQL> insert into test2values (123);
insert into test2 values(123)
*
ERROR at line 1:
ORA-01950: no privilegeson tablespace 'A2'
------可以发现,依然可以创建表,但是没有办法插入数据。
赋予 quota unlimited 权限后
SQL> alter user A1quota unlimited on A1;
User altered.
SQL> conn A1/oracle
Connected.
SQL> insert into test1values (123);
1 row created.
---可以插入数据。
SQL> insert into test2values (123);
insert into test2 values(123)
*
ERROR at line 1:
ORA-01950: no privilegeson tablespace 'A2'
---没有quotaunlimited on A2 之前,是不能插入数据到test2的。
SQL> alter user A1quota unlimited on A2;
User altered.
SQL> insert into test2 values (123);
1 row created.
---quota unlimited on A2 之后,立即可以插入数据到test2.
4 后期处理
drop user A1 cascade;
drop user A2 cascade;
drop tablespace A1 including contents and datafiles;
drop tablespace A2 including contents and datafiles;
5 总结
1、unlimited tablespace权限是用来限制用户对表空间中空间资源的使用的。
2、 revokeunlimited tablespace from user之后,在10g及以前的版本中,需要重新设置 quotaunlimited on默认表空间,才可以继续创建表,但是对于默认已经存在的表,可以继续插入数据。
在11g及之后版本中,revokeunlimited tablespace from user之后,用户可以继续创建表,但是不能插入数据到表中,之后重新设置 quotaunlimited on tablespace之后,才可以进入插入数据。
- UNLIMITED TABLESPACE 学习
- 有关UNLIMITED TABLESPACE权限
- 有关UNLIMITED TABLESPACE权限
- 有关UNLIMITED TABLESPACE权限
- 关于unlimited tablespace系统权限
- 系统权限UNLIMITED TABLESPACE为什么如此特殊?
- 系统权限UNLIMITED TABLESPACE为何此特殊
- 详解Oracle的unlimited tablespace系统权限
- 详解Oracle的unlimited tablespace系统权限
- Oracle的unlimited tablespace系统权限
- Oracle的unlimited tablespace系统权限
- 表空间配额和UNLIMITED TABLESPACE权限
- 表空间配额和UNLIMITED TABLESPACE权限
- 表空间配额和UNLIMITED TABLESPACE权限
- 详解Oracle的unlimited tablespace系统权限
- 10g中resource角色的隐含权限UNLIMITED TABLESPACE
- oracle表空间配额和unlimited tablespace权限
- resource角色隐式授权unlimited tablespace权限测试
- 关于javascript中的parentNode
- Aandroid在ViewPager中添加ListView
- iOS让你的app一直在后台活着(运行)
- HTML事件属性
- AE自定义工具时通过hook获取axMapControl
- UNLIMITED TABLESPACE 学习
- android bitmap和base64之间的转换
- 详解 $_SERVER 函数中QUERY_STRING和REQUEST_URI区别
- MVC,MVP 和 MVVM 的图示
- 修改debian的系统时间
- HDMI-CEC Control Service
- Android ViewPager + Fragment 替换 TabActivity
- 选择排序法
- Java虚拟机(JVM)参数配置说明