This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Feb 26, 2013 2:19 PM by 993537 RSS

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

880047 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points