The Oracle (tm) Users' Co-Operative FAQ
I get strange error messages when trying to use AUTOTRACE in SQL*Plus. How do I fix this ?
| Author's name: Nuno Souto Author's Email: dcs2k@optushome.com.au |
Date written: 4th Sept 2001 Oracle version(s): 7.3 - 8.1.7 |
| AUTOTRACE needs a standard ORACLE table in order to operate properly. This table is called PLAN_TABLE and needs to be accessible to the userid you're logged in as. There is a standard script that creates this table. It's called "utlxplan.sql" and it lives in ${ORACLE_HOME}/rdbms/admin. Execute this script to create the table under the user you're logged in as. Or ask the DBA to give you access to a PLAN_TABLE. |
AUTOTRACE can do the EXPLAIN PLAN for you automatically. It can also display the system statistics gathered from executing a given statement. This is a new feature in the later versions of ORACLE and SQL*Plus.
In order for statistics to be enabled in AUTOTRACE, your DBA needs to setup a few things:
- A specific role called PLUSTRACE needs to be created, enabled and granted to the user.
- The script ${ORACLE_HOME}/sqlplus/admin/plustrce.sql is provided by ORACLE so that the DBA can create and setup this role.
- Once the role has been created, the DBA can grant it to each specific user or PUBLIC. It then becomes possible for the users to enable the AUTOTRACE statistics and get complete information about each SQL statement they run in SQL*Plus.
Further reading: N/A
