ORA-01950 报错解决实例

ERROR at line 1:

ORA-01950: no privileges on tablespace 'TEST'

结论先行：

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 01950

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.

测试过程如下：

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            69
TEST                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                          TEST
TEST02                        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                VALID
IDX_TEST_02                    TEST                VALID

4，查看索引extent

TEST@regan1> select OWNER,EXTENT_ID,BYTES/1024 from dba_extents where SEGMENT_NAME='IDX_TEST_02';
OWNER                          EXTENT_ID BYTES/1024
------------------------------ ---------- ----------
TEST02                                  0        64
TEST02                                  1        64
TEST02                                  2        64
TEST02                                  3        64
TEST02                                  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        64
TEST02                                  1        64
TEST02                                  2        64
TEST02                                  3        64
TEST02                                  4        64
TEST02                                  5        64
TEST02                                  6        64
TEST02                                  7        64
TEST02                                  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        64
TEST02                                  1        64
TEST02                                  2        64
TEST02                                  3        64
TEST02                                  4        64
TEST02                                  5        64
TEST02                                  6        64
TEST02                                  7        64
TEST02                                  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        64
TEST02                                  1        64
TEST02                                  2        64
TEST02                                  3        64
TEST02                                  4        64
TEST02                                  5        64
TEST02                                  6        64
TEST02                                  7        64
TEST02                                  8        64
TEST02                                  9        64
TEST02                                10        64
TEST02                                11        64
TEST02                                12        64
TEST02                                13        64
TEST02                                14        64
TEST02                                15        64
TEST02                                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        64
TEST02                                  1        64
TEST02                                  2        64
TEST02                                  3        64
TEST02                                  4        64
TEST02                                  5        64
TEST02                                  6        64
TEST02                                  7        64
TEST02                                  8        64
TEST02                                  9        64
TEST02                                10        64
TEST02                                11        64
TEST02                                12        64
TEST02                                13        64
TEST02                                14        64
TEST02                                15        64
TEST02                                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'

----继续插入则由于缺少权限和配额，插入报错。