Re: Truncate $mlog table without stop DBMS_jobs

On 28 Mar 2007 07:51:42 -0700, "Valentin Minzatu" <valentinminzatu_at_yahoo.com> wrote:

>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;

http://groups.google.com/group/comp.databases.oracle.server/browse_frm/thread/7241ac51491d4c59/e80567454747520a .......
We run Oracle 9iR2,10gR1/2 on RH4/RH3 and Solaris 10 (Sparc) remove NSPAM to email Received on Fri Mar 30 2007 - 00:04:09 CDT