Solved

How to use Oracle created snapshot refresh log manually.

Posted on 2001-07-26
3
898 Views
Last Modified: 2007-11-27
Is it possbile to manually read Oracle's snapshot (materiallized view) refresh log?  I would like to allow Oracle to automatically capture changes to a master table in its snapshot log file, then, use that log to manually update another series of tables based on what changed in the first master table.  In particular, the deletes done in the master I may need to remove from secondary tables.
0
Comment
Question by:gnome43
3 Comments
 
LVL 1

Expert Comment

by:ylyip
Comment Utility
I am not sure, but can you create a trigger on the snapshot to detect for any inserts, deletes and updates?
0
 
LVL 3

Accepted Solution

by:
UsamaMunir earned 50 total points
Comment Utility
Hi!
Here is what you can do.

1- Create a Snapshot Log on some table. We create it on Emp Table.

SQL> create snapshot log on emp;

Materialized view log created.


2- Have a look at the 'USER_SNAPSHOT_LOGS'

SQL> desc user_snapshot_logs;
 Name                          
 -----------------------------
 LOG_OWNER                      
 MASTER                        
 LOG_TABLE                      
 LOG_TRIGGER                    
 ROWIDS                        
 PRIMARY_KEY                    
 FILTER_COLUMNS                
 CURRENT_SNAPSHOTS              
 SNAPSHOT_ID                    


3- See The LOG_TABLE from 'USER_SNAPSHOT_LOGS' it is the name of the LOG created on the EMP table.

SQL> select LOG_TABLE from user_snapshot_logs
  2  where log_owner = 'SCOTT';

LOG_TABLE
------------------------------
MLOG$_EMP

Note. The Name of The table on which the log is created will be prefixed with the LOG_TABLE name.

4- See The Structure of MLOG$_EMP?

SQL> desc MLOG$_emp;
 Name                  
 ---------------------
 EMPNO                
 SNAPTIME$$            
 DMLTYPE$$              
 OLD_NEW$$              
 CHANGE_VECTOR$$        

in this table M_ROW$$ is the ROWID of the row on which the DML is performed, and type of DML will be found in 'DMLTYPE$$' column.

5- Create an After Insert trigger on MLOG$_EMP.
coz no matter what DML happens on EMP table MLOG$_EMP table will always encounter an insert

SQL> create or replace trigger man_log
  2  after insert on MLOG$_EMP
  3  for each row
  4  begin
  5  if :new.DMLTYPE$$ = 'D' then --delete has occured--
  6  delete from emp1 where EMPNO = :new.EMPNO --- delete from another table
  7  end if;
  8  end;
  9  /

Trigger created.

6- Now Delete something from emp table and when u refresh the snapshot like DBMS_SNAPSHOT.REFRESH_ALL all rows deleted from emp will also be delete from emp1.


I hope it helps a Bit

Happy Hunting Partner

U




0
 
LVL 2

Expert Comment

by:FrodoBeggins
Comment Utility
I think you can play with

CREATE SNAPSHOT LOG INCLUDING NEW VALUES....

Then tne log itself will contain the data you need
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now