Materialized view refresh error(ORA-00942: table or view does not exist,ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821)

SQL>EXEC DBMS_MVIEW.REFRESH('TEST_MV','C','',FALSE,FALSE,0,0,0,TRUE);
BEGIN DBMS_MVIEW.REFRESH('DM_SALES_GROSS_NET_MV','C','',FALSE,FALSE,0,0,0,TRUE); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 1

SQL>

SOLUTION:

SQL>ALTER MATERIALIZED VIEW TEST_MV COMPILE;

Materialized view altered.

SQL>exec DBMS_MVIEW.REFRESH('DM_SALES_GROSS_NET_MV','C','',FALSE,FALSE,0,0,0,TRUE);

PL/SQL procedure successfully completed.

SQL>

Restore table from Recyclebin 11g/10g

I am Sharing my experience in recovery table from recyclebin

SQL> select object_name, original_name, type,droptime from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME    TYPE    DROPTIME
————————————-                   ————————–       ————    ————————
BIN$RVdHjbBfXI3gUwIAAH/nrg==$0    T1           TABLE    2016-09-05:01:32:05
BIN$RVVsfQkeTzfgUwIAAH8GCw==$0    T1           TABLE    2016-09-05:01:35:36


Here we can use two ways to restore the table

STEP 1:

This will restore the latest drop table to restore.

SQL> flashback table t1 to before drop;

Flashback complete.

STEP 2:

The below command to restore the selected recyclebin object to restore.

SQL> flashback table “BIN$RVdHjbBfXI3gUwIAAH/nrg==$0" to before drop;
Flashback complete.

SQL>