Home » Server Options » Replication » ORA-12004
ORA-12004 [message #74993] Mon, 29 January 2001 00:15 Go to next message
Gawie Opperman
Messages: 9
Registered: August 2000
Junior Member
Good afternoon,

We've got simple snapshots running, and they seemed to be fine (at least they where running without any hickups for a month now more or less) until one day some snapshots gave an ORA-12004 : REFRESH FAST cannot be used for snapshot. What can cause this? A complete refresh goes through fine. The other snapshots to other site on the same table works, it's only a few snapshots (4-6 out of 120) that isn't working.

Any suggestions would be appreciated.

Re: ORA-12004 [message #74994 is a reply to message #74993] Mon, 29 January 2001 03:45 Go to previous messageGo to next message
Messages: 23
Registered: January 2001
Junior Member

Regarding the Oracle Documentataion:

ORA-12004 REFRESH FAST cannot be used for snapshot "string"."string"

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.

Check the existence and validity of the snapshot logs on the master site.

Best regards,

Re: ORA-12004 [message #75504 is a reply to message #74994] Thu, 10 June 2004 10:57 Go to previous message
Shwan Mousawi
Messages: 1
Registered: June 2004
Junior Member
you are doing a recursive SQL, ie in the from clause you are calling the table twice.

otherwise use this SQL and it will let you know what is going on

select ss.name mview_name
, ss.refresh_method mview_type
, ss.last_refresh
, mv.last_refresh_type
, decode(mv.refresh_method||mvj.operator_type
, 'FASTI' , 'Fast'
, 'FORCEI', 'Force'
, 'FASTR' , 'Fast Right Outter Join'
, 'FORCER', 'Force Right Outter Join'
, 'FASTL' , 'Fast Left Outter Join'
, 'FORCEL', 'Force Left Outter Join'
, mv.refresh_method||mvj.operator_type
) refresh_method
, mv.build_mode
, decode( ob.status
, 'VALID', decode( ss.cr_operations
, 'REGENERATE', 'Require Complete Refresh'
, 'VALID', decode( mv.compile_state
, 'VALID', decode ( mva.unusable
, 'Y', 'MV is Unusables'
, decode(mva.known_stale
, 'Y', 'Unusable After Refresh'
, 'N', 'Valid'
, 'Require Complete Refresh'
, 'Error in Object rebuild'
) Status
, log.master table_name
, log.log_table mvlog
, trigger_name
from all_snapshots ss
, all_mviews mv
, all_objects ob
, all_triggers trg
, all_mview_logs log
, all_mview_detail_relations mdr
, all_mview_analysis mva
, all_mview_joins mvj
where ss.name like 'ENTER MV NAME'
and ss.name = mv.mview_name
and ob.object_name = mv.mview_name
and ob.object_type = 'MATERIALIZED VIEW'
and mv.owner = ob.owner
and trg.table_name(+) = ob.object_name
and trg.owner(+) = ob.owner
and mdr.mview_name = mv.mview_name
and mva.mview_name = mv.mview_name
and mva.owner = mv.owner
and mvj.detailobj1_relation = mdr.detailobj_name
and mvj.mview_name = mv.mview_name
and mvj.owner = mv.owner
and log.master(+) = mdr.detailobj_name
Previous Topic: Exchange for cert stuff, Transcender for MCAD/MCSD.net etc
Next Topic: Help from OCP's
Goto Forum:

Current Time: Mon Feb 20 00:16:03 CST 2017

Total time taken to generate the page: 0.04714 seconds