My question is how to create an AFTER LOGON trigger based on your answer to a previous question about restricting logon times.
To create an AFTER LOGON trigger, you can use code similar to the following:
CREATE OR REPLACE TRIGGER my_after_logon_trig AFTER LOGON currtime DATE; sid V$SESSION.SID%TYPE; serial V$SESSION.SERIAL#%TYPE; killcmd VARCHAR2(100); BEGIN -- Get the current time SELECT sysdate INTO currtime FROM dual; -- Is the current time outside of 09:00 to 17:00? IF (TO_CHAR(currtime,'HH24MI') < '0900' || TO_CHAR(currtime,'HH24MI') > '1700') THEN -- Get user's SID and SERIAL# and kill their session SELECT sid,serial# INTO sid,serial FROM v$session WHERE audsid=USERENV('sessionid'); killcmd:='ALTER SYSTEM KILL SESSION '''; killcmd:=killcmd||sid||','||serial||'''; EXECUTE IMMEDIATE killcmd; END IF; END; /This should give you a start. You can modify the above trigger definition if it doesn't suit your exact needs.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.