4 Replies Latest reply: Feb 22, 2014 1:23 AM by Renu-Oracle RSS

    Options of how to create materialized views

    vitkalov

      Hi,

       

      I'm writing to ask an advice/idea for one task I've got.

      I need to create materialized views on remote Oracle database (this is Amazon RDS) but connection is not stable enough to transport/create whole views I need.

      The message I get:

      ERROR at line 1:

      ORA-03135: connection lost contact

      Process ID: 24227

      Session ID: 48 Serial number: 593

       

      Can you advise any other options/ideas to do this task?

        • 1. Re: Options of how to create materialized views
          spajdy

          You can use BUILD DEFERRED clause in CREATE MATERIALIZED VIEW statement.

          Then MV is created and data are not populated. You need to perform MV refresh to polulate data. But question is if this refresh will be sucessful because of unstable connection.

          Second option is on primary DB create a table with data export it (using exp or DataPump). Transfer exportted data to RDS and import them there. Al last you create MV on prebuild table.

          • 2. Re: Options of how to create materialized views
            vitkalov

            Hi Spajdy,

             

            Thank you for you answer.

            Yes, DEFERRED could be useful, I'll try this option, but I didn't get the idea with export/import. If I import source table to the destination host and build a view based on that table (on the destination host) will I be able to change the datasource for mview to the original (remote) database?

             

            Cheers

            • 3. Re: Options of how to create materialized views
              spajdy

              When you create MV then ORACLE create undelying table and put some others data in datadictionary about MV and load data from remote DB to local table.

              When you create MV with deffered clause then ORACLE create undelying table and put some others data in datadictionary about MV .

               

              When you create MV on prebuild table then ORCALE use as underlaying table an existing table and put some others data in datadictionary about MV. If this table containt data then created MV contain data. And this table you can create using exp/imp utility.

              • 4. Re: Options of how to create materialized views
                Renu-Oracle

                Hi,


                set SQLNET.EXPIRE_TIME in sqlnet.ora in database server


                 

                Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes,

                to send a probe to verify that client/server connections are active. Setting a

                value greater than 0 ensures that connections are not left open indefinitely,

                due to an abnormal client termination. If the probe finds a terminated

                connection, or a connection that is no longer in use, it returns an error,

                causing the server process to exit. This parameter is primarily intended for

                the database server, which typically handles multiple connections at any one time.

                 

                Thanks,