Script 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


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


Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2) Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer