oracle crash with ORA-00942: table or view does not exist

Question

we use oracle9i database. and since last week my oracle crash many times, after running for 30 hours more or less. we see errors repeat in my trc file and alert log :

qmhGetHTTPError: Got unknown oracle error. Error stack is:
ORA-00942: table or view does not exist

we do not find the exact table or view does not exist. if we upgrade our database to oracle 10g or 11g,will solve the problems? I found some error another log,

error 1242 detected in background process
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01114: IO error writing block to file 16 (block # 156925)
ORA-01110: data file 16: 'D:\ORACLE\ORADATA\SDH\SDH_DATA4.ORA'
ORA-27072: skgfdisp: I/O error
OSD-04008: WriteFile() 
O/S-Error: (OS 1450) 

 will above implies hard disk error? anyone gets an idea? thanks for all.

Answer 1

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;

Answer 2

Mostly, if the table really exists, the reason is, that the user under which the DML statement is executed, does not have permission to select from the table. If the user is not granted the select privilege, no upgrade will ever help.