Home » RDBMS Server » Performance Tuning » ORA-12015: cannot create a fast refresh snapshot from a complex query
ORA-12015: cannot create a fast refresh snapshot from a complex query [message #153033] Wed, 28 December 2005 09:11 Go to next message
sgv_0226
Messages: 1
Registered: December 2005
Junior Member
hi,

I'm trying to create a materialized view in db, DBM, based on joined tables from a remote db (DBR). The tables are accessed through synonyms:

bank_official@DBR - accessed through synonym dbr_bank_official
bank_none@DBR - accessed through synonym dbr_bank_none
CREATE MATERIALIZED VIEW
REFRESH FAST ON COMMIT
as Select bank_ont, st_date  
    from dbr_bank_official a, 
         dbr_bank_none b
   where a.bin = b.bin;

I'm having an ORA-12015 error. It tells that this is a complex materialized view. Is it not possible to have a fast refresh with a joined base tables from a remote db??

Initially, I created this materialized view as REFRESH FORCE ON DEMAND. And I created materialized view logs. After that, I recreated it to REFRESH FAST ON COMMIT - but it won't compile..

[Updated on: Wed, 28 December 2005 09:13]

Report message to a moderator

Re: ORA-12015: cannot create a fast refresh snapshot from a complex query [message #153038 is a reply to message #153033] Wed, 28 December 2005 09:46 Go to previous message
wpaillan
Messages: 35
Registered: March 2005
Location: Santiago - Chile
Member
HI


Here plenty of information on the theme exists

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96567/repmview.htm#31780

--------------------------------------------------------------

ORA-12015 cannot create a fast refresh materialized view from a complex query

Cause: Neither ROWIDs nor primary key constraints are supported for complex queries.

Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE option or create a simple materialized view.
-------------------------------------------------------

1.- This is a view's example materialized, that an hour refreshes itself automatically each

*********
DROP MATERIALIZED VIEW MM2;
CREATE MATERIALIZED VIEW MM2
REFRESH COMPLETE with rowid
START WITH sysdate
NEXT sysdate + 1/24
AS Select bank_ont, st_date
from dbr_bank_official a,
dbr_bank_none b
where a.bin = b.bin;
**********

2.- Another possibility is to refresh the view, road procedures, or each you see that you get busy
***********
procedure pppp
begin
dbms_mview.refresh('owner.MM2',null,null,true,false,1,0,0,true);
or
dbms_mview.refresh('owner.MM2');
--- Another DeclaraciĆ³n or you sentence pl-sql
end pppp;
/

williams
Previous Topic: query waits
Next Topic: Show remain cpu time to end Query in OEM
Goto Forum:
  


Current Time: Thu Mar 28 16:34:40 CDT 2024