I want to create a trigger that restricts a user's logon after the number of sessions reaches a point. If, let's say my max number of sessions allowed is 20 sessions and user 'SCOTT' has 14 sessions already, then whenever SCOTT tries to logon into database (or create a new session), the trigger should check the most obsolete session created by SCOTT and kill it so that SCOTT can continue to log on. I have created a sample trigger, however it doesn't meet my expectation. Please find the sample script as below:
CREATE OR REPLACE TRIGGER my_after_logon AFTER LOGON ON DATABASE DECLARE usernm VARCHAR2(50); sid NUMBER; serial NUMBER; v_count NUMBER; BEGIN -- Get username SELECT user INTO usernm FROM dual; -- Get number of current sessions SELECT count(*) into v_count FROM v$session; IF v_count > 4 and usernm='SCOTT' THEN -- Get the obsolete session and -- Get SID and SERIAL# of the obsolete session SELECT sid,serial# INTO sid,serial from v$session WHERE username=usernm and to_char(logon_time,'dd-mon-yyyy hh24:mi:ss')= (select min(to_char(logon_time,'dd-mon-yyyy hh24:mi:ss')) from v$session where username='DSS') ; --IF usernm='SCOTT' THEN EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION '''||sid||','||serial||''''; --END IF; END IF; END; /I created the trigger in SYS account. Could you please advise me on how the trigger should be?
Your code has "where username='DSS'". Why the DSS user? Shouldn't this user be SCOTT? That is your most likely error in the logic.
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.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.