?
Solved

How to use Oracle created snapshot refresh log manually.

Posted on 2001-07-26
3
Medium Priority
?
919 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 1

Expert Comment

by:ylyip
ID: 6325422
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 150 total points
ID: 6325703
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
ID: 6329286
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

764 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