Oracle 查询杀锁

–锁的概念数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。实现数据库并发控制的一个非常重要的技术DML锁分两种 1.表级锁（TM锁）:共享锁与独占锁 2.行级锁（TX锁）:共享更新锁DDL锁:修改表结构 --锁的视图：V$LOCK【系统锁】V$SESSION【会话锁】 VKaTeX parse error: Expected 'EOF', got '#' at position 282: …lect sid,serial#̲ from vsession where sid=43; SID SERIAL#---------- ---------- 43 125 SQL> alter system kill session ‘43,125’ immediate; ================================================================================

检查出产生死锁的机器session是哪一台。【如果有输出结果则说明有死锁】 select s.sid, s.serial#, s.username, s.lockwait, s.osuser, s.process, s.status, s.machine, s.program, s.event from gv$sessionswheres.sidin(selectl.sessio{n}_{i}dfromgv$locked_object l) and s.status=‘ACTIVE’; username： 死锁语句所用的数据库用户；lockwait： 死锁的状态，如果有内容表示被死锁。status： 状态active表示被死锁machine： 死锁语句所在的机器program： 产生死锁的语句主要来自哪个应用程序。 select inst_id, event#, event, count(*) from gvKaTeX parse error: Expected 'EOF', got '#' at position 25: …here wait_class#̲ <> 6 group by …sql t where t.hash_value in (select s.sql_hash_value from gv$sessionswheres.sidin(selectl.sessio{n}_{i}dfromgv$locked_object l));

================================================================================== set line 1000 pagesize 200 long 999999; col spid for a5;col sname for a10;col pname for a10;col machine for a25;col sprogram for a20;col program for a20;col event for a40; SELECT a.spid 操作系统进程, b.sid, b.serial#, b.username 数据库用户, b.status, b.program, b.paddr, b.machine 计算机名, b.program, b.module, b.logon_time, b.event, c.sql_text 正在执行的SQL FROM v$processa,v$session b, v$sqlarea c WHERE a.addr = b.paddr --AND b.sid= ? AND b.sql_hash_value = c.hash_value; --或者b.sql_id=c.sql_id 关联

================================================================================== --死锁的解决方法一般情况下，只要将产生死锁的语句提交就可以了，但是在实际的执行过程中。用户可能不知道产生死锁的语句是哪一句，可以将程序关闭并重新启动就可以了 1）查找死锁的进程： select l.session_id, s.serial#, l.process, l.LOCKED_MODE, s.username 数据库用户, l.oracle_username, l.os_user_name, s.status, s.program, s.paddr, s.machine 计算机名, s.module, s.logon_time, s.event from gv$locke{d}_{o}bjectl,gv$session s where l.session_id = s.sid; alter system kill session ‘session_id,serial#’; --kill掉这个死锁的进程------------------------------------------------------------------------------- select d.sid, d.serial#, b.object_name, a.*, c.spid from gv$locke{d}_{o}bjecta,db{a}_{o}bjectsb,gv$process c, gv$session d where a.object_id = b.OBJECT_ID and c.addr = d.PADDR and d.sid = a.session_id alter system kill session ‘830, 19785’; --杀sid与serial#

================================================================================ 【系统层查杀】：通过杀Linux进程号：注意： 应当注意对于 sid 在 100 以下的应当谨慎，可能该进程对应某个应用，如对应某个事务,可以杀 sql> select p.spid from v$sessions,v$process pwhere s.paddr=p.addr s.sid=[输入会话sid] ; [oracle@xxx ~]$ ps -ef|grep spid 其中spid是这个进程的进程号，kill掉这个oracle进程。 sql> !ps -ef|grep 3734oracle 3734 3733 0 17:22 ? 00:00:01 oracledb01 (description=(local=no)(address=(protocol=beq)));

