Daily remote sync Oracle refresh group

One way to integrate Oracle systems is to share data between databases. In this example we will create a group of materialized views, which is then daily synced with a remote database.

In this scenario we have a couple of remote tables on an external Oracle database. The tables can be accessed using a database link we create.

create public database link <link_name>  connect to <schema>  using '<tns_service_name>';

The <link_name> variable is the name you want to use for the database link. The <schema> variable is the schema name of the remote database. The <tns_service_name> variable is the TNS-name of the remote database.

We fill in these variables with example data.

create public database link MY_DB_LINK;  connect to REMOTE_SCHEMA  using 'remote_database';

Now we create two materialized views of the two remote tables using the database link we just created.

CREATE MATERIALIZED VIEW m_table1 AS
SELECT col1, col2 FROM table1@remote_database;
CREATE MATERIALIZED VIEW m_table2 AS
SELECT col1, col2 FROM table2@remote_database;

Finally, we create the refresh group using the PL/SQL code below. The materialized views are refreshed every day at 6:00 in the morning.

begin
  dbms_refresh.make(
    name => 'mviews',
    list => 'm_table1,
    m_table2',
    next_date => to_date('06:00:00', 'hh24:mi:ss'),
    interval => 'sysdate+1'
  );
end;

To check if everything works, the following internal tables of Oracle can be queried to see if everything we created registered successfully by Oracle.

• dba_mviews
  
• dba_refresh
  
• dba_refresh_children
  
• dba_jobs
  

Sometimes the job just doesn't want to start. Maybe, the database is configured not allowed to start database jobs. Use the following query to check the number of jobs allowed to run on the database.

select t.value from v$parameter t
where name='job_queue_processes';

The number must be greater than 0. The standard value is 4. You can alter the value using:

alter system set job_queue_processes = 4;