Skip to Main Content
  • Questions

  • I am creating Log Trail but result does'nt appered in log table.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Muhammad.

Asked: April 15, 2014 - 10:25 am UTC

Last updated: April 18, 2014 - 9:04 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Dear tom,
I am trying to create log trail but when i am using the below code and performs task logon and logoff to the database nothing appears in log table.

create table log_trail (name varchar2(30), time date, action varchar2(10));

create or replace trigger tr_logon
after logon on database
begin
insert into log_trail values (user, sysdate, 'LOGON');
commit;
end tr_logon;

create or replace trigger tr_logoff
before logoff on database
begin
insert into log_trail values (user, sysdate, 'LOGOFF');
commit;
end tr_logon;

Please guide me ASAP.

and Tom said...

please verify that:

ops$tkyte%ORA9IR2> show parameter _system_trig_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_system_trig_enabled                 boolean     TRUE


is true, if not:
alter system set "_system_trig_enabled"=true;


to enable system event triggers.


that said, there is NO REASON for you to code this trigger, you should simply enabled auditing and use that!


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 16, 2014 - 1:34 am UTC

Tom

Are you suggesting to use the undocumented parameter ?
Tom Kyte
April 16, 2014 - 4:24 pm UTC

I'm suggesting to make sure the feature is enabled. I'm suggesting that perhaps they turned it off. I'm suggesting that they turn on logon triggers in order to use logon triggers.

that is what I'm suggesting. they are supposed to be enabled. The only reason and the only way I got their example to reproduce is to disable a feature that should have been enabled.


so yes, that is what I'm suggesting - that they enable that which should have already been enabled. apparently they already DID use an undocumented parameter.





Trigger Parameter enabled to true

Muhammad Ishtiaq, April 18, 2014 - 11:53 am UTC

Dear Tom,
I tried to see parameter as enabled or disabled but nothing appeared in the command output.
Then i enabled parameter to true.
But still nothing appearing in the log table, in case of connect and disconnect.

Regards
Tom Kyte
April 18, 2014 - 9:04 pm UTC

oh, i know what else I did, i commented out your commit, that doesn't belong there.

If you review your alert log, you should see:

Fri Apr 18 17:03:06 2014
Errors in file /home/ora9ir2/admin/ora9ir2/udump/ora9ir2_ora_8665.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at line 3
Fri Apr 18 17:03:19 2014
Errors in file /home/ora9ir2/admin/ora9ir2/udump/ora9ir2_ora_8667.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at line 3



remove the commits and you'll see:

ops$tkyte%ORA9IR2> create table log_trail (name varchar2(30), time date, action varchar2(10));

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace trigger tr_logon
  2  after logon on database
  3  begin
  4  insert into ops$tkyte.log_trail values (user, sysdate, 'LOGON');
  5  --commit;
  6  end tr_logon;
  7  /

Trigger created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace trigger tr_logoff
  2  before logoff on database
  3  begin
  4  insert into ops$tkyte.log_trail values (user, sysdate, 'LOGOFF');
  5  --commit;
  6  end tr_logon;
  7  /

Trigger created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> connect /
Connected.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select * from log_trail;

NAME                           TIME      ACTION
------------------------------ --------- ----------
OPS$TKYTE                      18-APR-14 LOGOFF
OPS$TKYTE                      18-APR-14 LOGON




I forgot my first step was to get rid of the erroneous commits and then enable system triggers...


Logon and Logoff Triggers

Muhammad Ishtiaq, April 21, 2014 - 6:26 am UTC

Thanks Tom i have resolved the errors and now i can view the log trail at the time of connect and disconnect.
Now i can see the trail but it is looking little bit confusing because it is giving three rows at the time of logon and logoff.
1 ADMIN 4/21/2014 11:23:28 AM LOGON
2 ADMIN 4/21/2014 11:23:28 AM LOGOFF
3 ADMIN 4/21/2014 11:23:28 AM LOGOFF
4 ADMIN 4/21/2014 11:23:28 AM LOGOFF
5 ADMIN 4/21/2014 11:23:32 AM LOGON
6 ADMIN 4/21/2014 11:23:28 AM LOGON

Actually i am using third party tool on client and my database is on another machine.

A reader, November 06, 2014 - 8:33 pm UTC

You can commit in trigger only if you set autonomous_transaction pragma (define it). You don't need to commit in the trigger here

A reader, November 17, 2014 - 8:09 pm UTC

May be the tool is logging off and loggin in three times. Oracle works well.

A reader, November 17, 2014 - 8:10 pm UTC

To test that, Try logging in and logging out with your database id and see what the log table contains
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library