Oracle Database 23AI session-related scripts.

 

Oracle Database 23AI session-related scripts.

##Columns Name Details###

sid - session identifier

serial# - session serial number

osuser - operating system client user name

machine - operating system machine name

program - operating system program name

module - name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure

------------------------------

select * from v$session;

------------------------------

select SID,SERIAL#,USERNAME,STATUS,SCHEMANAME,OSUSER,MACHINE from v$session where USERNAME='SYSTEM';

---------------

select SID,SERIAL#,USERNAME,STATUS,SCHEMANAME,OSUSER,MACHINE from v$session where STATUS='ACTIVE';

---------------------

select sid,

      serial#,

      osuser,

      machine,

      program,

      module

from v$session;

-----------------------

Note- Sometimes need to find session details. 

You can change the last line to search for sessions that are running specific queries. 

Replace ALTER TABLE%SHRINK% with another command that you want to search.

SELECT

   SES.SID,

   SES.SERIAL# SER#,

   SES.PROCESS OS_ID,

   SES.STATUS,

   SQL.SQL_FULLTEXT

FROM 

   V$SESSION SES,

   V$SQL SQL,

   V$PROCESS PRC

WHERE

   SES.SQL_ID=SQL.SQL_ID AND

   SES.SQL_HASH_VALUE=SQL.HASH_VALUE AND 

   SES.PADDR=PRC.ADDR AND

   UPPER(SQL.SQL_FULLTEXT) LIKE UPPER('ALTER TABLE%SHRINK%');

   --------------------------

How to find sql text and session information history during a week.

select a.SAMPLE_TIME, a.SQL_OPNAME, a.SQL_EXEC_START, a.program, a.client_id, b.SQL_TEXT

from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b --v$sqltext b

where a.SQL_ID = b.SQL_ID

order by a.SQL_EXEC_START asc;

--------------------------