sp2-0611 error occurred while Oracle set Autotrace

This error is rather strange, setting Autotrace is a sp2-0611 and ORA-942 error.

Version 11.2 for Linux x86-64:

Sql> CONN Test/test

is connected.

sql> SET Autot TRACE

An error occurred while collecting statistics ORA-942

sp2-0611: Error while enabling statistics reporting

Sql> SET Autot on

An error occurred while collecting statistics ORA-942

sp2-0611: Error while enabling statistics reporting

Sql> SET Autot off

Sql> SET Autot on

An error occurred while collecting statistics ORA-942

sp2-0611: Error while enabling statistics reporting

Sql> @?/rdbms/admin/utlxplan

Table has been created.

sql> SET Autot TRACE

An error occurred while collecting statistics ORA-942

sp2-0611: Error while enabling statistics reporting

Sql> SET Autot on

An error occurred while collecting statistics ORA-942

sp2-0611: Error while enabling statistics reporting

This error has never been encountered before, generally resulting in autotrace failure either because the plan_table does not exist or because the view rights included in the Plustrace role or plustrace role are missing.

More Wonderful content: http://www.bianceng.cn/database/Oracle/

The plan_table in 11g is a synonym, pointing to the plan_table$ table of Sys, and the DML permission for this table has been delegated to public, so it should not be a problem caused by this table.

The plan_table is set up under the current user, the error remains.

Suspicion is caused by a lack of permissions:

Sql> Conn/as SYSDBA

is connected.

Sql> @?/sqlplus/admin/plustrce

Sql>

sql> drop role Plustrace;

Drop role Plustrace

*

Line 1th Error:

ORA-01919: Role ' plustrace ' does not exist

sql> Create role Plustrace;

The role has been created.

Sql>

Sql> Grant Select on V_$sesstat to Plustrace;

The authorization was successful.

Sql> Grant Select on V_$statname to Plustrace;

The authorization was successful.

Sql> Grant Select on V_$mystat to Plustrace;

The authorization was successful.

Sql> Grant Plustrace to dba with admin option;

The authorization was successful.

Sql>

Sql> set echo off

Sql> GRANT plustrace to TEST;

The authorization was successful.

Return to the test user just now and log in again:

Sql> CONN Test/test

is connected.

Sql> SET Autot on

The problem disappears, which seems to be due to lack of authority. However, under normal circumstances, Oracle's error should be:

Sql> CREATE USER a identified by A;

User has created.

Sql> GRANT CONNECT to A;

The authorization was successful.

Sql> CONN a/a

is connected.

Sql> SET Autot on

SP2-0618: The session identifier could not be found. Enable Check Plustrace role

sp2-0611: Error while enabling statistics reporting

Sql> SET Autot off

Check the permissions that the test user has:

Sql> SELECT OWNER, table_name, privilege

2 from User_tab_privs

3 WHERE wner = ' SYS '

4 and privilege = ' SELECT ';

OWNER table_name Privilege

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

SYS v_$process SELECT

SYS v_$session SELECT

SYS V_$parameter SELECT

SYS V_$mystat SELECT

It is suspected that because the permission that contains the V_$mystat view, the Oracle does not have the error plustrace permission, but error ORA-942 errors.

Sql> Conn/as SYSDBA

is connected.

Sql> GRANT SELECT on V_$mystat to A;

The authorization was successful.

Sql> CONN a/a

is connected.

Sql> SET Autot on

An error occurred while collecting statistics ORA-942

sp2-0611: Error while enabling statistics reporting

This is the problem.