Solved

How to use Oracle created snapshot refresh log manually.

Posted on 2001-07-26
3
911 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 50 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

Industry Leaders: 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

Suggested Solutions

Title # Comments Views Activity
pivot rows to columns 1 59
Oracle mutateing errors 3 23
Oracle database T-1 Setup 7 44
scheduler notification 9 75
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

738 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