Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

oracle logon trigger

Posted on 2011-09-19
6
Medium Priority
?
1,013 Views
Last Modified: 2013-12-19
Hello All,

I would like to track the users who are connecting to one schema (say "Scott") and I am NOT interested in tracking the users who are connecting to other schemas in the database. So can I execute this trigger in "scott" schema (and not in sys or system)? I am new to the system level triggers and I would like to know your expert comments if there is any downside or server impact due to this system level trigger? Do I need to restart the DB server for the trigger to work?

create or replace trigger
   logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats_user_log values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   sysdate,
   to_char(sysdate, 'hh24:mi:ss')
);
COMMIT;
END;
0
Comment
Question by:sivi_3883
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 752 total points
ID: 36560998
system triggers execute at the system level.  You need to add an IF statement to check the username and only perform the insert if user='SCOTT'.
0
 
LVL 7

Assisted Solution

by:Jacobfw
Jacobfw earned 248 total points
ID: 36561021
As indicated by @slightwv, an If statement will work.
Impact on performance will be minimal for this system trigger unless you have a lot of user traffic.  There is always some impact of adding a trigger, but the impact will mainly come from the Insert statement in this case, which is adding one record for each logon.  Not a lot of overhead.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 752 total points
ID: 36561151
I suppose I could have provided the information for the trigger.

I removed the commit because I cannot remember if this is allowed in a trigger.

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/triggers.htm#LNPLS794

create or replace trigger
   logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN

if ora_login_user = 'SCOTT' then
insert into stats_user_log values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   sysdate,
   to_char(sysdate, 'hh24:mi:ss')
);
end if;
END;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:johnsone
ID: 36561269
COMMIT is not allowed in a trigger and will produce an error.  I believe it is an ORA-04092.
0
 

Author Comment

by:sivi_3883
ID: 36561451
All,
Thanks for your valuable comments.
I agree COMMIT is not allowed in trigger. Since I am performing a DML statement(INSERT) inside the trigger, I would like to confirm this. I dont use COMMIT inside Row/statement level triggers since the procedure(which will trigger the event on the respective table) will have the COMMIT.
In this case, will DML statement(insert) be committed automotically by a system level trigger?
Does it need a server restart for this trigger to work?

0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 752 total points
ID: 36561469
I came across a commit in an after logon trigger from an old script I had laying around so it must have been allowed at one point in the past.

>>Does it need a server restart for this trigger to work?

No.


>>be committed automotically by a system level trigger?

Check the doc link I provided.

For AFTER LOGIN:
Starts separate transaction and commits it after firing triggers.
 
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question