常用sql汇集

1. 查看后台正在运行的sql

select a.program, b.spid, c.sql_text from v$session a, v$process b, v$sqlarea c where a.paddr = b.addr and a.sql_hash_value = c.hash_value and a.username is not null;

2. 查看被锁object.

SELECT substr(v$lock.sid,1,4) "SID",substr(username,1,12) "UserName",substr(object_name,1,25) "ObjectName",v$lock.type "LockType",
decode(rtrim(substr(lmode,1,4)),'2','Row-S (SS)','3','Row-X (SX)','4','Share', '5','S/Row-X (SSX)','6','Exclusive', 'Other' ) "LockMode", substr(v$session.program,1,25) "ProgramName"
FROM V$LOCK,SYS.DBA_OBJECTS,V$SESSION
WHERE (OBJECT_ID = v$lock.id1
AND v$lock.sid = v$session.sid
AND username IS NOT NULL
AND username NOT IN ('SYS','SYSTEM')
AND SERIAL# != 1);

 

SELECT                                                         
        LPAD (' ', DECODE (l.xidusn, 0, 3, 0))
           || l.oracle_username
              User_name,
           o.owner,
           o.object_name,
           o.object_type,
           s.sid,
           s.serial#
    FROM   v$locked_object l, dba_objects o, v$session s
   WHERE   l.object_id = o.object_id AND l.session_id = s.sid
ORDER BY   o.object_id, xidusn DESC;

 

3. Kill session

SELECT SID,SERIAL#,STATUS FROM V$SESSION WHERE SID=418;     
ALTER SYSTEM KILL SESSION '418','10562';

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"
FROM v$session
WHERE sid IN (SELECT sid
FROM v$lock
WHERE block = 1);