Oracle refresh throws a table not found exception

Question

Having simple refresh script:

BEGIN 
DBMS_SNAPSHOT.REFRESH(
  LIST => 'SCHEMA_NAME.TABLE_NAME', 
  PUSH_DEFERRED_RPC => TRUE, 
  REFRESH_AFTER_ERRORS => FALSE, 
  PURGE_OPTION => 1, 
  PARALLELISM => 0, 
  ATOMIC_REFRESH => TRUE, 
  NESTED => FALSE); 
END;

Oracle argues that:

ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2426
ORA-06512: at line 2
00942. 00000 -  "table or view does not exist"

Question: what am I missing?

Note:

Refreshed MV does exist
if schema is not specified result is the same

have you tried wihout specifying schema? Only the materialized view_name? Can you put the ddl of MV as an update to question? — Florin Ghita, Jan 20 '12 at 10:30
This page may be helpful: http://www.dba-oracle.com/sf_ora_00942_table_or_view_does_not_exist.htm — Maxime Pacary, Jan 20 '12 at 10:31
@Frosy Z, it seems that one of the view tables has some broken dependencies. Thanx for help. — Denys S., Jan 20 '12 at 11:17

score 2 · Accepted Answer · edited May 23 '17 at 11:46

2

That is a common case of view, depending on some other tables or views (which might depend on other data), having broken dependencies. In this particular case the view depended on other view which depended on a table which was missing a column. Thanx to Frosty Z for giving a hint.

edited May 23 '17 at 11:46

Community

1
1

answered Jan 20 '12 at 14:35

Denys S.

6,358
12
43
65

score 1 · Answer 2 · answered Jan 08 '16 at 15:42

1

I have read elsewhere that performing a

ALTER MATERIALIZED VIEW schema_name.table_name COMPILE;

can fix this issue.

it didn't work for me and i have to drop and re-create the materialized view completely.

answered Jan 08 '16 at 15:42

ShoeLace

3,476
2
30
44

Oracle refresh throws a table not found exception

2 Answers2