Oracle Autotrace
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
CDB Warning Thus script used to create the autotrace role can not be run in CDB$ROOT only within a PDB.

-- the fact that this role can not be created in CDB$ROOT is poor form by essentially any definition. I reported it during Beta 2 and it was not fixed.
Create PLUSTRACE Role SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 19 21:05:06 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Enter user-name: sys@pdbdev as sysdba
Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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

SQL> create role plustrace;

Role created.

SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> set echo off
Grant PLUSTRACE Role GRANT plustrace TO <user_name>;
SQL> conn sys@pdbdev as sysdba -- connect as SYS to the pdbdev PDB

SQL> grant plustrace TO uwclass;
 
Syntax and Demos
Syntax SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Trace and Run SQL> uwclass/uwclass@pdbdev

SQL> set autotrace on

SQL> SELECT * FROM dual;
Trace Only SQL> uwclass/uwclass@pdbdev

SQL> set autotrace traceonly

SQL> SELECT * FROM dual;
Trace and Explain SQL> uwclass/uwclass@pdbdev

SQL> set autotrace traceonly explain

SQL> SELECT * FROM dual;
Trace and Statistics SQL> uwclass/uwclass@pdbdev

SQL> set autotrace traceonly statistics

SQL> SELECT * FROM dual;
Stop Tracing SET AUTOTRACE OFF
SQL> uwclass/uwclass@pdbdev

SQL> set autotrace off

Related Topics
Built-in Functions
Built-in Packages
Class Setup
DBMS_XPLAN
Explain Plan
Roles
Trace and TKPROF
What's New In 12cR2
What's New In 18cR1

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved