/
locked
*query locked object and analyse reason,kill it

桓臺(tái)網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)建站！從網(wǎng)頁設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、成都響應(yīng)式網(wǎng)站建設(shè)公司等網(wǎng)站項(xiàng)目制作，到程序開發(fā)，運(yùn)營維護(hù)。創(chuàng)新互聯(lián)建站自2013年起到現(xiàn)在10年的時(shí)間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來保證我們的工作的順利進(jìn)行。專注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)建站。

• */

select 'alter system kill session ''' || SID || ',' || SERIAL# || ''';'
from (select distinct a.sid,
a.Serial#,
status,
machine,
LOCKWAIT,
logon_time
from v$session a, v$locked_object b
where (a.status = 'ACTIVE' or a.status = 'INACTIVE')
and a.sid = b.session_id
and b.ORACLE_USERNAME = 'XYHISTEST' --加上用戶名可過濾用戶資源
);

/2、批量執(zhí)行第一步生成的語句
alter system kill session 'sid,serial#';
alter system kill session '6976,33967';/

/3、查詢oracle用戶名，機(jī)器名，鎖表對象/
SELECT l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
l.os_user_name,
s.machine,
s.terminal,
o.object_name,
s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial#;

/3.或者/
select s.SID, s.OSUSER, p.spid as OSPID, s.MACHINE, s.TERMINAL, s.PROGRAM
from v$session s, v$process p
where s.sid = 6 --session_id
and s.paddr = p.addr;

/4、查詢是執(zhí)行何sql語句導(dǎo)致鎖表的/
select b.sql_text
from v$session a, v$sql b
where a.sid = 6 --session_id
and a.SQL_ADDRESS = b.ADDRESS(+);

select sql_text
from v$session a, v$sqltext_with_newlines b
where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value) =
b.hash_value
and a.sid = #149
order by piece;

/5、查詢是執(zhí)行何sql語句導(dǎo)致鎖表的/
SELECT l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
s.user#,
l.os_user_name,
s.machine,
s.terminal,
a.sql_text,
a.action
FROM v$sqlarea a, v$session s, v$locked_object l
WHERE l.session_id = s.sid
AND s.prev_sql_addr = a.address
ORDER BY sid, s.serial#;

/6、查看是否有鎖表的 SQL/
select 'blocker(' || lb.sid || ':' || sb.username || ')_sql:' ||
qb.sql_text blockers,
'waiter (' || lw.sid || ':' || sw.username || ')_sql:' ||
qw.sql_text waiters
from v$lock lb, v$lock lw, v$session sb, v$session sw, v$sql qb, v$sql qw
where lb.sid = sb.sid
and lw.sid = sw.sid
and sb.prev_sql_addr = qb.address
and sw.sql_address = qw.address
and lb.id1 = lw.id1
and sw.lockwait is not null
and sb.lockwait is null
and lb.block = 1;

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)scvps.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。

分享題目：Oracle查看鎖表情況并處理鎖表-創(chuàng)新互聯(lián)
URL地址：http://weahome.cn/article/despds.html