ORA-00959 解決一例

     軍衛的資料庫，大多數是從Oracle7.3 升級到8i,再升級到11G, 在Oracle73時有一個表空間USER_DATA, 一般情況下，多次升級時都會保留下來。

此次在搭建開發資料庫時，為給資料庫瘦身，需去掉多餘的表空間。 在刪除USER_DATA表空間前，確保USER_DATA表空間上沒有資料庫物件。資料庫版本為X64-11.2.0.1

一、在下面的操作中，發現了以下的問題：

SQL> show user

USER 為 "SYSTEM"

SQL> select default_tablespace from dba_users where username='SYSTEM';

DEFAULT_TABLESPACE

------------------------------------------------------------

USER_DATA

SQL> CREATE TABLE PBCATFMT

  2  (

  3    PBF_NAME  VARCHAR2(30 BYTE),

  4    PBF_FRMT  VARCHAR2(254 BYTE),

  5    PBF_TYPE  INTEGER,

  6    PBF_CNTR  INTEGER

  7  )  ;

CREATE TABLE PBCATFMT

*

ERROR at line 1:

ORA-00959: tablespace 'USER_DATA' does not exist

由於system 使用者的預設表空間是USER_DATA，自然ORA-00959: 錯誤出現很正常。

SQL> alter user system  default tablespace users;

User altered.

SQL> select default_tablespace from dba_users where username='SYSTEM';

DEFAULT_TABLESPACE

------------------------------

USERS

SQL> CREATE TABLE PBCATFMT

  2  (

  3    PBF_NAME  VARCHAR2(30 BYTE),

  4    PBF_FRMT  VARCHAR2(254 BYTE),

  5    PBF_TYPE  INTEGER,

  6    PBF_CNTR  INTEGER

  7  )  ;

Table created.

表可以建立成功。

SQL>  CREATE   INDEX PBSYSCATFRMTS_IDX  ON pbcatfmt(pbf_name);

 CREATE   INDEX PBSYSCATFRMTS_IDX  ON pbcatfmt(pbf_name)

                                      *

ERROR at line 1:

ORA-00959: tablespace 'USER_DATA' does not exist

沒有指定表空間時，索引建立預設使用的是建立者的預設表空間，為什麼在這還報錯呢？

我重新建立'USER_DATA' 表空間，並重新執行上面的索引建立語句， 自然可以建立成功，查詢資料字典，該索引的確也在USERS 表空間上， USER_DATA'表空間上沒有資料庫物件。

二、原因查詢

 按照以往的經驗，由於這個表是新建的，不存在其它使用USER_DATA 表空間上的觸發器或索引。在沒有USER_DATA 表空間 的情況下，如果指定表空間，例如 CREATE   INDEX PBSYSCATFRMTS_IDX  ON pbcatfmt(pbf_name)  tablespace users ;  是可以執行成功的。那麼資料庫後面到底做了些什麼？

 使用Logminer  對日誌進行分析，看看執行建立索引時，資料庫到底做了些什麼。我們發現，系統對SYS  下的'OBJ$', 'AUD$', 'WRI$_ALERT_THRESHOLD_LOG'和SYSMAN 下的'MGMT_TASK_QTABLE', 'MGMT_COLLECTION_TASKS' 做了操作。會不會這些表使用了USER_DATA 表空間？

SQL> SELECT owner, table_name , tablespace_name

  2    FROM dba_tables

  3   WHERE (    owner = 'SYS'

  4          AND table_name IN ('OBJ$', 'AUD$', 'WRI$_ALERT_THRESHOLD_LOG')

  5         )

  6      OR (    owner = 'SYSMAN'

  7          AND table_name IN ('MGMT_TASK_QTABLE', 'MGMT_COLLECTION_TASKS')

  8         );

OWNER      TABLE_NAME                     TABLESPACE_NAME

---------- ------------------------------ --------------------

SYSMAN     MGMT_TASK_QTABLE               SYSAUX

SYSMAN     MGMT_COLLECTION_TASKS          SYSAUX

SYS        WRI$_ALERT_THRESHOLD_LOG       SYSTEM

SYS        OBJ$                           SYSTEM

SYS        AUD$                           SYSTEM

可以看到這些表沒有使用USER_DATA 表空間。

出於好奇，我又檢視了SYS 和SYSMAN 使用者預設表空間的資訊：

SQL> SELECT username, default_tablespace, temporary_tablespace

  2    FROM dba_users

  3   WHERE username IN ('SYS', 'SYSMAN');

USERNAME             DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE

-------------------- -------------------- --------------------

SYS                  USER_DATA            TEMP

SYSMAN               SYSAUX               TEMP

發現SYS 預設的表空間為USER_DATA 。

我試著修改下SYS 預設的表空間：

SQL> alter user sys default tablespace users;

 

User altered

再重新建立索引：

 

SQL>  CREATE   INDEX PBSYSCATFRMTS_IDX  ON pbcatfmt(pbf_name);

 

Index created

成功！

三、原因猜想

    

       在建立索引時，如果不指定表空間，ORACLE 的某些系統操作會使用SYS 的預設表空間，而且也許這些操作並不體現在Redo 檔案中。因此報ORA-00959 ，不只是看運算元據庫使用者的資訊，也要留意下SYS 使用者的資訊。