Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Truncate $mlog table without stop DBMS_jobs

Re: Truncate $mlog table without stop DBMS_jobs

From: Valentin Minzatu <valentinminzatu_at_yahoo.com>
Date: 28 Mar 2007 07:51:42 -0700
Message-ID: <1175093502.409892.80700@p15g2000hsd.googlegroups.com>


On Mar 27, 12:36 pm, "jshen...._at_gmail.com" <jshen...._at_gmail.com> wrote:
> hi,
>
> we run a Oracle8.1.7 database whose content is snapshoted to other
> three satallite oracle DB. The refreshing period is 5min. We set up
> DBMS_jobs to execute refreshing automatically.
>
> Last Saturday, we falled into Oracle software bug ORA-12008 error in
> materialized view refresh path( ORA-00001 ORA-02291 ORA-02292), and
> automatic refreshing failed. After doing full refreshing, automatic
> refreshing recovered. But, we found one of $mlog table is much bigger
> than usual. I want to truncate the table to improve performance.
>
> Someone suggested, before $mlog truncating operation DBMS jobs and
> other applications should be stopped. Because applications may modify
> dictionary of snapshotted item. A manual full refreshed should be
> executed before truncate operation.
>
> As execution time of DBMS_jobs on three satallite DB do not
> coincide with each other.
>
> Is that possible to do truncation operation without stoppting any
> application and DBMS_jobs?
>
> Joe

Not sure if this would work in 8i, but I would try: ALTER TABLE <mvlog> MOVE; Received on Wed Mar 28 2007 - 09:51:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US