How to Find out / Identify Trace File for the Current or Other Oracle User Session

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

set pages 1000 lines 222
col sql_id for a17
col inst_id for '99'
col sql_text for a60

SQL 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:-
set pages 1000 lines 220
col SQL_ID for a 15
select

SQL Query to Find Most Active Session in Oracle Database for Last One Hour

This 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 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. 
 

acehints.com Copyright 2011-20 All Rights Reserved | Site Map | Contact | Disclaimer