The error you're getting indicates that a query is trying to reference an object that doesn't exist or that the user doesn't have access to. If you started getting it a week ago and weren't getting it before that, something changed a week ago so that an object was dropped, a privilege was revoked, or a SQL statement was added that references an invalid object. It is unlikely that upgrading the database would resolve the error.
Can you determine the SQL statement that your application is issuing when it receives this error? If not, can you add additional logging so that you can determine what statement is generating the error? If your application doesn't log the SQL statements it generates, you could create a SERVERERROR
trigger (based on this trigger code). Obviously, you would probably want to log more than just the SQL statement.
create table stmt_err_log (
sql_stmt varchar2(1000)
);
CREATE OR REPLACE TRIGGER after_error
AFTER SERVERERROR ON DATABASE
DECLARE
pragma autonomous_transaction;
id NUMBER;
sql_text ORA_NAME_LIST_T;
v_stmt CLOB;
n NUMBER;
BEGIN
n := ora_sql_txt(sql_text);
--
IF n >= 1
THEN
FOR i IN 1..n LOOP
v_stmt := v_stmt || sql_text(i);
END LOOP;
END IF;
--
FOR n IN 1..ora_server_error_depth LOOP
IF ora_server_error(n) = '942'
THEN
insert into scott.stmt_err_log( sql_stmt )
values( v_stmt );
commit;
END IF;
END LOOP;
--
END after_error;
When adding multiple attributes, try:
DBMS_SCHEDULER.set_attribute(
name => 'first_job',
attribute => 'raise_events',
value => DBMS_SCHEDULER.job_succeeded + DBMS_SCHEDULER.some_attribute);
The valid values for event types (11gr2) is here. And according to docs, job_over_max_dur is enabled already for 11gr2.
Now, for 10g or 11gr1, I don't see job_over_max_dur in the list of event types raised by the scheduler. But you mentioned in earlier post that you will very soon migrate to 11gr2, so I assume you have a test box setup already that you can test this with 11gr2. If you really want to go down the rabbit hole with AQ, you can do more custom events, but it would probably be much easier to wait a bit until you're on 11gr2.
Edit
To add more info, first some basic scheduler stuff you can view to see whats running (or scheduled, or stalled, or whatever state):
select * from dba_scheduler_jobs
where owner = 'SOME_OWNER'
and job_name = 'SOME_JOB';
You can see if max_run_duration column is set and also the "raise_events" column will show a list of event types that are to be raised by each job. You should see "JOB_OVER_MAX_DUR" type here.
If you want to test if your db environment is handling the JOB_OVER_MAX_DUR properly, then you can setup a quick test:
BEGIN
-- setup simple scheduler job
--DBMS_SCHEDULER.DROP_JOB('EVENT_RAISING_JOB',false);
DBMS_SCHEDULER.create_job (
job_name => 'EVENT_RAISING_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
dbms_lock.sleep(70);
END;',
start_date => SYSTIMESTAMP,
end_date => SYSTIMESTAMP + 2, -- 2 day
repeat_interval => 'freq=daily; byhour=1',
enabled => TRUE);
DBMS_SCHEDULER.set_attribute(name=>'event_raising_job', attribute=>'max_run_duration', value=>interval '60' second);
DBMS_SCHEDULER.set_attribute(name => 'event_raising_job', attribute => 'raise_events',value => DBMS_SCHEDULER.job_all_events );
END;
-- setup email notification
exec dbms_scheduler.add_job_email_notification( job_name=>'EVENT_RAISING_JOB',recipients=>'someuser@somedomain.com', events=>'job_all_events');
-- launch job
exec dbms_scheduler.run_job('EVENT_RAISING_JOB',false);
You should get 3 emails that this job: STARTED, SUCCEEDED, and JOB_OVER_MAX_DUR
Best Answer
If you enable tracing for this specific error, a session trace file will be generated whenever it occurs. This will allow you to view the SQL that causes the error. You will probably need the cooperation of your DBA to do this, as it's likely only they will be able to activate the trace and access the trace dump directory.
Here's a link showing how to do this:
http://oracle.erkansaka.org/2011/09/how-to-trace-oracle-error.html
So for you that would be: