HOME

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

Contact Me

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.


Solutions

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:

		
ALTER SESSION SET time_zone = 'AMERICA/CHICAGO';

CREATE MATERIALIZED VIEW MVW_DAILY_DATA
NOLOGGING
PARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE 
START WITH TO_DATE(to_char(trunc(sysdate),'dd-mon-yyyy')|| '02:00:00','dd-mon-yyyy hh24:mi:ss')
NEXT  sysdate + 1
AS
SELECT * 
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 10.2.0.3.0 - 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.
Thanks...

TOP