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