How to get connected user session and the application name, who are using ?

As salamualikum (islamic greetings), brothers and sisters.

 Huhh, Post again after a long time. I'm passing very busy time. Lot's of change around me.

Come to the topic. And it's about database session issue.

Here we will learn how to get all the open database session (open by different application) and also the session for a particular USER.

By this information you will kill the session and also terminate any session forcefully and do you necessary works with the database.

connect database with the SYS/SYSTEM user. And run the bellow code.

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program,
       s.machine
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

it will show you all the open session for the database. If you need for a particular user, then run the bellow code.

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program,
       s.machine
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND'
AND s.username='db_user';

If you want to get just in a easy SQL, then run the bellow code,

select sid
           ,serial# 
from v$session 
where username = 'db_user';

Now, you get all the required information to terminate the specific session. How will you do this ?

You need to follow the following syntax.

alter system kill session '<sid>,<serial#>';

alter system kill session '39,1232';

You are done...!!!

Do you need to close all the session for a specific user ?

Then you will need a LOOP to close one by one. Follow the bellow example.

begin    
    for x in ( 
            select Sid, Serial#, machine, program 
            from v$session 
            where 
                username = 'db_user' 
        ) loop 
            execute immediate 'Alter System Kill Session '''|| x.Sid 
                     || ',' || x.Serial# || ''' IMMEDIATE';
            dbms_output.put_line('Connected Session Found from '||x.machine||', Via '||x.program);                   
        end loop; 
    dbms_output.put_line('Ops! No Connected Session Found.');
end;


End for the day....

Praise to Almighty Allah(swt).  :)

Fi amanillah