Home » Server Options » Replication » Query on DBMS_SNAPSHOT.DROP_SNAPSHOT
Query on DBMS_SNAPSHOT.DROP_SNAPSHOT [message #161821] Tue, 07 March 2006 06:01 Go to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
Hi all

I am having a snapshot log of approx 12 gb size.. containing >1000 Million orpahed entries.

I tried executing dbms_snapshot.purge_log command for purgin this log, but it generated great amount of redo, which I dont want to generate.

Checked a number of sites & came across dbms_snapshot.drop_snapshot pack.
Now my query is if this drop_snapshot procedure do generate redo or not?

Also, what can be the alternate ways for me to purge this log.
Will truncate do in this case on the mlog?
(Anyhow I'll be testing smae on my machine.)


Any suggestions/guidance will be highly appreciated.

--Girish
Re: Query on DBMS_SNAPSHOT.DROP_SNAPSHOT [message #161825 is a reply to message #161821] Tue, 07 March 2006 06:25 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
Oops... forgot to mention system info:

Win2k + sp4 server
Oracle 9204 EE.

--Girish
Re: Query on DBMS_SNAPSHOT.DROP_SNAPSHOT [message #162044 is a reply to message #161825] Wed, 08 March 2006 06:40 Go to previous message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
HI I have got the issue resolved.

The steps that i followed are as below:

1. Found all the materialized views registered on the master table (and MVIEW LOG)

select owner,name,mview_site,MVIEW_ID from dba_registered_mviews
where MVIEW_ID in
(
select SNAPSHOT_ID from dba_snapshot_logs where log_owner='<<mview log owner>>' an
d MASTER='<<master_table_name>>');
);


2. Unregistered the non-existing mviews (lst obtained in step 1):

EXECUTE DBMS_MVIEW.UNREGISTER_MVIEW('<<Owner>>','<<Mview name>>','<<Snapshot Site>>');

3.
a. LOCK the MASTER TABLE in EXCLUSIVE MODE.
b. TRUNCATE TABLE MLOG$_<<MASTER TABLE NAME>>;
c. DO A COMPLETE REFRESH FOR ALL THE MATERIALIZED VIEW ON THE MASTER TABLE.


--Girish
Previous Topic: Replication from Oracle to SQL Server
Next Topic: materialized view type is not supported by master site !!
Goto Forum:
  


Current Time: Tue Jul 27 10:15:55 CDT 2021