5 Replies Latest reply: Apr 18, 2013 9:43 PM by Harvey RSS

    Create materialized view on view

    user590978
      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
          >
          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
            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
              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
                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
                  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.