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

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 从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表空间无权限?
  1. 为什么会报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的默认表空间,而是存储于系统默认表空间。
  1. 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对象所属表空间。
  1. 分配用户表空间配额,解决ORA-01950错误。

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

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

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

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

从ORA-01950报错我们能了解的知识 从ORA-01950报错我们能了解的知识
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 从ORA-01950报错我们能了解的知识


 上一篇
Windows环境安装MySQL ZIP Archive Windows环境安装MySQL ZIP Archive
这是之前写的几篇关于Linux环境下安装MySQL数据库的文章,包括5.7.19以及5.6这两个版本, 《》 《》 《》 今儿碰巧需要部署一个Windows版本,于是先下载了最新的,5.7.20版本安装包,选择了Windows平台
下一篇 
Oracle ADG究竟是否收费? Oracle ADG究竟是否收费?
前两天微信群中,有位朋友问了, Oracle Database Standard Edition 11gR2标准版支持active data guard么? 可能平时下载、安装Oracle,未必十分关注版本的问题,