Jobs do not Run Automatically (Doc ID 205477.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 8.1.7.0 and later
Information in this document applies to any platform.
***Checked for relevance on 24-Oct-2012***

Symptoms

Jobs submitted using the dbms_job package do not run automatically when you have a logon trigger which does a select from v$session. The snp process tries to run the job. But there are errors during the execution of the job and the job fails to execute. There are failures that are registered in the FAILURES column of user_jobs view.

CREATE OR REPLACE TRIGGER trigger_logon
AFTER LOGON ON DATABASE
DECLARE
sess number(10);
prog VARCHAR2(70);
BEGIN
sess := USERENV('SESSIONID');
SELECT program INTO prog FROM v$session WHERE audsid = sess;
INSERT INTO audit_table(session_id,user_id, logon_date, program)
VALUES (sess,user,sysdate,prog);
COMMIT;
END;
/



The important thing to note is the statement:

SELECT program INTO prog FROM v$session WHERE audsid = sess;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms