|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
set autotrace in SQL*Plus
set autotrace off set autotrace on set autotrace traceonly set autotrace on explain set autotrace on statistics set autotrace on explain statistics set autotrace traceonly explain set autotrace traceonly statistics set autotrace traceonly explain statistics set autotrace off explain set autotrace off statistics set autotrace off explain statistics
Setting autotrace allows to display some statistics and/or an query execution plan for DML statements.
If autotrace is enabled with
The explain plan feature of autotrace requires a plan_table which can be created with $ORACLE_HOME/rdbms/admin/utlxplan.sql
The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.
An Oracle installation comes with $ORACLE_HOME/sqlplus/admin/plustrce.sql which installs the role plustrace. plustrace is granted those select rights. If now plustrace is granted to a user, he will then be able to turn autotrace on. Alternatively, plustrace can be granted to public.
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
If flagger is set to something different than
set flagger off
Storing settings across sessions
Settings (such as autotrace) can be stored accross sessions with the glogin.sql and/or login.sql file.