Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Track an SQL_ID to the code that executed it

682560Aug 21 2024

This post is from 2009, and related, but not really helpful with resolution: https://forums.oracle.com/ords/apexds/post/can-you-capture-sql-id-from-the-jdbc-connection-0611

We have a large monolithic Oracle instance that is accessed by many different clients/microservices. We have issues at times where a query is identified by an SQL_ID as problematic for some reason.

It would be INCREDIBLY useful and much more efficient in the process of diagnosing issues if there was a way to log the SQL_ID from the client/microservice. Once the problematic SQL_ID was identified, it could be found in the logs, which would immediately lead the person doing the research to the client(s)/microservice(s) that executed the code.

Has anyone had success with an approach for this? In our case, we are using configuration driven services (SpringBoot). Is it possible (without a performance impact) to get the SQL_ID after (or even before) an execution of a statement/stored procedure?

Ideally, I would like to have some way to get the SQL_ID from the ResultSetMetaData or the OracleResultSetMetaData.

Comments

Add EVENT_10842=15

You can also set this as an environment variable with the same name & value.

Sheeraz Majeed May 10 2024 — edited on May 10 2024

Hi, Thanks for reply.

But still no trace or log files to check used oci functions

Works for me.

cjones@cjones-mac:~$ rm -rf $HOME/instantclient/log/diag/clients && mkdir -p $HOME/instantclient/log/diag/clients
cjones@cjones-mac:~$ export EVENT_10842=15
cjones@cjones-mac:~$ sqlplus cj/cj@localhost/orclpdb1

...

SQL> select * from dual;

D
-
X

SQL> exit

And then a file like /Users/cjones/instantclient/log/diag/clients/user_cjones/host_nnnnnn_nn/trace/ora_nnn_nnnnn.trc will contain lines like:

# 2024-06-06 07:26:54.520 # Thread ID 8547126272 # Entry - OCIServerAttach(srvhp = 0x128030068, errhp = 0x13580a410, dblink_hash = H:0x9bf0f8e9ee161fdf, mode = OCI_DEFAULT(000000000), dblink = localhost/orclpdb1, dblink_len = 18 );
1 - 3

Post Details

Added on Aug 21 2024
1 comment
130 views