Prev: Free Webinars on PMP Certification Awareness and Roadmap
Next: prioritize inserts i.s.o replication
From: Sashi on 7 Jul 2008 10:11
Hi all,
I'm trying to update a materialized view:
EXECUTE DBMS_MVIEW.REFRESH('NED_PRODUCT_CARD_MV', 'C');

This commands hangs for a very long time (no idea if it'll ever
complete). Hm, reminds me of the halting problem.

When I terminate it with a Ctrl-C, I get the error message:
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
ORA-06512: at line 1


My version:
$ sqlplus -version

SQL*Plus: Release 9.2.0.6.0 - Production


We update this MV regularly (every few weeks) and this is the first
time that I'm seeing this.

Any pointers are appreciated.

Thanks,
Sashi
From: Sashi on 7 Jul 2008 12:30
On Jul 7, 10:11 am, Sashi <small...(a)gmail.com> wrote:
> Hi all,
> I'm trying to update a materialized view:
> EXECUTE DBMS_MVIEW.REFRESH('NED_PRODUCT_CARD_MV', 'C');
>
> This commands hangs for a very long time (no idea if it'll ever
> complete). Hm, reminds me of the halting problem.
>
> When I terminate it with a Ctrl-C, I get the error message:
> ERROR at line 1:
> ORA-12008: error in materialized view refresh path
> ORA-01013: user requested cancel of current operation
> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
> ORA-06512: at line 1
>
> My version:
> $ sqlplus -version
>
> SQL*Plus: Release 9.2.0.6.0 - Production
>
> We update this MV regularly (every few weeks) and this is the first
> time that I'm seeing this.
>
> Any pointers are appreciated.
>
> Thanks,
> Sashi

To answer my own question, this is quite another problem:
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01476: divisor is equal to zero
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
ORA-06512: at line 1

I don't know why it hanged for so long on earlier tries before
reporting this issue.
Updated the fields to take care of the divide by zero and then it
worked fine.
 | 
Pages: 1
Prev: Free Webinars on PMP Certification Awareness and Roadmap
Next: prioritize inserts i.s.o replication