================================================================================
–被占用的对象无法删除？先找出占用着的会话线程、再删 drop materialized view SJZBGT3.FACT_SKXX_RKSK_LJ; select ‘alter system kill session "’ || s.sid || ‘,’ || s.serial# || ‘" immediate;’, s.username, s.status, s.osuser, s.machine, s.process, s.program, s.sql_id, s.sql_exec_start, s.prev_sql_id, s.prev_exec_start, s.module, s.event from gv$sessionswheresidin(SELECTSIDFROMgv$access a WHERE a.OBJECT = ‘表名’ and a.OWNER = ‘用户’);

alter system kill session '4263,14764’immediate;

这段代码 可以找到，非系统锁的表，并给出 KILL SESSION语句。可以查到是那台机器，有什么程序锁的表SELECT OBJECT_NAME, SESSION_ID SID, MACHINE, VS.MODULE, ‘ALTER SYSTEM KILL SESSION ‘’’ || SESSION_ID || ‘, ’ || SERIAL# || ‘’’; ’ KILL_SESSION, VS.STATUS, VS.ACTION, SERIAL#, ORACLE_USERNAME, OS_USER_NAME FROM V$LOCKE{D}_{O}BJECTVO,V$SESSION VS, ALL_OBJECTS AO WHERE VO.SESSION_ID = VS.SID AND AO.OBJECT_ID = VO.OBJECT_ID AND NVL(VS.ACTION, ’ ') <> 'Service Management ’ ORDER BY OBJECT_NAME, MACHINE, VS.MODULE;
–查出所有被锁住的表
select b.owner TABLEOWNER, b.object_name TABLENAME, c.OSUSER LOCKBY, c.USERNAME LOGINID, c.sid SID, c.SERIAL# SERIAL from v$locke{d}_{o}bjecta,db{a}_{o}bjectsb,v$session c where b.object_id = a.object_id AND a.SESSION_ID = c.sid;
–通过SID, SERIAL解锁 --alter system kill session ‘SID, SERIAL’;

应急====================================
–四种性能故障应急处理的过程第1 种：Linux操作系统某个ORACLE 进程占用CPU 高，如何快速抓取SQL 语句？第2 种：业务报过来，说数据库很慢，作为DBA，怎么样知道数据库是不是很慢，然后如果慢，查出源头？第3 种：如果数据库hang 了，怎么快速马上恢复正常使用（如果只是恢复业务不分析问题）？第4 种：如何查系统有死锁，并快速杀死锁？ 动态性能视图：v$session--查会话连接信息v$sql --查SQL 信息v$sq{l}_{p}lan--查SQL执行计划v$sqltext --查SQL文本信息v$sqlarea--查SQL语句资源消耗情况v$session_wait --查会话等待信息v$sessio{n}_{e}vent--会话等待事件v$process --查进程v$lock--查锁v$locked_object --锁的对象v$sessio{n}_{l}ongops--查某些话会操作时间v$sysstat --查实例的一些统计信息v$sessio{n}_{w}ai{t}_{s}ession--查热点块v$transaction --查正在执行的事务信息v$sor{t}_{u}sag{e}_{t}emp--查temp使用情况v$sesstat --通过sid 得到会话的统计信息gv$access --正在被访问占用对象与sid --测试这么一条占CPU高的程序：declare i number; j number;begin i := 0; loop j := sqrt(i); i := i + 1; end loop;end;/

================================================================================ 第1 种：抓取性能消耗高的SQL语句【输入top命令占CPU高的pid号】SELECT a.username, a.machine, a.program, a.sid, a.serial#, a.status, c.piece, c.sql_text FROM v$sessiona,v$process b, v$sqltext c WHERE b.spid = ‘&spid’ AND b.addr = a.paddr AND a.sql_address = c.address(+) ORDER BY c.piece; --alter system kill session '1209,5’immediate;

