How to query Oracle user session information

How should I query the user session information in the Oracle database? The following describes in detail how to query Oracle user session information.
AD:
Oracle user sessions are expected to be well understood. The following describes how to query Oracle user session information for your reference. If you are interested, take a look.

Through the V $ SESSION view, you can query all Oracle user SESSION information:

SQL code
Select sid, logon_time, username, machine from v $ session;

The total number of Oracle user sessions opened by different users or hosts is calculated by grouping:

SQL code
Select username, machine, count (*) from v $ session group by username, machine;

User sessions can be terminated Based on SID and SERIAL:

SQL code
Alter system kill session 'v _ sid, v_serial # 'immediate;

The SID and SERIAL of the Oracle user SESSION # can be found in the V $ SESSION View:

SQL code
SQL> select sid, serial #, username, machine, status from v $ session where username like 'pcnsh % ';

Sid serial # USERNAME MACHINE STATUS
------------------------------------------------------------------------
366 14303 PCNSH197 client197.yourdomain.com INACTIVE
369 1745 PCNSH003 server009 INACTIVE
370 10165 PCNSH049 client049.yourdomain.com INACTIVE
371 18999 PCNSH056 client056 INACTIVE
372 6207 PCNSH056 client056 INACTIVE
373 7688 PCNSH197 client197.yourdomain.com INACTIVE
374 19246 PCNSH003 server009 INACTIVE
377 17649 PCNSH003 server009 INACTIVE

Sessions terminated in Oracle will be cleared by pmon only when the process tries to connect to Oracle again. Before the process tries to connect again, you can still view the SESSION when querying the V $ SESSION view.

When the user session cannot respond, the above method may not be able to terminate the user session, so you have to directly kill the system process (Exercise caution ).

The system PROCESS corresponding to a user SESSION can be queried through the V $ SESSION and V $ PROCESS views, and the system PROCESS number can be queried through the session sid and SERIAL.

SQL code
SQL> select p. spid from v $ session s, v $ process p
Where p. addr = s. paddr and s. sid = 518 and s. serial # = 41831;

SPID
------------
16782

Then, kill the process in the operating system:

SQL code
Kill-9 16782

Some software opens multiple sessions at startup. When the software unexpectedly exits, terminate these sessions in batches. The stored procedure terminates sessions in batches based on user name conditions. The creation script kill_user. SQL is as follows:

SQL code
Create or replace procedure kill_user (v_name in varchar2)
As
Message varchar2 (50 );
Cursor loguser is select sid, serial # from v $ session where username = upper (v_name );
V_info loguser % rowtype;
SQL _mgr varchar2 (1000 );
I number default 0;
Begin
Open loguser;
Loop
Fetch loguser into v_info;
Exit when loguser % notfound;
I: = 1;
SQL _mgr: = 'alter system kill session ''' | v_info.sid | ',' | v_info.serial # | ''' immediate ';
Execute immediate SQL _mgr;
Dbms_output.put_line ('all the sessions of '| v_name | 'have been killed ');
End loop;
Close loguser;
If I = 0 then
Raise_application_error (-20004, 'the user' | v_name | 'is not login The database currently! ');
End if;
Exception
When no_data_found then
Raise_application_error (-20004, 'the user' | v_name | 'is not login The database currently! ');
End;
/

The following describes how to terminate all processes of a user by calling the preceding stored procedure:

SQL code
SQL> execute kill_user ('user _ name ');

Author: "fighter plane"