How to Resolve ORA-12004: REFRESH FAST cannot be used for snapshot

Fast refreshing a materialized view requires a view log existed on the master table to make it function well, if you got ORA-12004, this could happen when the view log cannot be accessed.

ORA-12004

...
Tue Oct 12 10:31:58 2010
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_snp1_12483.trc:
ORA-12012: error on auto execute of job 345
ORA-12004: REFRESH FAST cannot be used for snapshot "USER"."MASTER_TABLE"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 373
ORA-06512: at "SYS.DBMS_IREFRESH", line 437
ORA-06512: at "SYS.DBMS_REFRESH", line 169
ORA-06512: at line 1
...

It seemed that the fast refresh failed to be functional. Let's see the details of the error code ORA-12004:

$ oerr ora 12004
12004, 00000, "REFRESH FAST cannot be used for snapshot "%s"."%s""
// *Cause:  The snapshot log does not exist or cannot be used.
// *Action: Use just REFRESH, which will reinstantiate the entire table.
//          If a snapshot log exists and the form of the snapshot allows the
//          use of a snapshot log, REFRESH FAST will be available starting
//          the next time the snapshot is refreshed.

Rationale

According to MOS (ID 179469.1), there are several possibilities could cause ORA-12004. In my case, missing materialized view logs could be the most possible one. So first of all, we need to check the existence of the view log through the database link. In the following example, the master table is called master_table and the database link is called dblink.

SQL> select * from dba_snapshot_logs@dblink where master='MASTER_TABLE';

no rows selected

The above statement tests two things:

• The availability of the database link.
• The existence of the view log.

Obviously, the database link was alive but the view log was missing for some reasons.

Solution

Since the view log was missing, we should create it on the master table in the source database. Pelase see the example below.

SQL> CREATE MATERIALIZED VIEW LOG ON USER.MASTER_TABLE PCTFREE 60 PCTUSED 30 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE" WITH ROWID EXCLUDING NEW VALUES;

Let's check the view log again.

SQL> select master from dba_snapshot_logs@dblink where master='MASTER_TABLE';

MASTER
------------------------------
MASTER_TABLE

Now, the snapshot is back to normal.

Another way that we can confirm the view log is the root cause: If you are able to do complete refresh, but unable to do fast refresh. I think the major problem is on the materialized view log.

If the above solution is not helpful, please check MOS for more information.