event.sql

用于ORACLE管理

--查询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