--查詢v$session_wait獲取各進程等待事件
select sid,event,p1,p1text from v$session_wait
--捕獲相關SQL
SELECT   a.sql_text
    FROM v$sqltext a
   WHERE a.hash_value = (SELECT b.sql_hash_value
                           FROM v$session b
                          WHERE b.SID = '&sid')
ORDER BY a.piece ASC
--
select index_name,index_type  from user_indexes where table_name='HS_INFO'
--檢查索引鍵值:
select index_name,column_name from user_ind_columns  where table_name ='HS_INFO'

desc hs_info

select sid,event,p1,p1text from v$session_wait where event not like 'SQL%'
/*
ORACLE鎖的管理
2003-03 余楓

ORACLE里鎖有以下幾種模式:

0：none
1：null　　　空 
2：Row-S　　 行共享(RS)：共享表鎖　
3：Row-X　　 行專用(RX)：用于行的修改
4：Share　　 共享鎖(S)：阻止其他DML操作
5：S/Row-X　 共享行專用(SRX)：阻止其他事務操作
6：exclusive 專用(X)：獨立訪問使用 

數字越大鎖級別越高, 影響的操作越多。

一般的查詢語句如select ... from ... ;是小于2的鎖, 有時會在v$locked_object出現。

select ... from ... for update;　　　是2的鎖。

當對話使用for update子串打開一個游標時，
所有返回集中的數據行都將處于行級(Row-X)獨占式鎖定，
其他對象只能查詢這些數據行，不能進行update、delete或select...for update操作。

insert / update / delete ... ; 　　 是3的鎖。 

沒有commit之前插入同樣的一條記錄會沒有反應, 
因為后一個3的鎖會一直等待上一個3的鎖, 我們必須釋放掉上一個才能繼續工作。

創建索引的時候也會產生3,4級別的鎖。

locked_mode為2,3,4不影響DML(insert,delete,update,select)操作, 
但DDL(alter,drop等)操作會提示ora-00054錯誤。

有主外鍵約束時 update / delete ... ; 可能會產生4,5的鎖。

DDL語句時是6的鎖
*/
--1、查看數據庫鎖,診斷鎖的來源及類型：

select object_id,session_id,locked_mode from v$locked_object
--找出數據庫的serial#,以備殺死：
select t2.username,t2.sid,t2.serial#,t2.logon_time 
  from v$locked_object t1,v$session t2 
  where t1.session_id=t2.sid 
  order by t2.logon_time
--殺死session  
alter system kill session 'sid,serial#'  
--一個用戶進程偶爾會掛起或占用過多資源而拒絕其它會話。
--如果 DBA 依然能夠訪問數據庫，她通常可以發出以下查詢：
select s.username, s.osuser, s.sid, s.serial#, p.spid
  from v$session s,v$process p
 where s.paddr = p.addr
   and s.username is not null
--殺死oracle進程中的一個線程
orakill ORCL 2760   
--查看相關會話正在執行的SQL
select sql_text from v$sqlarea where address = 
( select sql_address from v$session where sid = &sid )
--查看IP地址
select SYS_CONTEXT('USERENV','IP_ADDRESS')  from dual
/*
V$PROCESS：
Addr: process address
Pid: process identifier
Spid: Operating system process identifier 用來系統級kill -9 SPID
*/
SELECT 
P.PID,P.SPID,P.USERNAME,P.TERMINAL 
from v$process p
where addr in
(
  select addr from v$process where pid<>1
   minus
  select paddr from v$session
)
--查看索引
SELECT ind.* FROM USER_INDEXES  ind where ind.table_name='ZF_DWED_CSXX'
--80、分析表
analyze table  mzbs.db_code ESTIMATE STATISTICS SAMPLE 20 PERCENT

--ORACLE中檢查表是否被鎖的語句
SELECT A.OWNER,A.OBJECT_NAME,B.XIDUSN,   
     B.XIDSLOT,B.XIDSQN,B.SESSION_ID,   
     B.ORACLE_USERNAME,B.OS_USER_NAME,B.PROCESS,   
     B.LOCKED_MODE,C.MACHINE,C.STATUS,C.SERVER,   
     C.SID,C.SERIAL#,C.PROGRAM  
FROM ALL_OBJECTS A,   
     V$LOCKED_OBJECT B,   
     SYS.GV_$SESSION C 
WHERE  A.OBJECT_ID = B.OBJECT_ID  
       AND B.PROCESS = C.PROCESS 
       ORDER BY 1,2
--47、如何在Oracle中捕獲到SQL語句的全部操作內容
SELECT osuser, username, sql_text from v$session a, v$sqltext b 
   where a.sql_address =b.address order by address, piece

   
SELECT s.username, decode(l.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 l,dba_objects o WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is NOT NULL 
SELECT a.VALUE + b.VALUE logical_reads, c.VALUE phys_reads, round(100*(1-c.value/(a.value+b.value)),4) hit_ratio FROM v$sysstat a,v$sysstat b,v$sysstat c WHERE a.NAME='db block gets' AND b.NAME='consistent gets' AND c.NAME='physical reads' 
SELECT /*+ rule */ 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
SELECT s.USERNAME,s.SID,s.SERIAL#,t.UBAFIL "UBA filenum", t.UBABLK "UBA Block number",t.USED_UBLK "Number os undo Blocks Used", t.START_TIME,t.STATUS,t.START_SCNB,t.XIDUSN RollID,r.NAME RollName FROM v$session s,v$transaction t,v$rollname r WHERE s.SADDR=t.SES_ADDR AND t.XIDUSN=r.usn 
SELECT p1.value||'\'||p2.value||'_ora_'||p.spid filename 
     FROM v$process p, v$session s, v$parameter p1, v$parameter p2 
     WHERE p1.name = 'user_dump_dest' AND p2.name = 'db_name' AND p.addr = s.paddr AND s.audsid = USERENV ('SESSIONID')
select name,text from user_source us where name='Procedurename' and type = 'PROCEDURE' order by line

select b.* from v$locked_object b
select * from sys.gv_$session