Trace an Oracle session from its beginning
BrunoVroman
Posts: 3,950 Gold Crown
Comments
-
A really handy document which is required every now and then in Oracle DB troubleshooting !0
-
A really handy document which is required every now and then in Oracle DB troubleshooting !
Hi Bruno,Interesting note. But what about the infamous WHEN OTHERS THEN NULL?? (which you have yourself condemned in some threads)
0 -
Hi Bruno,
Hello Gregory,Interesting note. But what about the infamous WHEN OTHERS THEN NULL?? (which you have yourself condemned in some threads)
thank you for your comment and question... Indeed normally I raise the red flag when I see "WHEN OTHERS THEN NULL" and I thought at this when submitting the document, but to keep it "simple" I didn't put the explanation immediately...
You have noted that I've put a remark stating the the "exception" block was important...
The idea is that this is a "on_logon trigger" that is subject to small mistakes (some readers might copy and transform it, for example to store some auditing info in a table...).
=> It can be that an error occurs while running the trigger ((although I don't imagine a scenario for my version)), and this would prevent ANY LOGON to the database ((except under SYS but this is something I didn't want to mention too loudly either)).
This is why I suggest to "ignore any error" in this special case.
Best regards,
Bruno.0 -
Hello Gregory,
Hi Bruno,
thank you for your comment and question... Indeed normally I raise the red flag when I see "WHEN OTHERS THEN NULL" and I thought at this when submitting the document, but to keep it "simple" I didn't put the explanation immediately...
You have noted that I've put a remark stating the the "exception" block was important...
The idea is that this is a "on_logon trigger" that is subject to small mistakes (some readers might copy and transform it, for example to store some auditing info in a table...).
=> It can be that an error occurs while running the trigger ((although I don't imagine a scenario for my version)), and this would prevent ANY LOGON to the database ((except under SYS but this is something I didn't want to mention too loudly either)).
This is why I suggest to "ignore any error" in this special case.
Best regards,
Bruno.
I believe, there is a practical implication here. Suppose many users connect to various part of applications using same user (SCOTT) internally. Hence, we can enhance this trigger by implementing several other conditions, e.g. :
IF user=SCOTT and sys_context(userenv, 'terminal') -- or . 'ip_address' / 'client_identifier' / 'client_info')
Cheers!
0 -
Hi Bruno,
Yes, sure! This is why I wrote my second remark "The trigger can contain more conditions (for example: if sysdate between XXX and YYY)"
I believe, there is a practical implication here. Suppose many users connect to various part of applications using same user (SCOTT) internally. Hence, we can enhance this trigger by implementing several other conditions, e.g. :
IF user=SCOTT and sys_context(userenv, 'terminal') -- or . 'ip_address' / 'client_identifier' / 'client_info')
Cheers!
Most of the time I use this trigger in "coordination" with a end user (over the phone): I create the trigger but disabled, I call the user, we synchronize:
I enable the trigger
he/she connects
I disable the trigger.
then the user does what has to be done, disconnects, and notifies me. -> I can run tkprof and investigate.
But sometimes indeed I add conditions (on OSUSER, but as you wrote there are many possibilities) when there is a risk of more sessions starting at the ~same time or when the user wants to repeat the test a few consecutive times (then I don't have to disable enable disable ... the trigger: enable, give the "go" to the user, wait until the user gives me the "finished", disable the trigger, go to the traces).
Best regards,
Bruno.
0
