oracle 集群、单机所表查询处理

oracle 获取对象锁sql

Select s.Inst_Id,
                        Vv.Instance_Number "当前实例ID",
                        Vv.Instance_Name 当前实例name,
                        Vv.Host_Name 当前主机名,
                        v.Instance_Number 被锁实例id,
                        v.Instance_Name 被锁实例id,
                        v.Host_Name 被锁主机名,
                        s.Sid,
                        a.Object_Id,
                        c.Object_Name,
                        Session_Id v_Sid,
                        Serial# v_Serial#,
                        Oracle_Username,
                        Os_User_Name,
                        s.Process,
                        'Alter System Kill Session  ' || Chr(39) || Session_Id || ',' ||
                        Serial# || Chr(39) Killsql
       From Gv$locked_Object a,
                        Gv$session       s,
                        All_Objects      c,
                        Gv$instance      v,
                        V$instance       Vv
Where a.Session_Id = s.Sid
        And s.Inst_Id = v.Inst_Id
        And a.Object_Id = c.Object_Id;

获取锁表sql的执行内容

Select Sql_Text, Spid, p.Pid, s.Sid, s.Username, s.Program, Process
 From V$sqlarea q, V$session s, V$process p
Where q.Address = s.Sql_Address
  And q.Hash_Value = s.Sql_Hash_Value
  And s.Paddr = p.Addr
    And s.Sid = &sid;

获取释放锁表sql的执行脚本

Select d.Platform_Name "操作系统类型",
                        i.Host_Name "主机名",
                        Decode(d.Platform_Id, 7, 'orakill ' || i.Instance_Name || ' ' || Spid, 8, 'orakill ' ||
                                                        i.Instance_Name || ' ' || Spid, 'kill -9 ' || Spid) "kill"
       From Gv$session s, Gv$process p, Gv$database d, Gv$instance i
Where s.Paddr = p.Addr
        And s.Inst_Id = d.Inst_Id
        And s.Inst_Id = i.Inst_Id
        And s.Sid = &sid

系统级别杀进程

1. linux shell 执行

kill -9  spid    

2. windows cmd 执行

orakill INSTACNE_SID  SPID

3.sql 执行解锁会话

Select * From v$session
--  alter system kill session 'v_SID,v_SERIAL#';
Alter System Kill Session '78,21327'

3. 获取ddl锁对象

Select * from Dba_Ddl_Locks