Oracle Autotrace
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
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 [oracle@test21 dbhome_1]$ sqlplus sys@pdbdev as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Jan 23 03:03:33 2021
Version 21.1.0.0.0

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

Enter password:

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.1.0.0.0

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>
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>
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> conn uwclass@pdbdev
Enter password:

set autotrace on

SELECT * FROM dual;

D
-
X

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

-------------------------------------------------------------------------
| Id | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

Statistics
----------------------------------------------------------
5 recursive calls
6 db block gets
6 consistent gets
0 physical reads
1548 redo size
564 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Trace Only set autotrace traceonly

SELECT * FROM dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

-------------------------------------------------------------------------
| Id | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

Statistics
----------------------------------------------------------
5 recursive calls
6 db block gets
6 consistent gets
0 physical reads
1548 redo size
564 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Trace and Explain set autotrace traceonly explain

SELECT * FROM dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

-------------------------------------------------------------------------
| Id | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------
Trace and Statistics set autotrace traceonly statistics

SELECT * FROM dual;

Statistics
----------------------------------------------------------
5 recursive calls
6 db block gets
6 consistent gets
0 physical reads
1548 redo size
564 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Stop Tracing SET AUTOTRACE OFF
set autotrace off

SELECT * FROM dual;

D
-
X

Related Topics
Built-in Functions
Built-in Packages
Class Setup
DBMS_XPLAN
Explain Plan
Roles
Trace and TKPROF
What's New In 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx