0 Replies Latest reply: Feb 25, 2013 3:28 AM by Moazzam RSS

    Unable to extend undo tabespace issue in Materialized view on remote site

    Moazzam
      We are using Materialized view to synch data with remote site. During testing, when i inserted 5 million rows in the remote table then about 1.9GB undo space was used on the remote site during synchronization (FAST REFRESH).

      Following is the sample row that was inserted 5 million times on the remote site for testing:
            INSERT /*+APPEND*/
                  INTO test (UNIQUE_ID,
                                      t1,
                                      t2,
                                      t3,
                                      t4,
                                      t5)
            VALUES (i,
                    'TEST' || i,
                    'TEST' || i,
                    't',
                    't',
                    't');
      Following is the MV created on local site:
      CREATE MATERIALIZED VIEW test 
      ON PREBUILT TABLE WITH REDUCED PRECISION
      REFRESH FAST
      START WITH TO_DATE('25-Feb-2013 14:20:49','dd-mon-yyyy hh24:mi:ss')
      NEXT SYSDATE+(5/1440)   
      WITH PRIMARY KEY
      AS ......
      I want to know:
      1) why so much undo is being generated on remote site when data is being inserted in the local site. From remote site, Oracle is just reading MV log.
      2) Is there any way to reduce it because remote site is not in our control. We get unable to extend undo tablespace issue on remote site during testing.
      3) Is there any other strategy that we can adapt in such cases to synchronize the data.

      We are using Oracle 10g on Linux.