Trace a user's session from its beginning[@more@]
If you have to trace a "fast" user session, (for example, initiated by clicking on a button on the GUI of an application), you don't have enough time to query V$SESSION to initiate a trace for the session.
A very easy way is to use a database "on logon" trigger; here is an example:
CREATE OR REPLACE TRIGGER ON_LOGON_SCOTT
AFTER LOGON ON DATABASE
WHEN ( USER = 'SCOTT' )
BEGIN
execute immediate
'alter session set events ''10046 trace name context forever, level 12''';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
Remarks:
1) The “exception” block is of utmost importance to avoid big trouble if something goes wrong with the trigger.
2) The trigger can contain more conditions (for example: if sysdate between XXX and YYY)
3) Once the trigger is enabled, all new sessions of the user will be traced. To suspend this behaviour momentarily, just perform:
ALTER TRIGGER ON_LOGON_SCOTT DISABLE;
4) Suggestion: when not necessary anymore, drop the trigger instead of keeping it “disabled” forever (maybe somebody would re-enable it by mistake):
DROP TRIGGER ON_LOGON_SCOTT;
原文地址:http://www.dba-village.com/dba/village/dvp_tips.TipDetails?TipIdA=3764&SkipA=0&SortA=Date
A very easy way is to use a database "on logon" trigger; here is an example:
CREATE OR REPLACE TRIGGER ON_LOGON_SCOTT
AFTER LOGON ON DATABASE
WHEN ( USER = 'SCOTT' )
BEGIN
execute immediate
'alter session set events ''10046 trace name context forever, level 12''';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
Remarks:
1) The “exception” block is of utmost importance to avoid big trouble if something goes wrong with the trigger.
2) The trigger can contain more conditions (for example: if sysdate between XXX and YYY)
3) Once the trigger is enabled, all new sessions of the user will be traced. To suspend this behaviour momentarily, just perform:
ALTER TRIGGER ON_LOGON_SCOTT DISABLE;
4) Suggestion: when not necessary anymore, drop the trigger instead of keeping it “disabled” forever (maybe somebody would re-enable it by mistake):
DROP TRIGGER ON_LOGON_SCOTT;
原文地址:http://www.dba-village.com/dba/village/dvp_tips.TipDetails?TipIdA=3764&SkipA=0&SortA=Date
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/188692/viewspace-903287/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
快速查看表空间的使用情况
请登录后发表评论
登录
全部评论
