How do I enable AutoTrace features of sqlplus

Posted on 2009-06-30 filed under: Oracle (RSS) 22 views

Through the following methods can Autotrace granted permission to Everyone,

Autotrace If you need to restrict the authority of the public can be re-authorized user on a specific 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, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys as sysdba
Please enter password:
Is connected.
SQL> @? / Rdbms / admin / utlxplan

Table has been created.

SQL> create public synonym plan_table for plan_table;

Synonyms have been created.

SQL> grant all on plan_table to public;

Successful authorization.

SQL> @? / Sqlplus / admin / plustrce
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: the role of 'PLUSTRACE' does not exist

SQL> create role plustrace;

Role has been created
SQL>
SQL> grant select on v_ $ sesstat to plustrace;

Successful authorization.

SQL> grant select on v_ $ statname to plustrace;

Successful authorization.

SQL> grant select on v_ $ session to plustrace;

Successful authorization.

SQL> grant plustrace to dba with admin option;

Successful authorization.

SQL>
SQL> set echo off

DBA user plustrace the first to be awarded the role, and then we can plustrace given public

So that all users will have the authority plustrace role.

SQL> grant plustrace to public;

Successful authorization.

Then we can use the functions of the AutoTrace.

SQL> connect eqsp / eqsp
Is connected.
SQL> set autotrace on
SQL> set timing on
SQL>

On several commonly used Autotrace options:

SET AUTOTRACE OFF ---------------- not AUTOTRACE report generation, which is the default mode
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE optimizer only shows the report of the implementation of the path
SET AUTOTRACE ON STATISTICS - displays only the implementation of statistical information
SET AUTOTRACE ON ----------------- includes the implementation of plans and statistical information
SET AUTOTRACE TRACEONLY ------ with set autotrace on, but it does not show the query output

SQL> set autotrace traceonly
SQL> select table_name from user_tables;

98 firms have chosen.

Has spent 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 calls
0 db block gets
1389 consistent gets
0 physical reads
0 redo size
2528 bytes sent via SQL * Net to client
569 bytes received via SQL * Net from client
8 SQL * Net roundtrips to / from client
0 sorts (memory)
0 sorts (disk)
98 rows processed

SQL>

== THE END