SQL tracking methods

Oracle SQL tracking method:
1. SQL _trace
Enable tracking: Alter session set SQL _trace = true;
Mark the trace file: Alter session set tracefile_identifier = 'look _ for_me ';
Stop tracking: Alter session set SQL _trace = false;
You can use the tkprof tool to generate the last generated trace file.

2. tracking code 10046
Tracing sector introduction:
Level 0 disables SQL tracing, which is equivalent to SQL _trace = false
Level 1 standard SQL trace, equivalent to SQL _trace = true
Level 4 adds variable binding information based on Level 1.
Level 8 adds wait event information based on Level 1.
Level 12 adds variable binding and wait event information based on Level 1.

Open a trail:
Grant alter session to lttfm; -- must have the alter session permission
Alter session set events '2017 trace name context forever, level 12'; -- set by the current user
Or Insert the following row in the init. ora file: event = 10046 trace name context forever, Level 12; -- set global

Disable tracing:
Alter session set events '10046 trace name context off'; -- Disable User tracking

Note: SQL _trace and 10046 set the code tracing function to only trace Sessions of the current session or system level. You must use oradebug or
Dbms_system.set_ev or dbms_monitor.session_trace_enable.

3. Use oradebug
-- Use the following statement to find the spid to be tracked
Select * from V $ session a where audsid = userenv ('sessionid'); -- query the current sessionid
Select S. username,
S. osuser,
S. Sid,
S. paddr,
S. process,
P. spid OS _process_id,
P. PID oracle_process_id
From v $ session S, V $ PROCESS p
Where S. paddr = P. ADDR
And S. Username = upper ('ltwebgis ')
And S. Sid = 145;

-- Set the trail process ID.
SQL> connect/As sysdba
SQL> oradebug setospid 5672; this statement is used to track other sessions (5672 is the spid of V $ process ),

If this session is tracked, execute oradebug setmypid.
Or
SQL> connect/As sysdba
SQL> oradebug setorapid 15 -- the input 15 is the PID of V $ Process

-- Set unlimited tracking File Size
SQL> oradebug unlimit;
-- Set the trail, Level 8
SQL> oradebug event 10046 trace name context forever, Level 8;
Processed statements

-- Disable tracking
SQL> oradebug event 10046 trace name context off;

When you execute this statement (SQL> oradebug setospid 5672;), the message "ORA-01031: insufficient Permissions" appears ",
Cause: oradebug is a sysdba command (generally, the user prompts that the permission is insufficient), and is a SQL plus-specific command. It cannot be executed in PLSQL (otherwise, an invalid SQL prompt is displayed ).
You can use the oradebug HELP command to view the oradebug tool description.

4. dbms_system (must be executed by the Sys user)
Exec dbms_system.set_ev (SI => 159, Se => 254, ev => 10046, Le => 8, Nm => ''); -- the session ID and other parameters must be correctly set, otherwise, the trace cannot be generated.
After generation, you can use SQL statements to view the location of the trace file.

-- Stop a trail
Exec dbms_system.set_ev (SI => 159, Se => 254, ev => 10046, Le => 0, Nm => '');

5. dbms_monitor
Exec dbms_monitor.session_trace_enable (session_id => 136, serial_num => 4, Waits => true, binds => true );
Exec dbms_monitor.session_trace_disable (session_id = & gt; 136, serial_num = & gt; 4 );
If session_id is not set or is set to null, the current session is tracked.

6. dbms_support
Exec dbms_support.start_trace_in_session (SID => 1234, serial # => 56789, Waits => true, binds => true );
Exec dbms_support.stop_trace_in_session (SID => 1234, serial #=> 56789 );
Dbms_support by default, this package is not installed. If you want to use it, you must set it separately. It should exist in the $ ORACLE_HOME/rdbms/admin/directory
Dbmssupp. SQL and prvtsupp. PLB files can be used only after these two files are executed. If other users want to use them, they need to authorize them and create synonyms.

Note: The Tracing Methods 3, 4, 5, and 6 can only be executed under sys users, and any session can be tracked.

Obtain the trace file:
1) oradebug acquisition tracking File
-- Use oradebug to get the location of the current session trace file
SQL> oradebug setmypid
SQL> oradebug tracefile_name

-- Obtain the location of any session trace file
SQL> oradebug setospid 5392
Processed statements
SQL> oradebug tracefile_name
D: \ oracle \ product \ 10.2.0 \ admin \ fgisdb \ udump \ fgisdb_ora_5600.trc

2) obtain the trace file using SQL
-- SQL: view the location of the current session trace file
Select D. Value | '\' | Lower (rtrim (I. instance_name, CHR (0) | '_ ora _' |
P. spid | '. trc' trace_file_name
From (select P. spid
From v $ mystat M, V $ session S, V $ PROCESS p
Where M. Statistic # = 1
And S. Sid = M. Sid
And P. ADDR = S. paddr) P,
(Select instance_name from V $ instance) I,
(Select value from V $ parameter where name = 'user _ dump_dest ') D;

-- The following SQL statement is used to query the session trace file of the input spid.
Select D. Value | '\' | Lower (rtrim (I. instance_name, CHR (0) | '_ ora _' |
& Spid | '. trc' trace_file_name
From (select instance_name from V $ instance) I,
(Select value from V $ parameter where name = 'user _ dump_dest ') D;

View the trail level
1) view the tracking level of the current session (it must be executed under the Sys user)
Declare
Event_level number;
Begin
Dbms_system.read_ev (10046, event_level );
Dbms_output.put_line (to_char (event_level ));
End;

2) execute the following statement to view the tracking level of the tracking event.
SQL> oradebug setospid spid -- specify the spid AT THE TRAIL level
SQL> oradebug eventdump session
10046 trace name context Level 8, forever

Note: If the tracking session is enabled but the database is closed later, the tracking is automatically disabled.