As a DBA, we might need to provide details of sessions to Application Team. We can fetch the sid, sql_id from v$session and get some information. Today we will learn how to retrieve machine details of a sql_id in Oracle. Let’s go through in detail :
Retrieve machine details of a sql_id in Oracle :
Suppose our sql_is is 68nmb5zbr8fxy, So replace the sql_id in the below queries one by one and you can get outputs.
From v$session :
select machine,username,count(*) from gv$session where sql_id='68nmb5zbr8fxy' group by username,machine order by 2;
From Active Session History :
select sql_id,machine,module,count(*) from gv$active_session_history where sql_id='68nmb5zbr8fxy' group by sql_id,machine,module order by 2;
From Active Session History during a particular Time :
Replace the time duration as per your requirement :
set lin 300 pages 400
col username for a30
select sql_id,event,machine,count(*) from v$active_session_history where sql_id='68nmb5zbr8fxy' and sample_time between to_date('14-NOV-2024 02:56:15','DD-MON-YYYY HH24:MI:SS') and to_date('14-NOV-2024 02:57:54','DD-MON-YYYY HH24:MI:SS') group by sql_id,machine,event order by 4;
So, here are the steps that can help to retrieve machine details of a sql_id in Oracle. Hope this helps!! Also, you can get reference from the Oracle Doc as well :
Other Oracle Related Articles :