第2 种：业务报过来，说数据库很慢，作为DBA，怎么样知道数据库是不是很慢，然后如果慢，查出源头？–v$sessio{n}_{w}ait--查会话等待信息[directpathread]selectevent,count(\ast )fromgv$session_wait group by event order by 2 desc; --查出等待的对应的SQL idselect sw.p1, s.sql_id, count(), (ratio_to_report(count()) over()) * 100 pct from v$sessions,v$session_wait sw where s.event like ‘%direct path read%’ and s.sid = sw.sid group by sw.p1, s.sql_id order by count() desc; P1 SQL_ID COUNT() PCT----- ------------- ---------- ---------6 gb82syps2pgp7 3 100 --占用的百分百 --查出等待的对应的SQLsys@itpuxfs> select sql_id,sql_text from vKaTeX parse error: Expected 'EOF', got '#' at position 304: …',' || s.serial#̲ || '''; …session s, v$process p where s.PADDR = p.addr --and s.username = ‘GGS’ and s.status=‘ACTIVE’;
查杀数据库与Linux进程（结合）alter system kill session ‘143,33’; – kill -9 10928
再上这种（杀所有连接）：ps -ef | grep ‘LOCAL=NO’ |grep oracleitpuxfs | kill -9 awk '{print $2}' =================================================================================== 语句2：select sn.username, m.sid, sn.serial#, m.type, decode(m.lmode, 0, ‘none’, 1, ‘null’, 2, ‘rowshare’, 3, ‘rowexcl.’, 4, ‘share’, 5, ‘srowexcl.’, 6, ‘exclusive’, lmode, ltrim(to_char(lmode, ‘990’))) lmode, decode(m.request, 0, ‘none’, 1, ‘null’, 2, ‘rowshare’, 3, ‘rowexcl.’, 4, ‘share’, 5, ‘srowexcl.’, 6, ‘exclusive’, request, ltrim(to_char(m.request, ‘990’))) request, m.id1, m.id2 from v$sessionsn,v$lock m where (sn.sid = m.sid and m.request != 0) or (sn.sid = m.sid and m.request = 0 and lmode != 4 and (id1, id2) in (select s.id1, s.id2 from v$lock s where request != 0 and s.id1 = m.id1 and s.id2 = m.id2)) order by id1, id2, m.request; SQL> alter system kill session ‘sid,serial#’ immediate;
–杀mode=exclusive --------------------------------------------------

查看表上锁的情况：select sn.username, m.sid, sn.serial#, m.type, decode(m.lmode, 0, ‘none’, 1, ‘null’, 2, ‘rowshare’, 3, ‘rowexcl.’, 4, ‘share’, 5, ‘s/rowexcl.’, 6, ‘exclusive’, lmode, ltrim(to_char(lmode, ‘990’))) lmode, decode(m.request, 0, ‘none’, 1, ‘null’, 2, ‘rowshare’, 3, ‘rowexcl.’, 4, ‘share’, 5, ‘s/rowexcl.’, 6, ‘exclusive’, request, ltrim(to_char(m.request, ‘990’))) request, m.id1, m.id2 from v$sessionsn,v$lock m where (sn.sid = m.sid and m.request != 0)

