OraFAQ Forum: Performance Tuning » ORA-12015: cannot create a fast refresh snapshot from a complex query

Home » RDBMS Server » Performance Tuning » ORA-12015: cannot create a fast refresh snapshot from a complex query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-12015: cannot create a fast refresh snapshot from a complex query [message #153033]

Wed, 28 December 2005 09:11

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

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 07:43:19 CST 2024