Re: Oracle materialized view strange problem.

From: Jack <amitkr.3_at_gmail.com>
Date: 25 Apr 2006 10:00:26 -0700
Message-ID: <1145984425.948484.108810@i40g2000cwc.googlegroups.com>

Looks like its fixed now... (still monitoring for a couple of days)...

analyzed the materialized view and the index.... the query executes in seconds now...

Also on the master site there where two snapshots registered against MASTER MY_MASTER_TABLE that
have not been refreshed for quite a while, which is causing the snapshot log on this table to grow - currentl 1.3 million rows :

SQL> SELECT SNAPSHOT_ID, CURRENT_SNAPSHOTS FROM DBA_SNAPSHOT_LOGS 2 WHERE MASTER='MY_MASTER_TABLE'; SNAPSHOT_ID CURRENT_SNAPSHOTS

----------- -------------------
         61 13-03-2006 07:55:52
         83 03-04-2006 11:50:58
        171 25-04-2006 15:06:50

SNAPSHOT id's 61 and 83 are no longer in dba_registered_snapshots ie SQL> SELECT * FROM DBA_REGISTERED_SNAPSHOTS WHERE SNAPSHOT_ID=61; no rows selected

Elapsed: 00:00:00.01
SQL> SELECT * FROM DBA_REGISTERED_SNAPSHOTS WHERE SNAPSHOT_ID=83; no rows selected

These snapshots need to be purged from the snapshot log.

I purged them.. lets see how it goes

Thanks Received on Tue Apr 25 2006 - 12:00:26 CDT