–存在锁请求，即被阻塞 or (sn.sid = m.sid --不存在锁请求，但是锁定的对象被其他会话请求锁定 and m.request = 0 and lmode != 4 and (id1, id2) in (select s.id1, s.id2 from v$lockswhererequest!=0ands.id1=m.id1ands.id2=m.id2))orderbyid1,id2,m.request;---------------------------------------------------------语句3：setpages1000lin126colkaddrheadin{g}^{\prime }lock|addres{s}^{\prime }colusernameheadin{g}^{\prime }lock|holder|usernam{e}^{\prime }fora18colsidheadin{g}^{\prime }lock|holder|sessioni{d}^{\prime }format9999999999coltypeheadin{g}^{\prime }lock|typ{e}^{\prime }formata6colid1headin{g}^{\prime }id{1}^{\prime }format9999999999colid2headin{g}^{\prime }id{2}^{\prime }format9999999999collmodeheadin{g}^{\prime }lock|mod{e}^{\prime }format99999999colrequestheadin{g}^{\prime }request|mod{e}^{\prime }format99999999colblockin{g}_{s}idformat999999headin{g}^{\prime }blocked|sessioni{d}^{\prime }select/\ast +rule\ast /--a.kaddr,--(selectusernamefromv$session where sid = a.sid) username, a.sid, (select serial# from vKaTeX parse error: Expected 'EOF', got '#' at position 34: …= a.sid) serial#̲, -- (select ct…lock where KADDR = a.kaddr) ctime, – a.type, a.id1, a.id2, a.lmode, a.request, a.block, b.sid blocking_sid from v$locka,(select\ast fromv$lock where request > 0 and type <> ‘MR’) b where a.id1 = b.id1(+) and a.id2 = b.id2(+) and a.lmode > 0 and a.type <> ‘MR’ order by username, a.sid, serial#, a.type;

================================================================================最经典的一条语句SET ECHO OFFSET FEEDBACK 6SET HEADING ONSET LINESIZE 180SET PAGESIZE 50000SET TERMOUT ONSET TIMING OFFSET TRIMOUT ONSET TRIMSPOOL ONSET VERIFY OFFCLEAR COLUMNSCLEAR BREAKSCLEAR COMPUTESPROMPTPROMPTPROMPT ±-----------------------------------------------------------------------+PROMPT | BLOCKING LOCKS (Summary)|PROMPT ±-----------------------------------------------------------------------+PROMPTSET serveroutput ON FORMAT WRAPPEDSET feedback OFFDECLARE CURSOR cur_BlockingLocks IS SELECT iw.instance_name AS waiting_instance, sw.status AS waiting_status, lw.sid AS waiting_sid, sw.serial# AS waiting_serial_num, sw.username AS waiting_oracle_username, sw.osuser AS waiting_os_username, sw.machine AS waiting_machine, pw.spid AS waiting_spid, SUBSTR(sw.terminal, 0, 39) AS waiting_terminal, SUBSTR(sw.program, 0, 39) AS waiting_program, ROUND(lw.ctime / 60) AS waiting_lock_time_min, DECODE(lh.type, ‘CF’, ‘Control File’, ‘DX’, ‘Distributed Transaction’, ‘FS’, ‘File Set’, ‘IR’, ‘Instance Recovery’, ‘IS’, ‘Instance State’, ‘IV’, ‘Libcache Invalidation’, ‘LS’, ‘Log Start or Log Switch’, ‘MR’, ‘Media Recovery’, ‘RT’, ‘Redo Thread’, ‘RW’, ‘Row Wait’, ‘SQ’, ‘Sequence Number’, ‘ST’, ‘Diskspace Transaction’, ‘TE’, ‘Extend Table’, ‘TT’, ‘Temp Table’, ‘TX’, ‘Transaction’, ‘TM’, ‘DML’, ‘UL’, ‘PLSQL User_lock’, ‘UN’, ‘User Name’, ‘Nothing-’) AS waiter_lock_type, DECODE(lw.request, 0, ‘None’ /* Mon Lock equivalent /, 1, ‘NoLock’ / N /, 2, ‘Row-Share (SS)’ / L /, 3, ‘Row-Exclusive (SX)’ / R /, 4, ‘Share-Table’ / S /, 5, ‘Share-Row-Exclusive (SSX)’ / C /, 6, ‘Exclusive’ / X /, ‘[Nothing]’) AS waiter_mode_request, ih.instance_name AS locking_instance, sh.status AS locking_status, lh.sid AS locking_sid, sh.serial# AS locking_serial_num, sh.username AS locking_oracle_username, sh.osuser AS locking_os_username, sh.machine AS locking_machine, ph.spid AS locking_spid, SUBSTR(sh.terminal, 0, 39) AS locking_terminal, SUBSTR(sh.program, 0, 39) AS locking_program, ROUND(lh.ctime / 60) AS locking_lock_time_min, aw.sql_text AS waiting_sql_text FROM gv$locklw,gv$lock lh, gv$instanceiw,gv$instance ih, gv$sessionsw,gv$session sh, gv$processpw,gv$process ph, gv$sqlareaawWHEREiw.ins{t}_{i}d=lw.ins{t}_{i}dANDih.ins{t}_{i}d=lh.ins{t}_{i}dANDsw.ins{t}_{i}d=lw.ins{t}_{i}dANDsh.ins{t}_{i}d=lh.ins{t}_{i}dANDpw.ins{t}_{i}d=lw.ins{t}_{i}dANDph.ins{t}_{i}d=lh.ins{t}_{i}dANDaw.ins{t}_{i}d=lw.ins{t}_{i}dANDsw.sid=lw.sidANDsh.sid=lh.sidANDlh.id1=lw.id1ANDlh.id2=lw.id2ANDlh.request=0ANDlw.lmode=0AND(lh.id1,lh.id2)IN(SELECTid1,id2FROMgv$lock WHERE request = 0 INTERSECT SELECT id1, id2 FROM gv$lockWHERElmode=0)ANDsw.paddr=pw.addr(+)ANDsh.paddr=ph.addr(+)ANDsw.sq{l}_{a}ddress=aw.addressORDERBYiw.instanc{e}_{n}ame,lw.sid;TYPE{t}_{B}lockingLockRecordISRECORD(WaitingInstanceNameVARCHAR2(16),WaitingStatusVARCHAR2(8),WaitingSidNUMBER,WaitingSerialNumNUMBER,WaitingOracleUsernameVARCHAR2(30),WaitingOSUsernameVARCHAR2(30),WaitingMachineVARCHAR2(64),WaitingSpidVARCHAR2(12),WaitingTerminalVARCHAR2(30),WaitingProgramVARCHAR2(48),WaitingLockTimeMinuteNUMBER,WaiterLockTypeVARCHAR2(30),WaiterModeRequestVARCHAR2(30),LockingInstanceNameVARCHAR2(16),LockingStatusVARCHAR2(8),LockingSidNUMBER,LockingSerialNumNUMBER,LockingOracleUsernameVARCHAR2(30),LockingOSUsernameVARCHAR2(30),LockingMachineVARCHAR2(64),LockingSpidVARCHAR2(12),LockingTerminalVARCHAR2(30),LockingProgramVARCHAR2(48),LockingLockTimeMinuteNUMBER,SQLTextVARCHAR2(1000));TYPE{t}_{B}lockingLockRecordTableISTABLEOF{t}_{B}lockingLockRecordINDEXBYBINAR{Y}_{I}NTEGER;v_{B}lockingLockArray{t}_{B}lockingLockRecordTable;v_{B}lockingLockReccu{r}_{B}lockingLocks$session s, gv$locke{d}_{o}bjectl,gv$instance i WHERE i.inst_id = l.inst_id AND s.inst_id = l.inst_id AND s.sid = l.session_id AND o.object_id = l.object_id ORDER BY i.instance_name, l.session_id; --查询数据库正在执行的sql语句与该用户 SELECT a.spid 操作系统进程, b.sid, b.serial#, b.username 数据库用户, b.status, b.program, b.paddr, b.machine 计算机名, b.program, b.module, b.logon_time, b.event, c.sql_text 正在执行的SQL FROM v$processa,v$session b, vKaTeX parse error: Expected 'EOF', got '#' at position 174: … A.serial#̲, A.OSUSE…SESSION A, VKaTeX parse error: Expected 'EOF', got '#' at position 278: … s.serial#̲, l.locke…locked_object l, all_objects o, vKaTeX parse error: Expected 'EOF', got '#' at position 92: …Y sid, s.serial#̲; --kill掉当前的锁对…sqlarea b where b.FIRST_LOAD_TIME between ‘2019-03-12/09:20:40’ and ‘2019-03-14/08:45:30’ order by b.FIRST_LOAD_TIME desc; --查找前十条性能(读)差的sql SELECT * FROM (select PARSING_USER_ID, EXECUTIONS 执行次数, SORTS, COMMAND_TYPE, DISK_READS 磁盘读, sql_text, SQL_FULLTEXT 文本, sql_id, FIRST_LOAD_TIME, last_active_time, last_load_time, object_status, cpu_time / 1000, module, parsing_schema_name FROM v$sqlareaorderBYdis{k}_{r}eadsDESC)whereROWNUM<10;===================================================--查看当前运行的某个session执行的SQLSELECTb.sq{l}_{t}ext,b.SQ{L}_{F}ULLTEXT,a.machineOS机器,a.username数据库执行的用户,a.module,c.target被操作的对象,c.sofar/totalwork\ast 100,--conpletepercentc.elapse{d}_{s}econds执行几秒,c.tim{e}_{r}emaining剩余时间FROMv$session a, v$sqlareab,v$session_longops c WHERE a.sql_hash_value = b.hash_value(+) AND a.SID = c.SID(+) AND a.serial# = c.serial#(+) --AND a.sid = 5571 =================================================== --查看最近执行过的sql语句: SELECT sql_text, last_load_time FROM v$sqlWHERElas{t}_{l}oa{d}_{t}imeISNOTNULL--andsq{l}_{t}extlik{e}^{\prime }select$session a, v$sqltextbWHEREa.sq{l}_{a}ddress=b.addressanda.status{=}^{\prime }ACTIV{E}^{\prime }--ANDa.sid=???ORDERBYb.piece;--查看Oracle数据库中的长事务:setlinesize200setpagesize5000coltransactio{n}_{d}urationformata45withtransactio{n}_{d}etailsas(selectins{t}_{i}d,se{s}_{a}ddr,sysdate-star{t}_{d}ateasdifffromgv$transaction)select s.sid, s.serial#, s.username, s.program, to_char(trunc(t.diff)) || ’ days, ’ || to_char(trunc(mod(t.diff * 24, 24))) || ’ hours, ’ || to_char(trunc(mod(t.diff * 24 * 60, 24))) || ’ minutes, ’ || to_char(trunc(mod(t.diff * 24 * 60 * 60, 60))) || ’ seconds’ as transaction_duration, s.terminal, s.status, s.osuser, s.process, s.machine, s.event from gv$sessions,transactio{n}_{d}etailstwheres.ins{t}_{i}d=t.ins{t}_{i}dands.saddr=t.se{s}_{a}ddrands.status{=}^{\prime }ACTIV{E}^{\prime }orderbyt.diffdesc;--查询长事务SQL:下面SQL查询数据库中正在执行大于N秒的事务信息：withltras(selectt{o}_{c}har(sysdate{,}^{\prime }YYYYMMDDHH24MIS{S}^{\prime })时间,s.sid,s.sq{l}_{i}d,s.sq{l}_{c}hil{d}_{n}umber,s.pre{v}_{s}q{l}_{i}d,xid,t{o}_{c}har(t.star{t}_{d}ate{,}^{\prime }YYYYMMDDHH24MIS{S}^{\prime })star{t}_{t}ime,e.TYPE,e.block,e.ctime,decode(e.CTIME,0,(sysdate-t.star{t}_{d}ate)\ast 3600\ast 24,e.ctime)e{l}_{s}econd--q.sq{l}_{t}extfromv$transaction t, v$sessions,v$transaction_enqueue e where t.start_date <= sysdate - interval ‘100’ second /查询开始多少秒的事务/ and t.addr = s.taddr --and s.sql_child_number = q.CHILD_NUMBER(+) --and s.sql_id = q.sql_id(+) and s.prev_sql_id = q.sql_id(+) and t.addr = e.addr(+))select ltr., (select q1.sql_text from v$sqlq1whereltr.pre{v}_{s}q{l}_{i}d=q1.sq{l}_{i}d(+)andrownum=1)pre{v}_{s}q{l}_{t}ext,(selectq1.sq{l}_{t}extfromv$sql q1 where ltr.sql_id = q1.sql_id(+) and ltr.sql_child_number = q1.CHILD_NUMBER(+)) sql_text from ltr ltr; --查看正在并行的SQLselect s.username, s.machine, s.program, s.module, s.client_info, s.status, s.sid, s.sql_id, q.degree from v$sessions,(selectqcsid,count(\ast )-1degreefromv$px_session group by qcsid) q where s.sid=q.qcsid order by 1,4;select * from v$px_session group by qcsid;