ORA-00942: table or view does not exist or Warning: View altered with compilation errors.

Error:

SQL> ALTER VIEW HR.TMP_TABLE_REORG COMPILE; Warning: View altered with compilation errors.

How to find out the cause:

Use the query below to find the query which makes the View

SQL> set long 10000 SQL> set pages 0 SQL> select dbms_metadata.get_ddl('VIEW','TMP_TABLE_REORG','HR') from dual;

Output will look like;

SQL>  select "TABLE_NAME","COLUMN_NAME","CONSTRAINT_NAME","T_TABLE_NAME","START_TIME  ","END_TIME","RELOAD_START_TIME","RELOAD_END_TIME","OBJECT_SIZE_MB","T_F_CW02TMP _MB","N_REQ_CW02TMP_MB","PRE_ORIG_ROWCNT","PRE_TMP_ROWCNT","POST_ORIG_ROWCNT","
P  OST_TMP_ROWCNT" from REORG_USR.tmp_table_reorg;
OST_TMP_ROWCNT" from REORG_USR.tmp_table_reorg

Try to run the “Select statement” which makes the query. You will get error message as; It means that physical table that view depends on does not exist cause the issue.

ORA-00942: table or view does not exist