4 Replies Latest reply: Feb 21, 2014 11:23 PM by user591655 RSS

Options of how to create materialized views

vitkalov Newbie
Currently Being Moderated

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 Pro
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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
    user591655 Journeyer
    Currently Being Moderated

    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,

     


Legend

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