Solved

How to use Oracle created snapshot refresh log manually.

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

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ā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

815 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

12 Experts available now in Live!

Get 1:1 Help Now