How to enable the AutoTrace function of sqlplus

Author: eygle | English Version [Copyright Disclaimer: During reprinting, please be sure to mark the original source and author information of the article as hyperlinks and this statement]
Link: http://www.eygle.com/archives/2004/06/enable_sqlplus_autotrace.html

 

 

You can grant the Autotrace permission to Everyone using the following methods,

If you need to restrict the Autotrace permission, you can change the public authorization to the specific user authorization.

 

D: \ oracle \ ora92> sqlplus/nolog SQL * Plus: Release 9.2.0.1.0-Production on Tuesday June 3 15:16:03 2003 Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved. SQL> connect sys as sysdba Enter the password: Connected. SQL> @? \ Rdbms \ admin \ utlxplan The table has been created. SQL> create public synonym plan_table for plan_table; You have created a synonym. SQL> grant all on plan_table to public; Authorization successful. SQL> @? \ Sqlplus \ admin \ plustrce SQL> SQL> drop role plustrace; Drop role plustrace * ERROR is located in row 1st: ORA-01919: Role 'plustrace 'does not exist SQL> create role plustrace; Role created SQL> SQL> grant select on v _ $ sesstat to plustrace; Authorization successful. SQL> grant select on v _ $ statname to plustrace; Authorization successful. SQL> grant select on v _ $ session to plustrace; Authorization successful. SQL> grant plustrace to dba with admin option; Authorization successful. SQL> SQL> set echo off

DBA users are first granted the plustrace role, and then we can grant plustrace to public

In this way, all users will have the permissions of the plustrace role.

 

SQL> grant plustrace to public; Authorization successful.

Then we can use the AutoTrace function.

 

SQL> connect eqsp/eqsp Connected. SQL> set autotrace on SQL> set timing on SQL>

 

Description of several common Autotrace options:

Set autotrace off ---------------- do not generate AUTOTRACE Report, which is the default mode
Set autotrace on explain ------ AUTOTRACE only displays the optimizer execution path report
Set autotrace on statistics -- display only execution STATISTICS
Set autotrace on ----------------- contains the execution plan and statistics
Set autotrace traceonly ------ same as set autotrace on, but no query output is displayed

SQL> set autotrace traceonly SQL> select table_name from user_tables; 98 rows have been selected. Used time: 00: 00: 00.04 Execution Plan ---------------------------------------------------------- 0 select statement Optimizer = CHOOSE 1 0 NESTED LOOPS 2 1 nested loops (OUTER) 3 2 nested loops (OUTER) 4 3 nested loops (OUTER) 5 4 nested loops (OUTER) 6 5 NESTED LOOPS 7 6 table access (by index rowid) OF 'obj $' 8 7 INDEX (range scan) OF 'I _ OBJ2' (UNIQUE) 9 6 table access (CLUSTER) OF 'tab $' 10 9 INDEX (unique scan) OF 'I _ OBJ #' (NON-UNIQUE) 11 5 table access (by index rowid) OF 'obj $' 12 11 INDEX (unique scan) OF 'I _ obj1' (UNIQUE) 13 4 INDEX (unique scan) OF 'I _ obj1' (UNIQUE) 14 3 table access (CLUSTER) OF 'User $' 15 14 INDEX (unique scan) OF 'I _ USER #' (NON-UNIQUE) 16 2 table access (CLUSTER) OF 'seg $' 17 16 INDEX (unique scan) OF 'I _ FILE # _ BLOCK #' (NON-UNIQUE) 18 1 table access (CLUSTER) OF 'ts $' 19 18 INDEX (unique scan) OF 'I _ TS #' (NON-UNIQUE)   Statistics ---------------------------------------------------------- 0 recursive cballs 0 db block gets 1389 consistent gets 0 physical reads 0 redo size 2528 bytes sent via SQL * Net to client 569 bytes encoded ed via SQL * Net from client 8 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 98 rows processed SQL>

-The End-