从ORA-01950报错我们能了解的知识 – Java之音
Loading
0

从ORA-01950报错我们能了解的知识

从ORA-01950报错我们能了解的知识


开发同学提了一个问题,说有个性能测试的数据库,正在准备数据,使用如下批量操作的语句,报错ORA-01950,无法执行插入,

(注:用户、表、索引等对象,均用测试的名称代替),

SQL> insert all into TEST

(...)

values (...)

into TEST

(...)

values (...)

into TEST

(...)

values (...)

select 1 from dual;

*

ERROR at line 4:

ORA-01950: no privileges on tablespace 'USERS'


首先看了下,当前用户的信息,发现用户默认的表空间,TBS_DAT,不是USERS(系统默认表空间)。

SQL> select username, default_tablespace from dba_users where username='USER_A';

USERNAME   DEFAULT_TABLESPACE
--------------- -----------------------------------
USER_A         TBS_DAT


现在的问题就是,

1. 为什么用户USER_A默认表空间是TBS_DAT,但报的是USERS表空间无权限?

2. 为什么会报USERS表空间无权限?



问题一:为什么用户USER_A默认表空间是TBS_DAT,但报的是USERS表空间无权限


我们看下这张TEST表所属表空间,是USERS表空间,不是TEST表空间,因此执行插入的数据,是向这个USERS表空间操作,而不是默认的表空间,

SQL> select table_name, tablespace_name from user_tables;

TABLE_NAME   TABLESPACE
----------------- --------------------
TEST                 USERS




问题二:为什么会报USERS表空间无权限


我们看一下ORA-01950错误提示,没有权限分配extent区,解决方案是grant相应的系统权限,或者授予用户表空间资源,

SQL> !oerr ora 1950
01950, 00000, "no privileges on tablespace '%s'"
// *Cause:  User does not have privileges to allocate an extent in the
//          specified tablespace.
// *Action: Grant the user the appropriate system privileges or grant the user
//          space resource on the tablespace.


看一下用户USER_A可用的配额,结果为空,说明无权使用USERS表空间,因此执行插入操作的时候,就会报错ORA-01950,

SQL> select tablespace_name, max_bytes from user_ts_quotas;
no rows selected




总结一下上面的问题:

1. TEST表并未存储于USER_A的默认表空间,而是存储于系统默认表空间。

2. USER_A用户对于表空间,没有使用权限。



解决方案


首先,为了标准化管理,需要将存储于USERS表空间的对象,放置USER_A的默认表空间中,但是执行的时候,报错ORA-01950,

SQL> alter table TEST move tablespace tbs_dat;

alter table TEST move tablespace tbs_dat;

*

ERROR at line 1:

ORA-01950: no privileges on tablespace 'TBS_DAT'


看一下用户USER_A系统权限,结果为空,

SQL> select privilege from user_sys_privs;

no rows selected


用户配额是0,说明用户USER_A,根本没有权限使用表空间,

SQL> select tablespace_name, max_bytes from user_ts_quotas;
no rows selected


使用sys用户,授予用户USER_A对表空间TBS_DAT权限,

SQL> alter user user_a quota unlimited on tbs_dat;

User altered.


再次执行插入,报相同的错误,但错误表空间,变为TBS_IDX,

SQL> insert all into TEST

(...)

values (...)

into TEST

(...)

values (...)

into TEST

(...)

values (...)

select 1 from dual;

*

ERROR at line 4:

ORA-01950: no privileges on tablespace 'TBS_IDX'


这是怎么回事?


其实看一下IDX,就可以猜出来了,这张表一定是有索引,并且存储于TBS_IDX,而且用户对于TBS_IDX无使用权限,

SQL> select index_name, table_name, tablespace_name from user_indexes WHERE table_name='TEST';

INDEX_NAME     TABLE_NAME    TABLESPACE_NAME

------------------ ------------------- ------------------------------

PK_TEST            TEST                   TBS_IDX

IDX_TEST_01     TEST                   TBS_DAT


授权用户USER_A对于TBS_IDX的权限,

SQL> alter user user_a quota unlimited on tbs_idx;

User altered.


此时就可以将表放置TBS_DAT,

