In this post I am going to help you to identify the trace file name for the
current oracle session.
Method 1.
Using v$diag_info table
Trace Current Oracle Session – Different Methods
Method 1.
Using alter session command
ALTER SESSION SET sql_trace=TRUE; ALTER SESSION SET sql_trace=FALSE;
SQL Query to Find Blocking Sessions and SQL Text for Last One Day from History
In this post I wanted to give you one sql script to find blocking sessions for the last one day from the history.
SQL Query
set pages 1000 lines 222col sql_id for a17
col inst_id for '99'
col sql_text for a60SQL Query to Find SQLIDs Spend on CPU/Wait/IO
This query is used to find out the SQLIDs spend on the CPU, Wait and IO.
SQL Query
Script to Find Most Active Session in Oracle Database for Last One Hour
These SQLs to identify most active sessions in the oracle database for last one hour. Two queries mentioned here for the background and foreground processes.
SQL Query to Find I/O Intensive SQL and SQLText in Last One Hour
This can be used for finding out most CPU intensive query in last one hour. There are 3 data dictionary views involved in this query.
gv$active_session_history
gv$event_name
v$sqlarea
SQL Query to Find Out Oracle Session Details for a Past Time Period from the History tables
There are the 3 views can be used to achieve this goal. The columns are almost similar to the V$SESSION table except snap_id, sample_id, sample_time etc.. The AWR,
SQL Query to Find Out Pending Distributed Transaction in Oracle Database
A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database.
