专栏首页bisal的个人杂货铺从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. 延迟段创建的特性和影响。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 11g的延迟段功能

    1. 初始创建表时就需要分配空间,自然会占用一些时间,如果初始化多张表,这种影响就被放大。

    bisal
  • 传说中的“谓词越界“场景

    开发环境,碰见一个谓词越界的问题,模拟这条SQL,如下所示,其中A_ID是表test的外键,并且存在索引,

    bisal
  • 登录缓慢的诡异问题

    最近有套测试库,出现了很诡异的现象,通过PLSQL Developer登录这套数据库(用户名:user_a)的时候异常的慢,几分钟才能连上,从现象来看,可能有很...

    bisal
  • 零基础学习python编程不可错过的学习总结,小白福利!

    通过以上可以看到我们写的很贱的程序随便保存了一个.txt结尾的格式,竟然也执行了,并没有按照统一要求的.py格式来设计, 那是不是说明后缀名可以说是任意的呢?理...

    猫咪编程
  • Python学习之类

    之前写的博客,都是借鉴别的博文或者是环境的配置,没有自己的东西,掌握的很少。以后的博文,我都会尽力加上一些自己的东西。现在开始进入我的python世界吧!

    py3study
  • 前端开发中的几种资源重定向方法

    嗯,但是为嘛需要这么做呢...... 这道题面试中也真有很多稀里糊涂做完类似的项目,却答不上来的小伙伴,那你自个儿寻思去吧~

    江米小枣
  • 连仕彤博客[Python笔记] locals() 函数

    行 者
  • Python面向对象编程Day 28部分

      没有异常的情况下,整个代码块运行完毕后去触发__exit__,它的三个参数都为None

    py3study
  • python面向对象之反射和内置方法

    类方法:有个默认参数cls,并且可以直接用类名去调用,可以与类属性交互(也就是可以使用类属性)

    py3study
  • vSphere集成Docker容器

    VMware更新了其核心虚拟化平台——vSphere。其中一个关键的改进是开发人员现在可以在Docker容器中创建应用程序,然后这些容器可以在部署在vSpher...

    SDNLAB

扫码关注云+社区

领取腾讯云代金券