设置AUTOTRACE出现ORA-3212错误

最新推荐文章于 2021-04-05 02:44:30 发布

ckawt40802

最新推荐文章于 2021-04-05 02:44:30 发布

阅读量138

点赞数

文章标签：数据库

客户环境下设置了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

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