How to Find User Status, State, Sid, Serial# in Oracle Database

Hi, Guy's,

You can find the USER information like Status,State, SID, SERIAL# etc...which will also help you for killing sessions and also finding User-Machine locations.


For all the RAC-Database Users :-

SQL> set pagesize 50000
set linesize 5000
set long 50000
select username,status,state, to_char (logon_time,'dd-mon-yyyy hh24:mi:ss'),osuser,terminal,sid,serial#,inst_id,count(*) from gv$session
group  by  username,status,state,to_char (logon_time,'dd-mon-yyyy hh24:mi:ss'),osuser,terminal,sid,serial#,inst_id order by count(*);


For Specific Departments Users Whose Names are Starting From 'AC' :-

SQL> set pagesize 50000
set linesize 5000
set long 50000
select username,status,state, to_char (logon_time,'dd-mon-yyyy hh24:mi:ss'),osuser,terminal,sid,serial#,inst_id,count(*) from gv$session where username like 'AC%'
group  by  username,status,state,to_char (logon_time,'dd-mon-yyyy hh24:mi:ss'),osuser,terminal,sid,serial#,inst_id order by count(*);


For Specific User :-

SQL> select username,status,state, to_char (logon_time,'dd-mon-yyyy hh24:mi:ss'),osuser,terminal,sid,serial#,inst_id,count(*) from gv$session where username=('PHRASU')
     group  by  username,status,state,to_char (logon_time,'dd-mon-yyyy hh24:mi:ss'),osuser,terminal,sid,serial#,inst_id order by count(*);



Happy Learning....