設定AUTOTRACE出現ORA-3212錯誤

客戶環境下設定了AUTOTRACE，結果碰到了ORA-3212錯誤。

詳細錯誤如下：

SQL> conn / as sysdba
SQL> grant select on v_$session to posmrk;

Grant succeeded.

SQL> grant select on v_$mystat to posmrk;

Grant succeeded.

SQL> grant select on v_$statname to posmrk;

Grant succeeded.

SQL> CONN POSMRK
Enter password:
Connected.
SQL> @?/rdbms/admin/utlxplan

Table created.

SQL> conn posmrk@219.143.210.210:1621/pcmrk
已連線。
SQL> set autot trace
SQL> select * from dual;
Error ORA-942 while gathering statistics
SP2-0612: Error generating AUTOTRACE report

SP2-0612: Error generating AUTOTRACE report

Execution Plan
----------------------------------------------------------
An uncaught error happened in fetching the records : ORA-03212: Temporary Segment cannot be created in locally-managed tablespace
ORA-03212: Temporary Segment cannot be created in locally-managed tablespace

SP2-0612: Error generating AUTOTRACE STATISTICS report

由於當時沒有網路和文件，只能根據錯誤描述來分析問題。這個錯誤似乎和表空間以及臨時段有關，那麼問題牽扯的層面並不太多。

檢查了一下資料庫的臨時表空間設定，並未發現問題，檢查了一下使用者的表空間以及UNLIMITED TABLESPACE許可權，也未發現異常。

SQL> conn system
Connected.
SQL> set autot trace    
SQL> select * from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autot off

切換為其他使用者，沒有發現異常，說明應該是錯誤使用者本身的設定所致。

SQL> select username, temporary_tablespace
  2  from dba_users
  3  where username = 'POSMRK';

USERNAME                 TEMPORARY_TABLESPACE
------------------------ -----------------------------
POSMRK                   SYSTEM

SQL> alter user posmrk temporary tablespace temp;

User altered.

SQL> conn posmrk
Connected.
SQL> set autot trace
SQL> select * from dual;
Error ORA-942 while gathering statistics
SP2-0612: Error generating AUTOTRACE report

SP2-0612: Error generating AUTOTRACE report

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

SP2-0612: Error generating AUTOTRACE STATISTICS report

檢查使用者的臨時表空間設定，發現錯誤的設定為SYSTEM，顯然這時導致問題的原因，從SYSTEM表空間轉變為LOCAL管理方式以後，就不應該設定SYSTEM作為臨時表空間了，而應該使用專門的TEMPORARY表空間。

對這個設定進行修改後，ORA-3212錯誤已經小時，還存在一個ORA-942錯誤，這個錯誤以前碰到過，應該是缺少動態檢視的許可權所致：

SQL> set autot off
SQL> select table_name, privilege from user_tab_privs where table_name like 'V_$%';

TABLE_NAME                     PRIVILEGE
------------------------------ -----------------------------------------------
V_$SESSION                     SELECT
V_$MYSTAT                      SELECT
V_$STATNAME                    SELECT

SQL> conn / as sysdba
Connected.
SQL> grant select on v_$sesstat to posmrk;

Grant succeeded.

SQL> conn posmrk
Connected.
SQL> set autot trace
SQL> select * from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autot off

剛開始授權的時候，授權了V_$SESSION許可權而缺少了V_$SESSTAT檢視的許可權，導致這個問題產生，對檢視授權後，問題解決。