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;