select DISTINCT lk.session_id sid, se.serial#, lk.locked_mode lk_md, '|', se.CLIENT_IDENTIFIER c_name, --BES系统登录名 dd.FULL_NAME, se.logon_time, se.ACTION, FV.RESPONSIBILITY_NAME transfer, lk.oracle_username || '_' || lk.os_user_name || '_' ||se.machine user_info, '|', se.MODULE, BF.FORM_NAME, --substr(se.MODULE, instr(se.MODULE, ':', 1, 3) + 1, length(se.MODULE)), FT.USER_FORM_NAME, FT.LANGUAGE, se.WAIT_CLASS, sw.event, la.name, sa.sql_text, '|', 'alter system kill session ''' || se.sid || ',' || se.serial# || ''';' killsql, c.spid AS os_process_id, c.pid, 'ps -ef | grep ' || c.spid as check_os_process_command, 'kill -9 ' || c.spid as kill_os_process_command FROM v$locked_object lk, v$session SE, v$sqlarea sa, v$session_wait sw, v$latch la, v$process c, per_people_f dd, FND_USER FU, FND_RESPONSIBILITY_VL fv, FND_FORM_TL FT, FND_FORM BF WHERE 1 = 1 AND se.sid = lk.session_id AND se.prev_sql_addr = sa.address AND se.sid = sw.sid AND se.paddr = c.addr AND sw.p2 = la.latch#(+) AND se.CLIENT_IDENTIFIER = FU.user_name AND fu.EMPLOYEE_ID = DD.PERSON_ID AND substr(se.ACTION, instr(se.ACTION, '/', 1, 1) + 1, length(se.action))=FV.RESPONSIBILITY_KEY(+) AND substr(se.MODULE, instr(se.MODULE, ':', 1, 3) + 1, length(se.MODULE))= BF.FORM_NAME(+) AND BF.FORM_ID = FT.FORM_ID(+) AND FT.LANGUAGE(+) = USERENV('LANG') ORDER BY se.LOGON_TIME,sid
应当注意对于sid在100以下的应当谨慎，可能该进程对应某个application，如对应某个事务,可以kill. 可以用Spotlight软件对数据库的运行状态进行监控。 oracle会话被锁是经常的。可以使用alter system kill session 'sid,serial#'杀死会话, 如果有长期出现的一列，可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁：如果出现了锁的问题, 某个DML操作可能等待很久没有反应。 当你采用的是直接连接数据库的方式，也不要用OS系统命令 $kill process_num 或者 $kill -9 process_num来终止用户连接，因为一个用户进程可能产生一个以上的锁, 杀OS进程并不能彻底清除锁的问题。 --解除锁定 --1.强制: alter system kill session 'sid,serial#'; --2.当占用锁会话commit时,解除锁定alter system kill session 'sid,serial#'; ============================================== --以下几个为相关表 SELECT * FROM v$lock; SELECT * FROM v$sqlarea; SELECT * FROM v$session; SELECT * FROM v$process ; SELECT * FROM v$locked_object; SELECT * FROM all_objects; SELECT * FROM v$session_wait; =================================================== --1.查出锁定object的session的信息以及被锁定的object名,生 select b.owner, b.object_name, x.WAIT_CLASS,--引起等待的类 l.session_id, x.SERIAL#, l.locked_mode 锁定模式, lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username DB用户, l.os_user_name OS用户, x.username, x.logon_time, x.machine 机器名, x.terminal, x.client_info, x.logon_time, 'alter system kill session ''' || x.sid || ',' || x.serial# || ''';' killsql, c.spid AS os_process_id, c.pid, 'ps -ef | grep ' || c.spid as check_os_process_command, 'kill -9 ' || c.spid as kill_os_process_command from v$locked_object l, dba_objects b, v$session x, v$process c where b.object_id = l.object_id and l.SESSION_ID = x.SID and c.addr = x.paddr --and OBJECT_NAME like 'MTL_ITEM_LOCATIONS' order by b.object_id,l.xidusn DESC; --如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待(左边锁右边等待?????) --以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。 --如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN --还可以根据v$session_wait;查看等待事件 --2.查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句 SELECT lk.session_id sid, se.serial#, lpad(' ', decode(lk.xidusn, 0, 3, 0)) || lk.oracle_username User_name, lk.locked_mode, lk.oracle_username, se.user#, lk.os_user_name, se.machine, se.terminal, a.sql_text, a.action FROM v$sqlarea a, v$session se, v$locked_object lk WHERE lk.session_id = se.sid AND se.prev_sql_addr = a.address -- and sid= &sid --利用sid找到相应的sql ORDER BY sid, se.serial#; --3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode --这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现， --任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。 SELECT s.sid, --会话ID s.serial#, s.username, decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', l.type) LOCK_LEVEL, o.object_name, --对象名称 o.object_type, --对象类型 l.lmode, l.request, o.owner, --对象用户 s.schemaname, s.osuser, --操作系统用户 s.process, s.machine, --对象机器名域名 s.terminal, --对象名称 s.program, --对象通过程序 s.logon_time, l.block FROM v$session s, v$lock l, dba_objects o WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is NOT Null and l.type in ('TX', 'TM') -- AND o.OBJECT_NAME='MTL_ITEM_LOCATIONS' and s.sid=&input_sid order by s.sid,o.OBJECT_NAME; --4.这是查找被锁包的SQL select B.SID, b.USERNAME, b.MACHINE, a.OBJECT, 'alter system kill session ' || '''' || b.SID || ',' || b.SERIAL# || ''';' kill_command FROM V$ACCESS A, V$SESSION B WHERE A.SID = B.SID and upper(a.OBJECT) like '%CUX%' and a.TYPE = 'PACKAGE' --根据包名查找锁 select 'alter system kill session ' || '''' || to_char(b.sid) || ',' || to_char(b.serial#) || '''' || ';' kill_statement, b.sid, b.serial#, b.* from dba_ddl_locks a, v$session b where a.session_id = b.sid and a.name = 'DFG_EXP_HOMEPG'; ===================================== 5.查看等待事件: select sid, event, p1 as "p1 as file_id", p2 as "p2 as block_id/latch", p3 as "p3 as blocks", l.name from v$session_wait sw, v$latch l where event not like '%SQL%' and event not like '%rdbms%' and event not like '%mon%' and sw.p2 = l.latch#(+) and sid=&input_sid; ================================================== 查看是谁锁了谁 select s1.username /*|| [ email = '@' ] '@' [ / email ] */|| s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '|| s2.username || /*[ email = '@' ] '@' [ / email ] ||*/ s2.machine || ' ( SID=' || s2.sid || ' ) ' ASblocking_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; =============================================== 6.通过并发管理器的请求的ID查SQL a.先查sid SELECT 'Request id: ' || request_id, 'Trace id: ' || oracle_Process_id, 'Trace Flag: ' || req.enable_trace, 'Trace Name: ' || dest.value || '/' || dbnm.value || '_ora_' || oracle_process_id || '.trc', 'Prog. Name: ' || prog.user_concurrent_program_name, 'File Name: ' || execname.execution_file_name || execname.subroutine_name, 'Status : ' || decode(phase_code, 'R', 'Running') || '-' || decode(status_code, 'R', 'Normal'), 'SID Serial: ' || ses.sid || ',' || ses.serial#, ses.sid, 'Module : ' || ses.module from fnd_concurrent_requests req, v$session ses, v$process proc, v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog, fnd_executables execname where req.request_id = &request id and req.oracle_process_id = proc.spid(+) and proc.addr = ses.paddr(+) and dest.name = 'user_dump_dest' and dbnm.name = 'db_name' and req.concurrent_program_id = prog.concurrent_program_id and req.program_application_id = prog.application_id --- and prog.application_id = execname.application_id and prog.executable_application_id = execname.application_id and prog.executable_id = execname.executable_id; ------------------------- b. ##查引起等待的类 select a.WAIT_CLASS from v$session a where a.sid= xxx; ------------------------- c. ##查引起等待的SQL select sql_text, sql_id, hash_value from v$sqltext_with_newlines where hash_value in (select SQL_HASH_VALUE from v$session where paddr in (select addr from v$process where spid = (select b.oracle_process_id from apps.fnd_concurrent_requests b where b.request_id = 1413621))) order by piece; ========================================================== 另一个查并发的 SELECT U.USER_NAME, APP.APPLICATION_SHORT_NAME, FAT.APPLICATION_NAME, FR.RESPONSIBILITY_KEY, FRT.RESPONSIBILITY_NAME, FFF.FUNCTION_NAME, FFT.USER_FUNCTION_NAME, ICX.FUNCTION_TYPE, ICX.FIRST_CONNECT, ICX.LAST_CONNECT FROM ICX_SESSIONS ICX, FND_USER U, FND_APPLICATION APP, FND_APPLICATION_TL FAT, FND_RESPONSIBILITY FR, FND_RESPONSIBILITY_TL FRT, FND_FORM_FUNCTIONS FFF, FND_FORM_FUNCTIONS_TL FFT WHERE 1 = 1 AND U.USER_ID = ICX.USER_ID AND ICX.RESPONSIBILITY_APPLICATION_ID = APP.APPLICATION_ID AND FAT.APPLICATION_ID = ICX.RESPONSIBILITY_APPLICATION_ID AND FAT.LANGUAGE = 'ZHS' AND FR.APPLICATION_ID = ICX.RESPONSIBILITY_APPLICATION_ID AND FR.RESPONSIBILITY_ID = ICX.RESPONSIBILITY_ID AND FRT.LANGUAGE = 'ZHS' AND FRT.APPLICATION_ID = ICX.RESPONSIBILITY_APPLICATION_ID AND FRT.RESPONSIBILITY_ID = ICX.RESPONSIBILITY_ID AND FFF.FUNCTION_ID = ICX.FUNCTION_ID AND FFT.FUNCTION_ID = ICX.FUNCTION_ID AND ICX.DISABLED_FLAG != 'Y' AND ICX.PSEUDO_FLAG = 'N' AND (ICX.LAST_CONNECT + DECODE(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL, ICX.LIMIT_TIME, 0, ICX.LIMIT_TIME, FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT') / 60) / 24) > SYSDATE AND ICX.COUNTER < ICX.LIMIT_CONNECTS;