客户环境下设置了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视图的权限，导致这个问题产生，对视图授权后，问题解决。