How to enable trace for an already running database session?

There are many cases when we are troubleshooting a long running database session.

We may want to enable trace for the already running session to identify what is causing it to run such long.

We will need to first find the SPID for the session we want to enable trace.

Query the spid from v$process using the addr from v$session for the SID.

Once you have the SPID, use the below commands to enable trace for it.

sqlplus "/ as sysdba"

oradebug setospid [SPID]

oradebug unlimit

oradebug dump errorstack 3

The above commands will generate the trace for the session. We can identify the exact name of trace file from v$process.