To demonstrate the convenience of tracing files, we give a regular user, Scott, the role of DBA.

1. Use administrative login and grant Scott the role granted to the DBA

[oracle@oracle12c ~]$ sqlplus / as sysdba

SQL> grant dba to scott;

Grant succeeded.

SQL>

Determine the Oracle SID, as shown below. The SID here is ORCL

SQL> select instance_name from V$instance;

INSTANCE_NAME

orcl

SQL>

3. Switch to user SCOTT and determine the session ID

SQL> conn scott/tiger

Connected.

SQL> select sid from v$mystat where rownum=1;

   SID

70

SQL>

4, According to the session ID, determine the address information of the session

SQL> select paddr from v$session where sid=70;

PADDR

000000006DAB6588

SQL>

5, according to the address information of the session, determine the operating system process number

SQL> select spid from v$process where addr=’000000006DAB6588′;

SPID

54685

SQL>

Swim into the page directory/u01 / app/oracle/diag/RDBMS/former/former/trace can discover, this trace file contains 54685 did not exist, the reason is to use the trace files need to manually open session tracking.

6. Enable session tracking

SQL> alter session set sql_trace=true;

Session altered.

SQL>

7, perform a simple SQL statement, and check the www.sangpi.com/u01/app/oracle/diag/rdbms/orcl/orcl/trace directory, can see the trace file generated at this moment.

[oracle@oracle12c trace]$ pwd

/u01/app/oracle/diag/rdbms/orcl/orcl/trace

[oracle@oracle12c trace]$ ls *54685.trc

orcl_ora_54685.trc

[oracle@oracle12c trace]$

SQL > execute the following SQL statement

select * from scott.emp where deptno=10;

select * from scott.emp where deptno=20;

select * from scott.emp where deptno=30;

9. Since session tracking was enabled previously, if it is no longer needed, you need to manually close it.

SQL> alter session set sql_trace=false;

Session altered.

SQL>

10. Use tkprof tool to format the trace file

[oracle@oracle12c trace]$ tkprof orcl_ora_54685.trc /home/oracle/a.txt sys=no sort=fchela

TKPROF: Release 12.2.0.1.0 – Development on Mon Jun 28 10:37:48 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

[oracle@oracle12c trace]$

11. Check the generated A.TXT page game file, as follows:

SQL ID: 1mvxd868z75nf Plan Hash: 3956160932

select *

from scott.emp where deptno=30

SQL ID: 2nbac4n9hnzth Plan Hash: 3956160932

select *

from scott.emp where deptno=20

SQL ID: 062r5atccuyv4 Plan Hash: 3956160932

select *

from scott.emp where deptno=10