首页 > DBA > 抓session.sql

抓session.sql

/*
快速抓取当前你关注的Session的SQL活动,适合于你不方便做Trace的情况下,了解session的活动:
1) IF table hand_test is not exist then Create the table
   create  table hand_test(sqltext varchar2(4000),
                           sqldate date)
2) use plsqldeveloper ->Tools->Sessions to know which sessionid your should focus on
3) run this script use the session id you focus on
4) SELECT * FROM hand_test to view current sqltext of this session
*/

DECLARE
  l_sql VARCHAR2(32767);
BEGIN
    DELETE FROM hand_test;
    l_sql :=NULL;
    For cur_cousor In (Select ADDRESS
                       From v$session vs, v$open_cursor vc
                       Where vs.sid = vc.sid
                       --AND vs.machine Like 'WORKGROUP\DAPHNE-E441C4A2%'
                       --And vs.username = 'DPOS'
                       --And vs.program = 'D:\WINDOWS\system32\dllhost.exe'
                       And vs.sid = &SessionID
                       ) LOOP
        l_sql:=NULL;              
        FOR cur_piece IN ( SELECT  sql_text
                           FROM v$sqltext
                           WHERE address =  cur_cousor.address
                           ORDER BY piece)LOOP
                 l_sql := l_sql  ||  cur_piece.sql_text;        
        END LOOP;  
        IF l_sql IS NOT NULL THEN
           IF length(l_sql) >4000 THEN
              l_sql :=substr(l_sql,1,4000);
           END IF;
           INSERT INTO hand_test VALUES (l_sql, SYSDATE);
           COMMIT;
        END IF;   
    End Loop;
End;

 

 

关于作者:

昵称:商云方
档案信息:顾问, HAND张江技术中心
联系方式:你可以通过yunfang.shang@hand-china.com联系作者
点击查看商云方发表过的所有文章...
本文永久链接: http://blog.retailsolution.cn/archives/209

 

 

对本文的评价:

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading ... Loading ...

 

 

分类: DBA 标签: ,
  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.
您必须在 登录 后才能发布评论.