Oracle数据库查看锁表、解锁操作语句

Oracle数据库操作语句

备忘一下，后面方便查看

1、查看数据库锁表语句：

select /*+rule*/ s.username, decode(I.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL)LOCK_LEVEL,o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser from v$session s, v$lock I,dba_objects o where I.SID =s.SID
and I.ID1 = o.object_id(+) and s.USERNAME is not null;

 2、解锁语句，搭配上述锁表语句使用：

--解锁语句：
alter system kill session 'sid,serial#';

3、查看trace日志：

SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0)))
 || '_ora_' || P.SPID || '.trc' TRACE_FILE_NAME FROM 
 ( SELECT P.SPID FROM V$MYSTAT M, V$SESSION S, V$PROCESS P WHERE M.STATISTIC# =1 AND S.SID = M.SID AND P.ADDR = S.PADDR )
  P, ( SELECT T.INSTANCE FROM V$THREAD T, V$PARAMETER V WHERE V.NAME ='thread' AND 
  (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE)) ) I, (SELECT VALUE FROM V$PARAMETER WHERE NAME='user_dump_dest') T ;