There are many scenarios, DBA struggling to identify the sessions running in database on sudden adhoc request. To capture the session quickly with the information what we get is really easy if you ask below questions.
Ask below questions to the team who asks to find the session.
- Machine name where that application hosts
- Start time of that session in that application
- SQL text
STEP1:
Using the above information, you can quickly find a session running in the database. GV$Session have ‘MACHINE’ column, ‘LOGON_TIME’ column where you can use the basic information to filter the rows first.
Then, note the ‘SQLID’ column in GV$session for the rows it returned.
STEP2:
Using the sql text, you can find sql id using below query.
Select sql_id,sql_text from gv$sql_text where upper(‘%SQL_TEXT%’);
The reason using UPPER function is sometimes the query may running INITCAP, smallcase letters and using UPPER function will align with the filter criteria.
Now, with returned SQL_ID, match that with GV$session and you will get all details in your hand.
Happy Debugging!!!
