结论先行： 1，此表的创建用户权限无问题，表上有其他用户创建的索引 2，报错时，这个索引的创建用户在表空间上无权限或配额 3，dba权限的回收，会导致UNLIMITED TABLESPACE系统权限被
结论先行：1，此表的创建用户权限无问题，表上有其他用户创建的索引2，报错时，这个索引的创建用户在表空间上无权限或配额3，dba权限的回收，会导致UNLIMITED TABLESPACE系统权限被回收4，处理方法：给索引创建用户授予权限或配额grant UNLIMITED TABLESPACE to username;或alter user username quota unlimited on tablespace_name;
报错官方解释：Oracle@oel:/home/oracle>oerr ora 0195001950, 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.
测试过程如下：1，创建测试表TEST@regan1> create table test tablespace test as select * from sys.dba_objects;
Table created.
TEST@regan1> create index idx_test_01 on test(OBJECT_NAME) tablespace test;
Index created.
TABLESPACE_NAME TABLESPACE_T SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)——————– ———— ———— ———- ————- ———— ————-USERS PERMANENT 1399 179040 1330 95.05 69TEST PERMANENT 200 25600 4 1.94 196
2，创建测试用户SYS@regan1> create user test02 identified by test;
User created.
SYS@regan1> select username,default_tablespace from dba_users where username like ‘TEST%’;
USERNAME DEFAULT_TABLESPACE—————————— ——————————TEST TESTTEST02 USERS
SYS@regan1> grant connect to test02;
Grant succeeded.
SYS@regan1> grant resource to test02;
Grant succeeded.
3，创建测试用户下索引TEST@regan1> grant index on test to test02;
Grant succeeded.
SYS@regan1> grant unlimited tablespace to test02;
Grant succeeded.
TEST02@regan1> create index idx_test_02 on test.test(OBJECT_ID) tablespace test;
Index created.
TEST@regan1> select index_name,tablespace_name,status from dba_indexes where table_name=’TEST’;
INDEX_NAME TABLESPACE_NAME STATUS—————————— ——————– ——–IDX_TEST_01 TEST VALIDIDX_TEST_02 TEST VALID
4，查看索引extentTEST@regan1> select OWNER,EXTENT_ID,BYTES/1024 from dba_extents where SEGMENT_NAME=’IDX_TEST_02′;
OWNER EXTENT_ID BYTES/1024—————————— ———- ———-TEST02 0 64TEST02 1 64TEST02 2 64TEST02 3 64TEST02 4 64
5，插入测试TEST@regan1> insert into test select * from test;
14082 rows created.
TEST@regan1> COMMIT;
Commit complete.
TEST@regan1> select OWNER,EXTENT_ID,BYTES/1024 from dba_extents where SEGMENT_NAME=’IDX_TEST_02′;
OWNER EXTENT_ID BYTES/1024—————————— ———- ———-TEST02 0 64TEST02 1 64TEST02 2 64TEST02 3 64TEST02 4 64TEST02 5 64TEST02 6 64TEST02 7 64TEST02 8 64
9 rows selected.
6，授予dba权限并回收SYS@regan1> select * from dba_sys_privs where grantee=’TEST02′;
GRANTEE PRIVILEGE ADM—————————— —————————————- —TEST02 UNLIMITED TABLESPACE NO
SYS@regan1> grant dba to test02;
Grant succeeded.
SYS@regan1> revoke dba from test02;
Revoke succeeded.
SYS@regan1> select * from dba_sys_privs where grantee=’TEST02′;
no rows selected
7，插入测试TEST@regan1> insert into test select * from test;insert into test select * from test *ERROR at line 1:ORA-01950: no privileges on tablespace ‘TEST’
TEST@regan1> select OWNER,EXTENT_ID,BYTES/1024 from dba_extents where SEGMENT_NAME=’IDX_TEST_02′;
OWNER EXTENT_ID BYTES/1024—————————— ———- ———-TEST02 0 64TEST02 1 64TEST02 2 64TEST02 3 64TEST02 4 64TEST02 5 64TEST02 6 64TEST02 7 64TEST02 8 64
9 rows selected.
8，授予配额或权限上面是授予UNLIMITED TABLESPACE权限，以下使用配额。SYS@regan1> alter user test02 quota unlimited on test;
User altered.
SYS@regan1> select * from dba_ts_quotas where username=’TEST02′;
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO——————– —————————— ———- ———- ———- ———- —TEST TEST02 589824 -1 72 -1 NO
9，插入测试TEST@regan1> insert into test select * from test;
28173 rows created.
TEST@regan1> select OWNER,EXTENT_ID,BYTES/1024 from dba_extents where SEGMENT_NAME=’IDX_TEST_02′;
OWNER EXTENT_ID BYTES/1024—————————— ———- ———-TEST02 0 64TEST02 1 64TEST02 2 64TEST02 3 64TEST02 4 64TEST02 5 64TEST02 6 64TEST02 7 64TEST02 8 64TEST02 9 64TEST02 10 64TEST02 11 64TEST02 12 64TEST02 13 64TEST02 14 64TEST02 15 64TEST02 16 1024
17 rows selected.
TEST@regan1> rollback;
Rollback complete.
TEST@regan1> select OWNER,EXTENT_ID,BYTES/1024 from dba_extents where SEGMENT_NAME=’IDX_TEST_02′;
OWNER EXTENT_ID BYTES/1024—————————— ———- ———-TEST02 0 64TEST02 1 64TEST02 2 64TEST02 3 64TEST02 4 64TEST02 5 64TEST02 6 64TEST02 7 64TEST02 8 64TEST02 9 64TEST02 10 64TEST02 11 64TEST02 12 64TEST02 13 64TEST02 14 64TEST02 15 64TEST02 16 1024
17 rows selected.—-这步可以看到，rollback后索引的extent依然处于分配状态。
SYS@regan1> alter user test02 quota 0 on test;
User altered.
SYS@regan1> select * from dba_ts_quotas where username=’TEST02′;
no rows selected
TEST@regan1> insert into test select * from test;
28164 rows created.—-这步可以看到，索然权限和配额都已经没有，但是之前已分配的extent仍然可以使用。
TEST@regan1> insert into test select * from test;insert into test select * from test*ERROR at line 1:ORA-01950: no privileges on tablespace ‘TEST’—-继续插入则由于缺少权限和配额，插入报错。
更多Oracle相关信息见Oracle 专题页面 https://www.558idc.com/topicnews.aspx?tid=12
