1 2 Previous Next 27 Replies Latest reply on Feb 26, 2013 10:19 PM by 993537

    unable to update the existing table with schema owner and getting ORA-00942

    880047
      Hi All,

      I am working 11gR2 3node rac database. below are the db details.


      SQL> select * from v$version;

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE 11.2.0.3.0 Production
      TNS for Linux: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production


      Today i have encountered a wired issue, when i am trying to update on table, i am getting "ORA-00942: table or view does not exist". But the table is existing and i have logged in as a schema owner and doing this. I am able to do the DDL changes, but not DML changes (able to do select on this table not insert & updates).

      SQL> UPDATE ACCOUNT ACC SET ACC.LAST_SUCC_ACQ_TS =
      2 ( SELECT ACQ_START_TS
      FROM BANKING_STMT_TASK BST
      3 4 WHERE BST.STMT_ID IN (
      5 SELECT LAST_SUCC_BANKING_STMT_ID
      FROM BANKING_ACCOUNT_DETAIL BNK
      6 7 WHERE BNK.ACCOUNT_ID=ACC.ACCOUNT_ID ));

      UPDATE ACCOUNT ACC SET ACC.LAST_SUCC_ACQ_TS =
      *
      ERROR at line 1:
      ORA-00942: table or view does not exist

      After i got the error, i performed below tasks.
      1.
      SQL> select TABLE_NAME,status,LAST_ANALYZED,TABLE_LOCK from user_tables where table_name='ACCOUNT';

      TABLE_NAME STATUS LAST_ANAL TABLE_LO
      ------------------------------ -------- --------- --------
      ACCOUNT VALID 13-AUG-11 ENABLED


      table status is VALID.

      2. checked privileges
      this schema owner have unlimited tablespace quota and create session, resource privileges.


      3. found the trace file info


      Trace file info



      *** 2012-03-19 16:10:50.169
      *** SESSION ID:(1995.27517) 2012-03-19 16:10:50.169
      *** CLIENT ID:() 2012-03-19 16:10:50.169
      *** SERVICE NAME:(SYS$USERS) 2012-03-19 16:10:50.169
      *** MODULE NAME:() 2012-03-19 16:10:50.169
      *** ACTION NAME:() 2012-03-19 16:10:50.169

      ORA-12012: error on auto execute of job 64
      ORA-00942: table or view does not exist
      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
      ORA-06512: at "SYS.DBMS_IREFRESH", line 685
      ORA-06512: at "SYS.DBMS_REFRESH", line 195
      ORA-06512: at line 1


      This is very critical for me. Please provide some inputs to over come this issue and let me know for further information.

      Thanks in advance.

      Edited by: Hari on Mar 19, 2012 10:23 PM
        1 2 Previous Next