SQL Trace with filter by client_id and sql_id

sql_trace_filter_by_client_id.sql

[oracle@db-21 ~]$ sqlplus /nolog @q

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Dec 22 20:39:06 2021
Version 21.4.0.0.0

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

SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb;

Session altered.

SQL>
SQL> drop user tc1 cascade;

User dropped.

SQL>
SQL> grant create session, select_catalog_role to tc1 identified by tc1;

Grant succeeded.

SQL>
SQL> col sql_id old_v sql_id
SQL> select dbms_sql_translator.sql_id(q'#select 'trace_me' from dual#') sql_id from dual;

SQL_ID
--------------------------------------------------------------------------------
bqka14bvd2zmb

SQL>
SQL> alter system set events 'sql_trace[sql:&sql_id.] {streq:refs(refp(varaddr("ksmuh_p"), 16), 4888), "TRACE_ME"}';
old   1: alter system set events 'sql_trace[sql:&sql_id.] {streq:refs(refp(varaddr("ksmuh_p"), 16), 4888), "TRACE_ME"}'
new   1: alter system set events 'sql_trace[sql:bqka14bvd2zmb] {streq:refs(refp(varaddr("ksmuh_p"), 16), 4888), "TRACE_ME"}'

System altered.

SQL>
SQL> conn tc1/tc1@db-21/pdb
Connected.
SQL>
SQL> col trace_file old_v trace_file
SQL> select value trace_file from v$diag_info where name='Default Trace File';

TRACE_FILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_9073.trc

SQL>
SQL> select 'trace_me' from dual;

'TRACE_M
--------
trace_me

SQL>
SQL> !cat &trace_file.
cat: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_9073.trc: No such file or directory

SQL>
SQL> exec dbms_session.set_identifier('TRACE_ME')

PL/SQL procedure successfully completed.

SQL>
SQL> select 'trace_me' from dual;

'TRACE_M
--------
trace_me

SQL>
SQL> !cat &trace_file.
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_9073.trc
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.4.0.0.0
Build label:    RDBMS_21.3.0.0.0_LINUX.X64_210727
ORACLE_HOME:    /u01/app/oracle/product/21.0.0/dbhome_1
System name:    Linux
Node name:      db-21.localdomain
Release:        5.4.17-2136.300.7.el8uek.x86_64
Version:        #2 SMP Fri Oct 8 16:23:01 PDT 2021
Machine:        x86_64
VM name:        KVM CPUID feature flags: 0x01000089
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 48
Unix process pid: 9073, NID: 4026531836, image: oracle@db-21.localdomain


*** 2021-12-22T20:39:06.457697+00:00 (PDB(3))
*** SESSION ID:(54.55031) 2021-12-22T20:39:06.457770+00:00
*** CLIENT ID:(TRACE_ME) 2021-12-22T20:39:06.457794+00:00
*** SERVICE NAME:(pdb) 2021-12-22T20:39:06.457813+00:00
*** MODULE NAME:(SQL*Plus) 2021-12-22T20:39:06.457831+00:00
*** ACTION NAME:() 2021-12-22T20:39:06.457848+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-12-22T20:39:06.457864+00:00
*** CONTAINER ID:(3) 2021-12-22T20:39:06.457881+00:00
*** CLIENT IP:(10.0.2.15) 2021-12-22T20:39:06.457906+00:00
*** CONNECTION ID:(08Mc7swTI27gUw8CAApjHQ==) 2021-12-22T20:39:06.457924+00:00

=====================
PARSING IN CURSOR #139659713710176 len=27 dep=0 uid=129 oct=3 lid=129 tim=7392888570 hv=4140924523 ad='762c3898' sqlid='bqka14bvd2zmb'
select 'trace_me' from dual
END OF STMT
EXEC #139659713710176:c=23,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=7392888570
FETCH #139659713710176:c=6,e=6,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=7392889354
STAT #139659713710176 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 str=1 time=1 us cost=2 size=0 card=1)'
FETCH #139659713710176:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1388734953,tim=7392889773

SQL>
SQL> exec dbms_session.set_identifier('SOME_CLIENT')

PL/SQL procedure successfully completed.

SQL>
SQL> select 'trace_me' from dual;

'TRACE_M
--------
trace_me

SQL>
SQL> !cat &trace_file.
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_9073.trc
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.4.0.0.0
Build label:    RDBMS_21.3.0.0.0_LINUX.X64_210727
ORACLE_HOME:    /u01/app/oracle/product/21.0.0/dbhome_1
System name:    Linux
Node name:      db-21.localdomain
Release:        5.4.17-2136.300.7.el8uek.x86_64
Version:        #2 SMP Fri Oct 8 16:23:01 PDT 2021
Machine:        x86_64
VM name:        KVM CPUID feature flags: 0x01000089
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 48
Unix process pid: 9073, NID: 4026531836, image: oracle@db-21.localdomain


*** 2021-12-22T20:39:06.457697+00:00 (PDB(3))
*** SESSION ID:(54.55031) 2021-12-22T20:39:06.457770+00:00
*** CLIENT ID:(TRACE_ME) 2021-12-22T20:39:06.457794+00:00
*** SERVICE NAME:(pdb) 2021-12-22T20:39:06.457813+00:00
*** MODULE NAME:(SQL*Plus) 2021-12-22T20:39:06.457831+00:00
*** ACTION NAME:() 2021-12-22T20:39:06.457848+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-12-22T20:39:06.457864+00:00
*** CONTAINER ID:(3) 2021-12-22T20:39:06.457881+00:00
*** CLIENT IP:(10.0.2.15) 2021-12-22T20:39:06.457906+00:00
*** CONNECTION ID:(08Mc7swTI27gUw8CAApjHQ==) 2021-12-22T20:39:06.457924+00:00

=====================
PARSING IN CURSOR #139659713710176 len=27 dep=0 uid=129 oct=3 lid=129 tim=7392888570 hv=4140924523 ad='762c3898' sqlid='bqka14bvd2zmb'
select 'trace_me' from dual
END OF STMT
EXEC #139659713710176:c=23,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=7392888570
FETCH #139659713710176:c=6,e=6,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=7392889354
STAT #139659713710176 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 str=1 time=1 us cost=2 size=0 card=1)'
FETCH #139659713710176:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1388734953,tim=7392889773
CLOSE #139659713710176:c=7,e=7,dep=0,type=0,tim=7392895032

SQL>
SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb;

Session altered.

SQL>
SQL> alter system set events 'sql_trace off';

System altered.