This discussion is archived
5 Replies Latest reply: Apr 18, 2013 7:43 PM by Harvey RSS

Create materialized view on view

user590978 Newbie
Currently Being Moderated
Hi, I am trying to create a materialized view on view from different schema but i cannot create it with REFRESH FAST command as i need to refresh the materialized view only when any insert/update/delete happened.

I am able to create MV with refresh on demand or refresh complete. but i want data instantly whenever DML performed on base tables of view.

Can you please advice how i can achieve this ?

Thanks in advance!..

Here are the scripts.
View scripts:

select md.id_card_number as membership_number,
       md.person_code,
       mmtt.mem_trans_id as attempt_id,
       dl.proposal_doc_id,
       dt.description,
       ud.file_name,
       ud.file_location,
       ud.given_name,
       ud.org_user_id as org_user_id,
       ud.org_date_timestamp as org_date_timestamp
  from s_proposal_history         dl,
       frk_file_upload_details ud,
       s_map_members_to_trans  mmtt,
       s_member_details        md,
       s_document_types        dt
 where ud.doc_id = dl.proposal_doc_id
   --and dl.status_flag = 'A'
   and mmtt.mem_trans_id = dl.MEM_TRANS_ID
   and md.member_id=mmtt.member_id
   and dt.doc_type_id=54
   and id_card_number is not null
   and mmtt.mem_cov_status_id='1402'
union all
select md.id_card_number as membership_number,
       md.person_code,
       mmtl.source_id as attempt_id,
       mmtl.filename as proposal_doc_id,
       lm.description,
       mmtl.filename as file_name,
       null as file_location,
       mmtl.filename as given_name,
       mmtl.org_user_id as org_user_id,
       mmtl.org_date_timestamp as org_date_timestamp
from s_map_members_to_letters mmtl,
     s_letter_master lm,
     s_member_details md ,
     s_map_members_to_trans mmtt
where lm.letter_id = mmtl.letter_id
  and mmtl.status_flag = 'A'
  and trunc(mmtl.org_date_timestamp)>sysdate-10
  and md.member_id=mmtl.member_id
  and md.id_card_number is not null
  and mmtt.mem_cov_status_id=1402
  and md.member_id=mmtt.member_id;

MV script :

 CREATE materialized view mv_DOCUMENT_MAPPER 
  refresh  
  fast 
  next sysdate+1/1440
  AS SELECT *
  FROM [view];
  • 1. Re: Create materialized view on view
    rp0428 Guru
    Currently Being Moderated
    >
    Hi, I am trying to create a materialized view on view from different schema but i cannot create it with REFRESH FAST command as i need to refresh the materialized view only when any insert/update/delete happened.

    I am able to create MV with refresh on demand or refresh complete. but i want data instantly whenever DML performed on base tables of view.

    Can you please advice how i can achieve this ?
    >
    You haven't posted any exception. For a FAST refresh there are restrictions. Have you created the proper LOGS?
    >
    If you specify REFRESH FAST, then the CREATE statement will fail unless materialized view logs already exist for the materialized view master tables.
    >
    See the CREATE MATERIALIZED VIEW section of the SQL Language doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6002.htm

    >
    The EXPLAIN_MVIEW procedure of the DBMS_MVIEW package for help diagnosing problems with fast refresh and the TUNE_MVIEW procedure of the DBMS_MVIEW package correction of fast refresh problems
  • 2. Re: Create materialized view on view
    bencol Pro
    Currently Being Moderated
    I think you might want some rowids and markers in there
    http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#autoId35
  • 3. Re: Create materialized view on view
    user590978 Newbie
    Currently Being Moderated
    We cannot create the logs on master table as it's not in my schema. We only got view accessible and we want refresh from view to my table in my schema.
    It's just refresh from view to table.

    Please advice some other how can we achieve this. is there any other way to achieve it.

    I need incremental data only not the complete refresh.

    Please help!

    Thanks in advance!..
  • 4. Re: Create materialized view on view
    user590978 Newbie
    Currently Being Moderated
    We cannot create the logs on master table as it's not in my schema. We only got view accessible and we want refresh from view to my table in my schema.
    It's just refresh from view to table.

    Please advice some other how can we achieve this. is there any other way to achieve it.

    I need incremental data only not the complete refresh.

    Please help!

    Thanks in advance!..
  • 5. Re: Create materialized view on view
    Harvey Newbie
    Currently Being Moderated
    You can create the MATERIALIZED VIEW LOG from your user if you have the following privileges:
    COMMENT ANY TABLE, CREATE ANY TABLE and select on master table

    below is the example.
    User ofor2 have the following privileges:
    
    OWNER                TABLE_NAME                     PRIVILEGE
    -------------------- ------------------------------ ------------------------------
    HDHILLON             TEMPT                          SELECT
                                                        COMMENT ANY TABLE
                                                        CONNECT
                                                        CREATE ANY TABLE
    The table on which we will be creating the MATERIALIZED VIEW LOG is 
    SQL>  select owner, object_name, object_type from dba_objects where object_name ='TEMPT';
    
    OWNER                OBJECT_NAME     OBJECT_TYPE
    -------------------- --------------- -------------------
    HDHILLON             TEMPT           TABLE
    
    SQL> 
    
    SQL> show user
    USER is "OFOR2"
    SQL> create MATERIALIZED VIEW LOG ON hdhillon.tempt;
    
    Materialized view log created.
    
    SQL> 
    
    
    SQL> select m.mowner Owner, 
      2  m.master Tablename, 
      3  m.log Logname  
      4  from sys.mlog$ m, sys.slog$ s
      5  WHERE s.mowner (+) = m.mowner
      6  and s.master (+) = m.master ;
    
    OWNER      TABLENAME  LOGNAME
    ---------- ---------- ---------------
    HDHILLON   TEMPT      MLOG$_TEMPT
    
    SQL> select owner, object_name, object_type from dba_objects where object_name='MLOG$_TEMPT';
    
    OWNER                OBJECT_NAME     OBJECT_TYPE
    -------------------- --------------- -------------------
    HDHILLON             MLOG$_TEMPT     TABLE
    
    SQL> 
    This is the only way if you are create the MATERIALIZED VIEW LOG from user who is not the owner of the master table. Basically this will create the MATERIALIZED VIEW LOG under owner of the master.

    Morel of the story is that master table and MATERIALIZED VIEW LOG should be owned by same owner.

    Detail is under
    http://docs.oracle.com/cd/E14072_01/server.112/e10592/statements_6003.htm
    Prerequisites
    
    The privileges required to create a materialized view log directly relate to the privileges necessary to create the underlying objects associated with a materialized view log.
    
    If you own the master table, then you can create an associated materialized view log if you have the CREATE TABLE privilege.
    
    If you are creating a materialized view log for a table in another user's schema, then you must have the CREATE ANY TABLE and COMMENT ANY TABLE system privileges, as well as either the SELECT object privilege on the master table or the SELECT ANY TABLE system privilege.
    
    In either case, the owner of the materialized view log must have sufficient quota in the tablespace intended to hold the materialized view log or must have the UNLIMITED TABLESPACE system privilege.
    I hope this will help.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points