7 Replies Latest reply: Mar 6, 2013 10:56 PM by Hussein Sawwan-Oracle RSS

    Error on compile the Materialized view refresh

    Muthu
      Hello all,

      I am getting issue while compile the Materialized view refresh.

      ALTER MATERIALIZED VIEW FII_GL_AGRT_SUM_MV COMPILE;--( did successfully)

      exec DBMS_MVIEW.REFRESH('APPS.FII_GL_AGRT_SUM_MV','C');

      Error:

      ORA-12008: error in materialized view refresh path
      ORA-01013: user requested cancel of current operation
      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
      ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720
      ORA-06512: at line 1

      Please tell me what could be the issue

      Thanks and Regards,
      Muthu
        • 1. Re: Error on compile the Materialized view refresh
          shreevat
          Check these links

          http://ora-12008.ora-code.com/

          metalink Master Note for MVIEW 'ORA-' error diagnosis. For Materialized View CREATE or REFRESH, DBMS_MVIEW [ID 1087507.1]

          Thanks
          Shree
          • 2. Re: Error on compile the Materialized view refresh
            Muthu
            Hello all,

            I am using R12.1.3.

            While execute the below command, its not completing..

            exec DBMS_MVIEW.REFRESH('APPS.FII_GL_AGRT_SUM_MV','C');

            I found the reason why its not completing. Because its Refresh In Progress.


            SQL> select u.name owner,o.name mview_name, s.obj#,lastrefreshdate
            *2 from sys.obj$ o, sys.sum$ s, sys.user$ u*
            *3 where u.user# = o.owner# and o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;*

            OWNER                          MVIEW_NAME                           OBJ# LASTREFRE
            ------------------------------ ------------------------------ ---------- ---------
            APPS                           FII_GL_AGRT_SUM_MV                 438268 06-MAR-13



            SQL> select decode(bitand(s.mflags,8), 8,'Refresh In Progress','Not being refreshed')
            *2 from sys.obj$ o, sys.user$ u, sys.sum$ s*

            *3 where o.obj# = s.obj# and o.owner# = u.user# and o.type# = 42*
            *4 and o.name='FII_GL_AGRT_SUM_MV';*

            DECODE(BITAND(S.MFL
            -------------------
            Refresh In Progress

            How to stop Refreshing or is there any other solution for this issue?

            Please clarify me...

            Thanks and Regards,
            Muthu
            • 3. Re: Error on compile the Materialized view refresh
              Hussein Sawwan-Oracle
              Muthu,

              Please see (Unable To Proceed With Upgade Due To Materiealized View Refresh That Never Completes [ID 1467392.1]).

              Thanks,
              Hussein
              • 4. Re: Error on compile the Materialized view refresh
                Muthu
                Thank you Hussein for your timely reply....

                You gave correct support note which is exactly matching to my issue.

                Since I'm a Functional, I couldn't understand the solution.

                Please give the command (sql or plsql) to clear the process.

                Thanks and Regards,
                Muthu
                • 5. Re: Error on compile the Materialized view refresh
                  Hussein Sawwan-Oracle
                  Muthu,
                  Thank you Hussein for your timely reply....

                  You gave correct support note which is exactly matching to my issue.

                  Since I'm a Functional, I couldn't understand the solution.

                  Please give the command (sql or plsql) to clear the process.
                  Please run the last two queries under "Cause" section and post the output here.

                  Thanks,
                  Hussein
                  • 6. Re: Error on compile the Materialized view refresh
                    Muthu
                    Thank you Hussein for your response...

                    I herewith attached the query output which you asked.

                    SQL> select * from v$mvrefresh;

                    no rows selected

                    SQL> select * from dba_jobs_running;

                    no rows selected

                    SQL>


                    Thanks and Regards,
                    Muthu
                    • 7. Re: Error on compile the Materialized view refresh
                      Hussein Sawwan-Oracle
                      Muhtu,

                      Please see these docs.

                      How to Cancel a Refresh Operation of a Materialized View (MV) [ID 806833.1]
                      How to Monitor the Progress of a Materialized View Refresh (MVIEW) [ID 258021.1]

                      Thanks,
                      Hussein