Creating a materialized view with database link

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc

Poston Wed, 17 Oct 2012 3:19 pm

I'm trying to create a materialized view that queries a table that is accessed via a database link. I wish that this view was updated to 5:0 pm 5 minutes. The command I'm using is: CREATE MATERIALIZED VIEW MVIEW1 BUILD IMMEDIATE USING INDEX REFRESH complete ON DEMAND start with sysdate next sysdate + 5/1440 USING DEFAULT LOCAL ROLLBACK SEGMENT using enforced constraints ENABLE QUERY REWRITE the SELECT.NR1, IF.NR2, SS.NM FROM ADM. tab1 @ db2ROB, ADM. tab2 @ SS db2ROB WHERE.CD_E = ... AND IF.CD_P = ... AND IF.CD_SIT = SS.CD_SIT AND SS.CD_SIT NOT IN (...);

When I run gives the error ORA-01031, however the user has grant to create materialized view.
Oracle version of 2 databases is 11 g.
Robson Costa
Location: Campo Grande - MS

Poston Thu, 18 Oct 2012 10:40 am

Hi Robson, If you are creating the Materialized View with the user where this object is to be created, you will need the following privileges:-CREATE MATERIALIZED VIEW-CREATE TABLE-CREATE ANY TABLE-the user must have privileges DBLINK connection p/do SELECT the tables from the remote server;
-In the case above, run only SELECT the querie of the MV to see if it occurs no error;

Further details can be obtained from ORACLE's documentation: ... s_6002.htm Hugs, Sergio Coutinho
Location: Sao Paulo - SP

  • See also
    Last Post

Return to SQL

Who is online

Users browsing this forum: No registered users and 2 guests