[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How to use Oracle created snapshot refresh log manually.

Posted on 2001-07-26
3
Medium Priority
?
933 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

650 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