OracleDiagnostics

Trace another session with waits and binds

Prompts for a SID and serial number, turns on extended SQL trace, and shows the trace file name. Run the disable line when you've captured enough, then format the file with tkprof.

Changes dataPrompts for input

Not yet verified. How scripts are tested

This script changes the database. Check the names and values before you run it.

ora-trace-session.sql

1SET VERIFY OFF LINESIZE 2002COLUMN tracefile FORMAT A1203 4EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => &&sid, serial_num => &&serial, waits => TRUE, binds => TRUE);5 6SELECT p.tracefile7FROM v$session s8JOIN v$process p ON p.addr = s.paddr9WHERE s.sid = &&sid;10 11-- When you have enough, run this in the same SQL*Plus session:12-- EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => &&sid, serial_num => &&serial);13-- UNDEFINE sid serial

Save it as ora-trace-session.sql and run it with SQL> @ora-trace-session.

Open in denrepo

More Oracle scripts: Diagnostics

Alert log errors in the last 24 hoursORA- and TNS- errors plus checkpoint warnings from the alert log, read through SQL. It can be slow when the alert log is very large.
Where the alert log and trace files areThe ADR home, alert log directory, trace directory and this session's own trace file.
Review problems and incidents (ADRCI)ADRCI commands for critical errors like ORA-00600 and ORA-07445, and for packaging an incident to send to Oracle Support.