alter table test move tablespace tbs_dat;

Table altered.


相应地要将索引放置,索引表空间TBS_IDX,

SQL> alter index IDX_TEST_01 rebuild tablespace tbs_idx;

Index altered.


可以使用如下SQL,找出失效的索引,

SELECT * FROM user_indexes WHERE status='UNUSABLE';


执行alter index ... rebuild重建索引,

SQL> alter index IDX_TEST_01 rebuild;

Index altered.


此时表和索引,都是正确的表空间了,再执行插入操作,就不会报错了,

SQL> select table_name, tablespace_name from user_tables;

TABLE_NAME   TABLESPACE
----------------- --------------------
TEST                 TBS_DAT


SQL> select index_name, table_name, tablespace_name from user_indexes WHERE table_name='TEST';

INDEX_NAME     TABLE_NAME    TABLESPACE_NAME

------------------ ------------------- ------------------------------

PK_TEST            TEST                   TBS_IDX

IDX_TEST_01     TEST                   TBS_IDX




开发同学的问题解决了,但凡事要问个为什么,不仅仅是学习Oracle,任何事情都如此。为什么表没有存储于用户的默认表空间而是存储于系统默认表空间?为什么用户甚至没有他的默认表空间的使用权限?如果表空间没有使用权限,为什么能建表?



为什么表没有存储于用户的默认表空间而是存储于系统默认表空间


我们知道,如果手工执行CREATE TABLE建表语句,没有显示标识表空间,则会存储于用户默认表空间。


另外一种可能,就是逻辑导出和导入,我简单测试了下,用户A默认表空间TBS_A,包含一张表名叫TEST,存储于表空间TBS_A,用户B默认表空间则是TBS_B,系统默认表空间USERS。

无论使用exp/imp,还是expdp/impdp,导出用户A的数据,使用imp导入用户B,用户B拥有的TEST表,则存储于TBS_A表空间。使用impdp加上remap_schema=a:b参数,不用remap_tablespace参数,导入用户B,用户B拥有的TEST表,则还是存储于TBS_A表空间。




为什么用户甚至没有他的默认表空间的使用权限


