Created
February 29, 2016 01:49
-
-
Save Kelvin-Lei/56f3b0f5a894df8eb29f to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --SID使用数据库的某个用户的当前会话ID | |
| --sid：session的id | |
| --serial＃：sid的序列号，因为可能有些会话的sid相同，sid可以重复使用，以后使用已经使用过的sid时，serial＃会递增 | |
| --附：用于确定锁住数据库对象的锁的SQL | |
| SELECT c.owner | |
| ,c.object_name | |
| ,c.object_type | |
| ,b.sid | |
| ,b.serial# | |
| ,b.status | |
| ,b.osuser | |
| ,b.machine | |
| FROM v$locked_object a | |
| ,v$session b | |
| ,dba_objects c | |
| WHERE b.sid = a.session_id | |
| AND a.object_id = c.object_id; | |
| ALTER system kill session '980,973'; | |
| --死锁 | |
| SELECT ao.object_name | |
| ,s.sql_text | |
| ,s2.sql_text next_sql_text | |
| ,d.sid | |
| ,d.serial# | |
| ,c.spid | |
| ,d.action | |
| ,c.spid | |
| ,d.module | |
| ,d.program | |
| ,d.username | |
| ,d.client_identifier | |
| , /*(SELECT ra.request_id FROM fnd_concurrent_requests ra, v$session rb, v$process rc WHERE ra.oracle_session_id = rb.audsid AND rb.paddr = rc.addr(+) AND rb.sid = d.sid) request_id,*/vo.* | |
| ,ao.* | |
| FROM v$locked_object vo | |
| ,all_objects ao | |
| ,v$process c | |
| ,v$session d | |
| ,v$sqlarea s | |
| ,v$sqlarea s2 | |
| WHERE vo.object_id = ao.object_id | |
| AND c.addr(+) = d.paddr | |
| AND d.sid(+) = vo.session_id | |
| AND s.address = d.prev_sql_addr | |
| AND s2.sql_id = d.sql_id | |
| ORDER BY ao.last_ddl_time DESC; | |
| --根据请求号查找session | |
| SELECT a.process_status_code | |
| ,a.oracle_process_id | |
| ,a.os_process_id | |
| ,process_start_date | |
| ,a.* | |
| ,b.* | |
| FROM fnd_concurrent_processes a | |
| ,fnd_concurrent_requests b | |
| WHERE b.controlling_manager = a.concurrent_process_id | |
| AND b.request_id = 49580183; | |
| --杀ORACLE进程: | |
| ALTER system kill session '查出的SID,查出的SERIAL#'; | |
| ALTER system kill session '355,15455'; | |
| --查看数据库等待事件（编译时包被锁住--常用） | |
| SELECT b.saddr | |
| ,fcr.request_id | |
| ,pt.user_concurrent_program_name --数据库等待事件 | |
| ,a.sid | |
| ,b.serial# | |
| ,c.spid | |
| ,b.machine | |
| ,b.module | |
| ,b.action | |
| ,a.seconds_in_wait | |
| ,b.logon_time | |
| ,a.event | |
| ,a.p1text | |
| ,a.p1 | |
| ,a.p1raw | |
| ,a.p2text | |
| ,a.p2 | |
| ,a.p2raw | |
| ,a.p3text | |
| ,a.p3 | |
| ,a.p3raw | |
| ,b.username | |
| ,a.wait_time | |
| FROM v$session_wait a | |
| ,v$session b | |
| ,v$process c | |
| ,fnd_concurrent_requests fcr | |
| ,fnd_concurrent_programs_tl pt | |
| ,fnd_concurrent_programs pb | |
| WHERE 1 = 1 | |
| AND a.wait_class <> 'Idle' -- for 10g^11g db | |
| AND c.addr(+) = b.paddr | |
| AND a.event NOT LIKE 'SQL*Net%' | |
| AND a.event NOT LIKE 'rdbms%' | |
| AND a.event NOT LIKE 'jobq slave wait' | |
| AND a.event NOT LIKE '%mon timer' | |
| AND a.event NOT LIKE 'ASM%' | |
| AND a.event NOT LIKE '%idle wait%' | |
| AND a.event NOT LIKE 'DIAG idle wait' | |
| AND a.event NOT LIKE 'wakeup time manager' | |
| AND a.event NOT LIKE 'queue m%' | |
| AND a.event <> 'PL/SQL lock timer' | |
| AND a.event <> 'pipe get' | |
| --AND a.event LIKE 'enq:%' | |
| --and a.event = 'library cache lock' | |
| --AND a.INST_ID = b.INST_ID | |
| -- AND b.MODULE='01@<oracle/gmdbappl/xbd/11.5.10/sql/XBDLHDZD.sql' | |
| --AND b.MODULE like '%XBDARPAY1%' | |
| --and b.action<>'Concurrent Request' | |
| AND fcr.oracle_session_id = b.audsid | |
| AND pb.application_id = fcr.program_application_id | |
| AND pb.concurrent_program_id = fcr.concurrent_program_id | |
| AND pb.application_id = pt.application_id | |
| AND pb.concurrent_program_id = pt.concurrent_program_id | |
| AND pt.language = 'ZHS' | |
| AND b.sid = a.sid | |
| --AND b.sid =1714 | |
| -- AND b.USERNAME = 'PERFSTAT' | |
| --AND a.P2 = 245562 | |
| ORDER BY a.event | |
| ,a.p1; | |
| --杀ORACLE进程: | |
| ALTER system kill session '查出的SID,查出的SERIAL#'; | |
| ALTER system kill session '1151,65151'; | |
| ALTER system kill session '29,1323'; | |
| 如何杀死oracle死锁进程 方法一： | |
| --１.查哪个过程被锁：查V$DB_OBJECT_CACHE视图: | |
| SELECT * | |
| FROM v$db_object_cache | |
| WHERE owner = '过程的所属用户' | |
| AND locks != '0'; | |
| --2. 查是哪一个SID,通过SID可知道是哪个SESSION：查V$ACCESS视图: | |
| SELECT * | |
| FROM v$access | |
| WHERE owner = '过程的所属用户' | |
| AND NAME = '刚才查到的过程名'; | |
| --3. 查出SID和SERIAL#：查V$SESSION视图: | |
| SELECT sid | |
| ,serial# | |
| ,paddr | |
| FROM v$session | |
| WHERE sid = '刚才查到的SID' | |
| --查V$PROCESS视图: | |
| SELECT spid FROM v$process WHERE addr = '刚才查到的PADDR'; | |
| --4. 杀进程 | |
| --(1).先杀ORACLE进程: | |
| ALTER system kill session '查出的SID,查出的SERIAL#'; | |
| --(2).再杀操作系统进程: | |
| kill - 9 刚才查出的spid | |
| --或ORAKILL 刚才查出的SID 刚才查出的SPID | |
| --方法二： | |
| --1）查找死锁的进程： | |
| sqlplus "/as sysdba" | |
| SELECT s.username | |
| ,l.object_id | |
| ,l.session_id | |
| ,s.serial# | |
| ,l.oracle_username | |
| ,l.os_user_name | |
| ,l.process | |
| FROM v$locked_object l | |
| ,v$session s | |
| WHERE l.session_id = s.sid; | |
| --2）kill掉这个死锁的进程： | |
| ALTER system kill session ‘sid, serial#’; | |
| （其中sid = l.session_id） 3） | |
| --如果还不能解决， | |
| SELECT pro.spid | |
| FROM v$session ses | |
| ,v$process pro | |
| WHERE ses.sid = xx | |
| AND ses.paddr = pro.addr; | |
| --其中sid用死锁的sid替换。 | |
| --EXIT ps - ef | grep spid | |
| --其中spid是这个进程的进程号，kill掉这个oracle进程。 | |
| --被锁住包 | |
| SELECT b.sid | |
| ,b.serial# | |
| ,a.name | |
| ,a.owner | |
| ,a.type | |
| FROM dba_ddl_locks a | |
| ,v$session b | |
| WHERE a.session_id = b.sid | |
| AND a.name LIKE 'AFA_AR_FINA%' | |
| AND owner = 'APPS'; | |
| SELECT * FROM all_objects o WHERE o.object_name LIKE 'AFA_AR_UTL%'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment