Kill session method in oracle

How to find the number of tables locked in oracle:
 
Select B. owner table_owner, B. object_name, c. username, c. sid, c. serial #
From v $ locked_object a, dba_objects B, v $ session c
Where a. object_id = B. object_id and c. sid = a. session_id;
In this way, based on sid and serial #, you can directly kill them (the specific method is described below)
 
Query whether a table is locked. For example, the temporary table SHZGY. SHZGY_PZ_BB_ERROR1

Select object_id, owner | '.' | object_name
From dba_objects
Where owner = 'shzgy'
And object_name like 'shzgy _ PZ_BB_ERROR1 ';
OBJECT_ID OBJECT_NAME
--------------------------------------------
105421 SHZGY. SHZGY_PZ_BB_ERROR1
Find the id1 of the table.
Select * from v $ lock where id1 = 105421;
Addr kaddr sid ty ID1 ID2 LMODE REQUEST CTIME BLOCK
----------------------------------------------------------------------------------------
A08E1304 A08E1314 29 TO 105421 1 3 0 1209396 0
A08D4904 A08D4914 30 TO 105421 1 3 0 1295439 0
A08E1188 A08E1198 32 TO 105421 1 3 0 1284027 0
A08E12B8 A08E12C8 77 TO 105421 1 3 0 1209674 0
A08E11D4 A08E11E4 120 TO 105421 1 3 0 1280668 0
A08E1350 A08E1360 144 TO 105421 1 3 0 1209330 0
A08E1220 A08E1230 151 TO 105421 1 3 0 1279593 0
You have selected 7 rows.
Seven sessions lock the temporary table.
 
If you can kill, you can drop table shzgy. shzgy_pz_bb_error1;
Select a. sid, a. SERIAL #, B. spid, a. status, a. PROGRAM
From v $ session a, V $ PROCESS B
Where a. sid in (29, 30, 32, 77,120,144,151)
And a. paddr = B. ADDR
Order by a. sid;

Sid serial # SPID STATUS PROGRAM
----------------------------------------------------------------
29 619 9738 inactive jdbc Thin Client
30 47 7608 inactive jdbc Thin Client
32 148 8014 inactive jdbc Thin Client
77 198 9736 inactive jdbc Thin Client
120 258 8204 inactive jdbc Thin Client
144 17 9762 inactive jdbc Thin Client
151 253 8232 inactive jdbc Thin Client
All seven sessions are JDBC calls, and status = INACTIVE.
Then, based on the call and activity status, determine whether these sessions can be killed.
If you can, kill these sessions:
Use the dbms_system.set_ SQL _trace_in_session package to trace this session:
SQL> alter system kill session 'sid, SERIAL # 'immediate;
In this case, the session status is marked as killed, and Oracle will clear the process during the next touch of the user.
We found that when a session is killed, the paddr of the session is modified. If multiple sessions are killed, the paddr of multiple sessions is changed to the same process address:
SQL> select saddr, sid, serial #, paddr, username, status from v $ session where username is not null;
Saddr sid serial # PADDR USERNAME STATUS
--------------------------------------------------------------------------
542E0E6C 11 314 542B70E8 EYGLE INACTIVE
542E5044 18 662 542B6D38 SYS ACTIVE
SQL> alter system kill session '123 ';
System altered.
SQL> select saddr, sid, serial #, paddr, username, status from v $ session where username is not null;
Saddr sid serial # PADDR USERNAME STATUS
--------------------------------------------------------------------------
542E0E6C 11 314 542D6BD4 EYGLE KILLED
542E5044 18 662 542B6D38 SYS ACTIVE
SQL> select saddr, sid, serial #, paddr, username, status from v $ session where username is not null;
Saddr sid serial # PADDR USERNAME STATUS
--------------------------------------------------------------------------
542E0E6C 11 314 542D6BD4 EYGLE KILLED
542E2AA4 14 397 542B7498 EQSP INACTIVE
542E5044 18 662 542B6D38 SYS ACTIVE
SQL> alter system kill session '123456'; System altered.
SQL> select saddr, sid, serial #, paddr, username, status from v $ session where username is not null;
Saddr sid serial # PADDR USERNAME STATUS
--------------------------------------------------------------------------
542E0E6C 11 314 542D6BD4 EYGLE KILLED
542E2AA4 14 397 542D6BD4 EQSP KILLED
542E5044 18 662 542B6D38 SYS ACTIVE
In this case, resources cannot be released. We need to query spids and kill these processes at the operating system level.
 
Method: A session corresponds to a process in the operating system. We do not use Alter system kill session instead, when the background process of the session is killed, it will prompt the lazy Pmon process to quickly clean up.
Using a session as an example,
A. Find the session you want to kill and write down paddr.
B. Find the spid corresponding to this session.
C. Kill the process identified by the spid.
♀If your Oracle is on a Unix platform, you can use kill.
$ Killed 13824
♀If your Oracle is on the mongown platform, there are some differences, because mongown uses thead to replace process, the sid and spid values need to be used, the command used is also replaced by kill with Orakill, in the format of orakill sid spid.
C: \> orakill 941 13824
D. Check v $ session again to see if the session is absent.

 

This article is from "pursuit"