Refresh table(s) data on daily basis from remote database

This section shows ways to refresh given table(s) data on daily or periodical basis.

Business Requirement

Data from a remote database, fetched via a complex query (which might be time consuming), is required in current database on daily basis.


Below are some possible solutions to meet the business requirement.

Solution 1 TOP

Foremost and a nice solution is creating a Materialized view (MV).

The MV will run the complex query, periodically as per given refresh frequency, to fetch data from from a remote database via a DB link.

A typical example (like shown below), in which the MV will refresh at 2 AM CT on daily basis:


START WITH TO_DATE(to_char(trunc(sysdate),'dd-mon-yyyy')|| '02:00:00','dd-mon-yyyy hh24:mi:ss')
NEXT  sysdate + 1
FROM 	A@dblink a , B@dblink b, C@dblink c 
WHERE 	a.id = b.id 
AND 	b.id = c.id ; 

And then the MV can be queried easily as any regular table.

But interestingly, while following this approach, I stumbled upon a MV bug in Oracle 10g.

My DB version is - 64bi

Take above example, when I created the Materialized View MVW_DAILY_DATA, it got created quickly and I was able to query it successfully.
But I couldn't drop it, the drop command just kept waiting.
The locked objects query showed that the MV was locked in row exclusive mode.

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, 
b.status,b.osuser, b.machine,b.program,
decode(LOCKED_MODE,0,'None',1,'Null',2, 'Row Share',3,'Row Exclusive',4, 'Share',
		5,'Sub Share Exclusive',6,'Exclusive',locked_mode) as locked_mode   
FROM v$locked_object a, v$session b, dba_objects c  WHERE b.SID = a.session_id AND a.object_id = c.object_id;

Upon contacting DBA, I came to know that there was a thread running in remote DB (@dblink) which was doing full table scan on huge table C.
Also, a query on USER_JOBS showed me that the value in TOTAL_TIME column was constantly increasing for that job, meaning the job was running continuously, without even reaching its run scheduled time.
So to unlock it (so that I can drop the MV), DBA killed the session in remote DB and I immediately dropped the MV in my current database to get rid of it.

But I have used same solution in 11g database and never faced this bug there. For multiple tables/queries, need to create as many materialized views, specifying the respective refresh times in each MV.
Its the preferred solution for me since 11g.

Solution 2 TOP

Another method is as follows (which I followed in my 10g database where I encountered the above mentioned MV bug) :

i) First create a stored procedure in which we can dynamically drop & re-create table via CTAS OR truncate and insert into table using the remote DB query.

ii) Then schedule the procedure via DBMS_SCHEDULER to run as per desired frequency.

This solution provides the same functionality. It has an advantage that multiple tables can be refreshed inside a single procedure, but they all will get refreshed at the same time as the scheduler job. Works well in both 10g and 11g.

I hope this article will help the readers to meet the given business requirement via Oracle PL/SQL.