Kilitlenmiş Objeler

Oracle , Oracle Performance Tuning Kilitlenmiş Objeler

Kilitlenmiş Objeler

Kilitlenmiş objeler ya da diğer adıyla locked objects

Select sid,serial#,taddr,username,osuser,lockwait,status,machine,SQL_ADDRESS,SQL_ID,PROCESS
from v$session where lockwait is not null order by SID;

1 2	Select sid,serial#,taddr,username,osuser,lockwait,status,machine,SQL_ADDRESS,SQL_ID,PROCESS from v$session where lockwait is not null order by SID;

Alter system kill session ‘sid numarası,serial# numarası’;

1	Alter system kill session ‘sid numarası,serial# numarası’;

select ‘Alter system kill session ”’||sid||’,’||serial#||”’;’
from v$session where lockwait is not null order by SID;

1 2	select ‘Alter system kill session ”’\|\|sid\|\|’,’\|\|serial#\|\|”’;’ from v$session where lockwait is not null order by SID;

select s.sid, s.serial#, p.spid
from
v$session s,
v$process p
where
s.paddr = p.addr
and
s.sid in (select SESSION_ID from v$locked_object);

select s.sid, s.serial#, p.spid

from

v$session s,

v$process p

where

s.paddr = p.addr

and

s.sid in (select SESSION_ID from v$locked_object);

kilitlenenin ve kilitleyenin sid&serial ını alıyoruz.

select sid, serial#
from v$session
where sid IN (select holding_session from dba_waiters);

select sid, serial#

from v$session

where sid IN (select holding_session from dba_waiters);

Kilit bekleyen Kullanıcıları bul ve kill immediate

Select ‘alter system kill session ”’||sid||’,’||serial#||”’ immediate ;’ from v$session where lockwait is not null order by osuser;</div>
Kitlenmelere bakılacak view
[crayon-68076bfcc69eb397333518]select * from dba_blockers
select * from dba_waiters (wating_session, holding_session)

Select ‘alter system kill session ”’||sid||’,’||serial#||”’ immediate ;’ from v$session where lockwait is not null order by osuser;</div>

Kitlenmelere bakılacak view

[crayon-68076bfcc69eb397333518]select * from dba_blockers

select * from dba_waiters (wating_session, holding_session)

select l1.sid, ‘ IS BLOCKING ‘, l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2

select l1.sid, ‘ IS BLOCKING ‘, l2.sid

from v$lock l1, v$lock l2

where l1.block =1 and l2.request > 0

and l1.id1=l2.id1

and l1.id2=l2.id2

select s1.username || ‘@’ || s1.machine
|| ‘ ( SID=’ || s1.sid || ‘ ) is blocking ‘
|| s2.username || ‘@’ || s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

select s1.username || ‘@’ || s1.machine

|| ‘ ( SID=’ || s1.sid || ‘ ) is blocking ‘

|| s2.username || ‘@’ || s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status

from v$lock l1, v$session s1, v$lock l2, v$session s2

where s1.sid=l1.sid and s2.sid=l2.sid

and l1.BLOCK=1 and l2.request > 0

and l1.id1 = l2.id1

and l2.id2 = l2.id2 ;

select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
2* from v$session where sid=479 ;

1 2	select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# 2* from v$session where sid=479 ;

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != ‘BACKGROUND’;

SET LINESIZE 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id,

s.sid,

s.serial#,

p.spid,

s.username,

s.program

FROM gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE s.type != ‘BACKGROUND’;

ALTER SYSTEM KILL SESSION ‘115,15268’ immediate;

1	ALTER SYSTEM KILL SESSION ‘115,15268’ immediate;

ALTER SYSTEM KILL SESSION ‘sid,serial#,@inst_id’;

1	ALTER SYSTEM KILL SESSION ‘sid,serial#,@inst_id’;

ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;

1	ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;

ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ POST_TRANSACTION;

1	ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ POST_TRANSACTION;

ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ IMMEDIATE;

1	ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ IMMEDIATE;

alter system disconnect session ‘30,7’;

1	alter system disconnect session ‘30,7’;

orakill ORACLE_SID spid
kill -9 spid
ps -ef | grep ora

orakill ORACLE_SID spid

kill -9 spid

ps -ef | grep ora

vmstat 2

vmstat 2

sar -u 2 5
sar -P ALL
sar -d 2
sar -d | grep dev3
sar -r -n DEV -f /var/log/sa/sa16 — sa16 dosyasına yaz
sar -n DEV | more

sar -u 2 5

sar -P ALL

sar -d 2

sar -d | grep dev3

sar -r -n DEV -f /var/log/sa/sa16 — sa16 dosyasına yaz

sar -n DEV | more

V_$LOCKED_OBJECT view’ında takılı kalan kaydın process numarası bulunabilir.

Aşağıdaki şekilde v$session view’ından sorgulanır.

Select sid,serial#,taddr,username,osuser,lockwait,status,machine,SQL_ADDRESS,SQL_ID,PROCESS
from v$session where process=’896:904′ order by osuser;

1 2	Select sid,serial#,taddr,username,osuser,lockwait,status,machine,SQL_ADDRESS,SQL_ID,PROCESS from v$session where process=’896:904′ order by osuser;

SQL_ADDRESS’ten sorgusu kontrol edilip

Alter system kill session ‘sid numarası,serial# numarası’ immediate;

1	Alter system kill session ‘sid numarası,serial# numarası’ immediate;

ile oturum durdurulur eğer bu işletm cvp vermiyorsa . işletim sistemi tarafından o kullanıcıya ait işlem sonlandırılabilir.
aşağıdaki sorgu ile işletim sistemindeki process id bulunur.

select
sid,
spid
from v$process p, v$session s
where paddr = addr
and s.sid in(XXXX)
order by s.sid;

select

sid,

spid

from v$process p, v$session s

where paddr = addr

and s.sid in(XXXX)

order by s.sid;

root ile sisteme bağlanıp .

#kill -9 spid

1	#kill -9 spid

Yazar

sysdba.org