本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer，已开源在github ，欢迎star！
开发同学提了一个问题，说有个性能测试的数据库，正在准备数据，使用如下批量操作的语句，报错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
现在的问题就是，
为什么用户USER_A默认表空间是TBS_DAT，但报的是USERS表空间无权限？
为什么会报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
总结一下上面的问题：
TEST表并未存储于USER_A的默认表空间，而是存储于系统默认表空间。
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 TABLESPACESQL 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错误，我们了解了一些知识点，
动态拼接SQL变更表、索引和LOB对象所属表空间。
分配用户表空间配额，解决ORA-01950错误。
unlimited tablespace系统权限的授予。
用户默认表空间，系统默认表空间。
延迟段创建的特性和影响。
如果您觉得此篇文章对您有帮助，欢迎关注微信公众号：bisal的个人杂货铺，您的支持是对我最大的鼓励！共同学习，共同进步:)
