When will oracle recycle the process of status='KILLED' in v$session
This article shows you when oracle recovers the status='KILLED' process in v$session. The content is concise and easy to understand. It will definitely make your eyes shine. I hope you can gain something through the detailed introduction of this article.
When does oracle recycle processes with status='KILLED' in v$session, some processes are killed and never recycled
A session needs to release the resources it uses, including locks, rollback segments, etc. If the session has been running for a long time, killing the session can be a very time-consuming process.
View the number of rollback segment blocks currently used by the session
SELECT USED_UBLK FROM V$TRANSACTIOn a ,v$session b where a.addr=b.taddr;
You can look at the number of rolls back.
1. Determine if you are rolling back
select * from V$TRANSACTIOn where bitand(flag,power(2,7))>0
The flag field value may identify the transaction type
select to_char( 7811, '0000000X' ), to_char( 7683, '0000000X') from dual;
TO_CHAR(7 TO_CHAR(7
--------- ---------
00001E83 00001E03
^ ^
0 indicates "normal user transaction"
8 indicates "rollback,most likely - means no more changes and you cannot commit"
2. Rollback amount: UBABLK field
3. View rollback time
col td format a40
col es format 999,999
col totalwork format 999,999
col units format a10
SELECT decode (target_desc,NULL,decode(target,NULL,opname, concat(opname, concat (' - ',target))),decode (target,NULL,concat(opname, concat (' : ',target_desc)),concat (opname, concat (' :',concat(target_desc,concat (' - ',target)))))) td,sofar,totalwork,units,start_time,TO_CHAR (elapsed_seconds,'9999990.00'), decode(sofar, 0, 0, round(elapsed_seconds*(totalwork-sofar)/sofar)) Time left
FROM v$session_longops
WHERE sid = &v_sid
AND serial# =&v_serial#
and sofar
< totalWork D SOFAR TOTALWORK UNITS START_TIME TO_CHAR(ELAPSED_SECONDS,'99999 剩下的时间 ------------------------------------ ----- --------- -------- ----------- ------------------------------ ---------- Table Scan - SINO.ACC_POENTRYD 16099 16099 Blocks 2005-4-15 1 8.00 0查看剩下的时间 4.你使用了alter session kill一个死进程，可能当时只是把session标识了killed，而v$process还存在，你可以用下面方法解决 UNIX平台 SQL>SELECT spid
FROM v$process
WHERE NOT EXISTS ( SELECT 1
FROM v$session
WHERE paddr = addr);
% kill
WINDOWS：
select p.spid "OS Thread", b.name "Name-User", s.osuser,
s.program
from v$process p, v$session s, v$bgprocess b
where p.addr = s.paddr
and p.addr = b.paddr
UNION ALL
select p.spid "OS Thread", s.username "Name-User",
s.osuser, s.program
from v$process p, v$session s
where p.addr = s.paddr
and s.username is not null; use orakill if
SQL> SELECT spid, osuser, s.program,sid
FROM v$process p, v$session s
WHERE p.addr=s.paddr;
SPID OSUSER PROGRAM SID
------------------------ -------------------- --------------- ----------
6484 lifeng.fang sqlplus.exe 11
SQL> host orakill charset 6484;
Kill of thread id 6484 in instance charset successfully signalled.
5. You can add dcd in sqlnet.ora
The above content is when oracle recovers the status='KILLED' process in v$session. Have you learned knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.
