[Oracle]REFRESH SNAPSHOT

當您發現您的 snapshot 沒有及時更新資料的時候，可以透過以下的指令，立刻幫您更新，達到現階段的資料同步！

EXEC DBMS_SNAPSHOT.REFRESH('SNAPSHOT_NAME');

參數比較多可以設定的語法是

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

有遇過這樣的問題

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"

網路上的解答是 不過還沒成功破解 ><
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.

In this case, you do not know which of the tables within the materialized view is throwing the ORA-00942 error.  This user was advised to enable SQL Trace (TKPROF) st the session level to determine the exact table that is seeing the ORA-00942 error.