Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.