Noveljic Nenad
2017-12-12 15:02:52 UTC
Has somebody tried tracing a specific sql_id in Oracle 12.2? The feature doesn't seem to work properly.
Here's the test case:
Session 1:
alter system set events='sql_trace[sql: 2asugy1n1r7y7] level=12' ;
SQL> alter system set events='sql_trace[sql: 2asugy1n1r7y7] level=12' ;
System altered.
SQL> quit
Session 2:
connect / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug eventdump session
sql_trace[sql: 2asugy1n1r7y7] level=12
SQL> select count(*) from v$sql where sql_id='2asugy1n1r7y7';
COUNT(*)
----------
0
SQL> select /* test_trace */ 1 from dual ;
1
----------
1
SQL> select count(*) from v$sql where sql_id='2asugy1n1r7y7';
COUNT(*)
----------
1
Unlike 12.1, the SQL doesn't get traced on a 12.2 database unless ran in the same session where the "alter system set events" was executed .
Nenad
Twitter:@NenadNoveljic
Home page: http://nenadnoveljic.com/blog/
____________________________________________________
Please consider the environment before printing this e-mail.
Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">p { font-family: Arial;font-size:9pt }</style>
</head>
<body>
<p>
<br>Important Notice</br>
<br>This message is intended only for the individual named. It may contain confidential or privileged information. If you are not the named addressee you should in particular not disseminate, distribute, modify or copy this e-mail. Please notify the sender immediately by e-mail, if you have received this message by mistake and delete it from your system.</br>
<br>E-mail transmission may not be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also processing of incoming e-mails cannot be guaranteed. All liability of the Vontobel Group and its affiliates for any damages resulting from e-mail use is excluded. You are advised that urgent and time sensitive messages should not be sent by e-mail and if verification is required please request a printed version.<br/>
</p>
</body>
</html>
Here's the test case:
Session 1:
alter system set events='sql_trace[sql: 2asugy1n1r7y7] level=12' ;
SQL> alter system set events='sql_trace[sql: 2asugy1n1r7y7] level=12' ;
System altered.
SQL> quit
Session 2:
connect / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug eventdump session
sql_trace[sql: 2asugy1n1r7y7] level=12
SQL> select count(*) from v$sql where sql_id='2asugy1n1r7y7';
COUNT(*)
----------
0
SQL> select /* test_trace */ 1 from dual ;
1
----------
1
SQL> select count(*) from v$sql where sql_id='2asugy1n1r7y7';
COUNT(*)
----------
1
Unlike 12.1, the SQL doesn't get traced on a 12.2 database unless ran in the same session where the "alter system set events" was executed .
Nenad
Twitter:@NenadNoveljic
Home page: http://nenadnoveljic.com/blog/
____________________________________________________
Please consider the environment before printing this e-mail.
Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">p { font-family: Arial;font-size:9pt }</style>
</head>
<body>
<p>
<br>Important Notice</br>
<br>This message is intended only for the individual named. It may contain confidential or privileged information. If you are not the named addressee you should in particular not disseminate, distribute, modify or copy this e-mail. Please notify the sender immediately by e-mail, if you have received this message by mistake and delete it from your system.</br>
<br>E-mail transmission may not be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also processing of incoming e-mails cannot be guaranteed. All liability of the Vontobel Group and its affiliates for any damages resulting from e-mail use is excluded. You are advised that urgent and time sensitive messages should not be sent by e-mail and if verification is required please request a printed version.<br/>
</p>
</body>
</html>