商云方的技术博客 (沪ICP备14046774号-1)

/*
快速抓取当前你关注的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;