之前写过的一篇文章《resource角色对quota表空间限额的影响》(http://blog.csdn.net/bisal/article/details/43192051),结论就是,若为用户授予resource角色,则用户自动具有UNLIMITED TABLESPACE权限,即使用quota限额,也不受其控制。按理说用户应该有这个权限,验证一下,

SQL> create user a identified by a;

User created.


SQL> grant resource, connect to a;

Grant succeeded.


登陆用户A,确实包含UNLIMITED TABLESPACE,

SQL> select privilege from user_sys_privs;

PRIVILEGE

----------------------------------------

UNLIMITED TABLESPACE


用户A角色是CONNECT和RESOURCE,

SQL> SELECT * FROM user_role_privs;

USERNAME   GRANTED_ROLE   ADM  DEF  OS_

--------------- ---------------------- ------ ------ ------

A                    CONNECT               NO    YES  NO

A                    RESOURCE             NO    YES  NO


但上述问题中的用户,系统权限检索结果为空,

SQL> select privilege from user_sys_privs;

no rows selected


猜测一:使用revoke收回了UNLIMITED TABLESPACE权限,

SQL> revoke unlimited tablespace from user_b;
Revoke succeeded.


猜测二:原来这用户有DBA的权限,然后收回了DBA权限,默认会收回UNLIMITED TABLESPACE,可以验证下,

SQL> grant dba to a;

Grant succeeded.


登陆用户A,

SQL> select privilege from user_sys_privs;

PRIVILEGE

----------------------------------------

UNLIMITED TABLESPACE


SQL> SELECT * FROM user_role_privs;

USERNAME    GRANTED_ROLE   ADM  DEF  OS_

---------------  ---------------------- ------ ------ ------

A                      CONNECT              NO    YES  NO

A                      DBA                        NO    YES  NO

A                      RESOURCE            NO    YES  NO


revoke用户A的dba,

SQL> revoke dba from a;

Revoke succeeded.


登陆用户A,

SQL> SELECT * FROM user_role_privs;

USERNAME    GRANTED_ROLE  ADM   DEF   OS_

--------------- ---------------------- ------- ------ ------

A                      CONNECT             NO     YES   NO

A                      RESOURCE           NO     YES   NO


此时UNLIMITED TABLESPACE已经被删除,

SQL> select privilege from user_sys_privs;

no rows selected




如果表空间没有使用权限,为什么能建表


其实可能很多朋友已经猜到,这和Oracle 11g的一个新特性有关,说新已经不新了,延迟段创建这个功能,这篇文章《一个用户创建引发的权限控制问题》介绍了这个特性,并且实现了一个和这个延迟段创建新特性相关的用户需求。简单讲,默认将表(以及索引、LOB)的物理空间分配推迟到第一条记录插入到表中时。即有实际的数据插入表中时,再为每个对象初始化空间分配。其中11.2.0.1不支持分区表 、bitmap join indexes和domain indexes。11.2.0.2版本开始支持分区表。


假设用户A没有unlimited tablespace系统权限,正常来讲,他不能做任何数据变更,默认情况下,他可以创建表,

SQL> CREATE TABLE a(a NUMBER);

Table created.


但是不能插入,报错ORA-01950,

SQL> INSERT INTO a VALUES(1);

INSERT INTO a VALUES(1)

*

ERROR at line 1:

ORA-01950: no privileges on tablespace 'TBS_DAT'


原因就是,11g会默认开启延迟段创建,segment_created是NO表示此时未创建段,因此允许创建表,只有当插入数据,需要分配段空间的时候,才会报错,

SQL> SELECT table_name, tablespace_name, segment_created FROM user_tables WHERE table_name='TEST';

TABLE_NAME   TABLESPACE_NAME   SEG

----------------- -------------------------- -------

TEST                 TBS_DAT                       NO




如何批量变更表、索引以及LOB对象的表空间


为了批量操作,可以动态拼接,对于表可以用,

SQL> select 'alter table ' || table_name || ' move tablespace tbs_dat;' from user_tables where tablespace_name='USERS';
'alter table ' || table_name || ' move tablespace tbs_dat;'

--------------------------------------------------------------------------------
alter table TEST move tablespace tbs_dat;

...


对于索引可以用,当然可以加上online,

SQL> select 'alter index ' || index_name || ' rebuild tablespace tbs_dat;' from user_indexes where tablespace='USERS';

'alter index ' || index_name || ' rebuild tablespace tbs_dat;'

--------------------------------------------------------------------------------

alter index P_TEST rebuild tablespace tbs_dat;

...


对于LOB字段,就不能使用alter index了,

SQL> alter index SYS_IL0000092583C00011$$ rebuild tablespace tbs_dat;

*

ERROR at line 1:

ORA-02327: cannot create index on expression with datatype LOB


正确的语法是,

alter table [table_name] move lob([lob_column_name]) store as(tablespace [target_tablespace_name])


例如下面的语句,

alter table test move lob(msg_content) store as (tablespace tbs_dat);


索引和LOB对象变更了表空间,要检查一下索引的状态,是否正确,如果下面的语句,有返回结果,则需要使用alter index ... rebuild语句,重建索引,让其状态变为VALID,

SELECT * FROM user_indexes WHERE status='UNUSABLE';




总结:

通过ORA-01950错误,我们了解了一些知识点,

1. 动态拼接SQL变更表、索引和LOB对象所属表空间。

2. 分配用户表空间配额,解决ORA-01950错误。

3. unlimited tablespace系统权限的授予。

4. 用户默认表空间,系统默认表空间。

5. 延迟段创建的特性和影响。



如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

从ORA-01950报错我们能了解的知识


从ORA-01950报错我们能了解的知识

最后编辑于:2018/8/10作者: 知音小助手

知音小助手

Java之音网站的小助手,帮助大家整理有价值的文章@@

暂无评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注

arrow grin ! ? cool roll eek evil razz mrgreen smile oops lol mad twisted wink idea cry shock neutral sad ???

服务网站公众号,会定期推送网站优质内容,网站最新动态!

服务网站公众号,会定期推送网站优质内容,网站最新动态!