Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Mview base on a view?

Mview base on a view?

From: Jay Hostetter <>
Date: Tue, 06 Apr 2004 10:21:44 -0400
Message-ID: <>

I was just wondering if it is a valid practice to create a materialized vie=
w based on a view.  I have some columns in a table that I want to replicate=
 to another database.  However, there are other columns in the table that I=
 want to hide from the remote database.  I am specifically concerned about =
somebody querying through my db link back to the source database.  So my da=
tabase link will only be able to access a view, which hides some of the col=
umns in the table.  I was browsing through the docs and didn't find this sc=
enario.  I was curious if anybody else does this or if there is another met=
hod I should use.

Thank you,

/* In Local Database */

create table user_list (

  u_name varchar2(30),
  u_password varchar2(30),
  u_created_date date


create materialized view log on user_list; grant select on mlog$_user_list to remote_user;

create view vu_user_list as
  select u_name,u_created_date

     from user_list;

grant select on vu_user_list to remote_user;

/* In remote database */

create database link source_db connect remote_user identified by secretpwd = using 'db1';

create materialized view vu_user_list
  refresh with rowid
  as (select * from user_a.vu_user_list_at_source_db);

alter materialized view vu_user_list refresh fast;


This e-mail message and any files transmitted with it are intended for the =
use of the individual or entity to which they are addressed and may contain=
 information that is privileged, proprietary and confidential. If you are n=
ot the intended recipient, you may not use, copy or disclose to anyone the =
message or any information contained in the message. If you have received t=
his communication in error, please notify the sender and delete this e-mail=
 message. The contents do not represent the opinion of D&E except to the ex=
tent that it relates to their official business.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Tue Apr 06 2004 - 09:17:49 CDT

Original text of this message