6 Replies Latest reply: Feb 28, 2011 7:26 AM by 635471 RSS

    Materialized view log

    775803
      Hi I have create one materialized view and log table to extract data from remote database .

      CREATE MATERIALIZED VIEW test
      REFRESH FORCE START WITH SYSDATE NEXT SYSDATE + 1/720
      WITH PRIMARY KEY
      AS SELECT * FROM emp@dblink;


      CREATE MATERIALIZED VIEW LOG ON emp;

      The problem is log table is not purging. I have tried the link to resolve the problem but still problem persist. Log table is not purging after refresh

      http://aprakash.wordpress.com/2010/08/06/mview-log/

      When i viewed register table on remote database i.e.
      select * from user_registered_snapshots

      No_data_found comes.

      Why is table does not have any entry??I am very confused, when I tried same thing on local database with local source then log purging is working and registered_snapshots table has 1 entry but why not remote database.

      Please help.

      Edited by: Sheetal on Feb 23, 2011 8:48 PM
        • 1. Re: Materialized view log
          775803
          Please response.

          Edited by: Sheeti on Feb 24, 2011 4:28 AM
          • 2. Re: Materialized view log
            Sushil Kumar
            firstly drop log and then re-create:-

            drop materialized view log on emp;

            create materialized view log on emp;
            • 3. Re: Materialized view log
              775803
              Hi,
              I did in the same way but still log table is not purging.
              • 4. Re: Materialized view log
                635471
                From the docs:
                If the materialized view has remote tables in the FROM clause, all tables in the FROM clause must be located on that same site. Further, ON COMMIT refresh is not supported for materialized view with remote tables. Materialized view logs must be present on the remote site for each detail table of the materialized view and ROWID columns must be present in the SELECT list of the materialized view, ...
                • 5. Re: Materialized view log
                  775803
                  Thanks for the reply.
                  I think the issue is when i manually create Materialized view log table on remote database .Firstly I am creating log table on remote database and then materialized view of remote table on local database. I think because of this oracle does not able to make entry in user_registered_snapshots table and so will purging too because user_registered_snapshots table entry is very crucial for purging point view.
                  I cannot create log table on remote database from local database as ddl statement are not allowed on remote database.

                  Please help what to do this scenario or any other alternative. I just want fast refresh materialized view of remote database table with purging

                  Thanks.
                  • 6. Re: Materialized view log
                    635471
                    Sheeti wrote:
                    Thanks for the reply.
                    I think the issue is when i manually create Materialized view log table on remote database .Firstly I am creating log table on remote database and then materialized view of remote table on local database. I think because of this oracle does not able to make entry in user_registered_snapshots table and so will purging too because user_registered_snapshots table entry is very crucial for purging point view.
                    Use DBMS_MView.Register_MView if the remote MV is not registered..
                    Please help what to do this scenario or any other alternative. I just want fast refresh materialized view of remote database table with purging
                    The documentation states that you need to include the ROWID of the remote table in the materialized view select list if you want to use fast refresh.