Making a 10046 event is really troublesome

-- Obtain the current session ID under PL/SQL delover command
Select sys_context ('userenv', 'sid') Sid from dual; -- start the 10046 trail
Alter session set events '2014 trace name context forever, level 12'; -- declare
Type type_owner is table of zengfankun_temp01.owner % Type Index by binary_integer;
Type type_object_name is table of zengfankun_temp01.object_name % Type Index by binary_integer;
Type type_object_id is table of zengfankun_temp01.object_id % Type Index by binary_integer;
Type type_object_type is table of zengfankun_temp01.object_type % Type Index by binary_integer;
Type type_last_ddl_time is table of zengfankun_temp01.last_ddl_time % Type Index by binary_integer;

Rochelle ary_owner type_owner;
L_ary_object_name type_object_name;
L_ary_object_id type_object_id;
L_ary_object_type type_object_type;
L_ary_last_ddl_time type_last_ddl_time; cursor cur_object is
Select owner, object_name, object_id, object_type, last_ddl_time
From zengfankun_temp01
Order by owner, object_name, object_type, last_ddl_time;
Begin
Open cur_object;
Loop
Fetch cur_object bulk collect
L_ary_owner,
Rochelle _ object_name,
L_ary_object_id,
L_ary_object_type,
L_ary_last_ddl_time
Limit 10000;
Exit when cur_object % notfound or cur_object % notfound is null;
End loop;
End; -- close the trace alter session set events '10046 trace name context off'; -- select rtrim (C. value, '/') | decode (E. plat_id, 1, '\', '/') |
D. instance_name | '_ ora _' | ltrim (to_char (A. spid) | '. trc' trace_file
From v $ process,
V $ session B,
V $ parameter C,
V $ instance D,
(Select case
When T. BANNER like '% WINDOWS %' then
1
When T. BANNER like '% Linux %' then
2
When T. BANNER like '% AIX %' then
3
When T. BANNER like '% Solaris %' then
4
End plat_id
From v $ version t
Where T. BANNER like '% TNS %') E
Where a. ADDR = B. paddr
And B. Sid = & p_sid
And C. Name = 'user _ dump_dest ';
-- Convert trctkprof D: \ oracle \ product \ 10.2.0 \ admin \ shark_olap \ udump \ shark_ora_16942.trc D: \ output10.txt

References:

In many cases, you can use SQL tracking to diagnose the performance of a database and record some information in the trace file for further analysis. In general, we can set SQL tracking by initializing the parameter SQL _trace = true.

We can also set 10046 events for SQL tracking, and set different trace levels to obtain more information than SQL _trace.

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.

10046 events not only track user sessions (the trace file is in user_dump_dest), but also the background process (the trace file is in background_dump_dest ). The size of the trace file depends on four factors: Trace Level, trace duration, session activity level, and max_dump_file_size.

Enable tracking event 10046

1. Global settings

Modify initialization parameters

Event = "10046 trace name context forever, Level 8"

2. Set in the current session

Alter session set events '2017 trace name context forever, level 8 ';

Alter session set events '10046 trace name context off ';

3. Set sessions for other users

First, obtain the session ID and serial number of the session to be tracked.

Select Sid, serial #, username from V $ session where username = 'trace _ username ';

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 );

Or

Exec dbms_system.set_ev (1234,567 89, 10046, 8 ,'');

Exec dbms_system.set_ev (1234,567 89, 10046, 0 ,'');

Or

Exec dbms_monitor.session_trace_enable (session_id => 1234, serial_num => 56789, Waits => true, binds => true );

Exec dbms_monitor.session_trace_disable (session_id => 1234, serial_num => 56789 );

You can also use the oradebug tool to set the 10046 event.

First, obtain the OS process ID of the session through V $ process.

Select S. username, 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 ('trace _ username ');

Oradebug setospid 12345;

Oradebug unlimit;

Oradebug event 10046 trace name context forever, Level 8;

Oradebug event 10046 trace name context off;

Obtain a trail File

1. Use oradebug

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/Opt/oracle/product/9.2.0/rdbms/log/uxdb_ora_9183.trc

2. Set the initial parameter TRACEFILE_IDENTIFIER.

Alter session set tracefile_identifier = 'mytrack ';

In this way, the generated trace file name will contain the words MyTrace.

/Opt/oracle/product/9.2.0/rdbms/log/uxdb_ora_9183_MyTrace.trc

3. query through SQL

Select d. value | '/' | lower (rtrim (I. instance, chr (0) | '_ ora _' | p. spid | '. trc 'trace_file_name

From

(Select p. spid

From sys. v $ mystat m, sys. v $ session s, sys. v $ process p

Where m. statistic # = 1 and s. sid = m. sid and p. addr = s. paddr) p,

(Select t. instance from sys. v $ thread t, sys. v $ parameter v

Where v. name = 'thread' and (v. value = 0 or t. thread # = to_number (v. value) I,

(Select value from sys. v $ parameter where name = 'user _ dump_dest ') d;

? /Rdbms/log/uxdb_ora_9183.trc

View the tracking level of the current session

SQL> Set serveroutput on

SQL> declare

Event_level number;

Begin

Dbms_system.read_ev (10046, event_level );

Dbms_output.put_line (to_char (event_level ));

End;

/