This section shows ways to refresh given table(s) data on daily or periodical basis.
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:
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.