8 Replies Latest reply: Feb 2, 2012 4:01 AM by DavidGreenfield RSS

    Cube Refresh error

    Dileep
      I got below error while doing cube refresh. Dimension refresh went fine.

      <ERROR
      TEXT="ORA-33272: Analytic workspace TEST_AW.TEST_AW cannot be opened.
      ORA-33275: Autonomous transaction query of an UPDATEd analytic workspace is not supported
      "/>

      Olap version: 11.2.0.1
        • 1. Re: Cube Refresh error
          DavidGreenfield
          The build code uses "autonomous transactions" to log the progress of the build (e.g. into CUBE_BUILD_LOG or CUBE_OPERATIONS_LOG). I have never seen this go wrong before, but there is always a first time. So here are some questions.

          <li>What level of logging did you have enabled during the build?

          <li>What did you specify for the parallelism parameter of the build?

          <li>Is this a default cube build (e.g. "LOAD, SOLVE" or "CLEAR, LOAD, SOLVE") or have you added an OLAP DML step to the process? (e.g. "LOAD, EXECUTE OLAP DML '...', SOLVE").

          <li>Is there only one AW involved (TEST_AW), or were there multiple AWs attached?

          <li>Had you made any uncommitted changes to TEST_AW before you started the build?

          <li>Are there any other errors in the alert log? I am wondering if the "autonomous transaction" error is reported because of a failure to log some other error.
          • 2. Re: Cube Refresh error
            Dileep
            1) I used DBMS_CUBE to refresh cubes so I guess I used whatever comes as default because I did not update anything for logging specifically.

            2) 0

            3)It is a default one (LOAD,SOLVE)

            DBMS_CUBE.BUILD(
            'TEST_AW.CUBE1 , TEST_AW.CUBE2',
            'CC', -- refresh method
            false, -- refresh after errors
            0, -- parallelism
            true, -- atomic refresh
            true, -- automatic order
            false); -- add dimensions

            4) Yes..It is a single AW (TEST_AW)

            5) No. I did not have any uncommitted changes

            6) I see below error in alert log at the same time.

            XOQ-00699: internal error, arguments: [kgeade_is_0], [], [], [], [], [], [], []
            • 3. Re: Cube Refresh error
              DavidGreenfield
              Dileep,

              I think you'll need an SR for this. Please run the following before your PL/SQL build. It should put an error stack in the trace file. You can upload that to the SR and I can take a look.
              alter session set events = '33275 trace name errorstack level 1';
              David
              • 4. Re: Cube Refresh error
                Dileep
                Thanks David.

                I could not compile my pl/sql build package if I set this event. I am getting below error.

                PLS_00103: Encountered the symbol "ALTER' when expecting one of the following:
                • 5. Re: Cube Refresh error
                  Dileep
                  Never mind. I guess I can also run this statement outside of my plsql package.
                  • 6. Re: Cube Refresh error
                    Dileep
                    Created SR 3-5257284695 and uploaded trace files.
                    • 7. Re: Cube Refresh error
                      DavidGreenfield
                      I was able to reproduce this against the standard GLOBAL schema.
                      DECLARE
                        jobid VARCHAR2(100);
                      BEGIN 
                        SELECT DBMS_SCHEDULER.GENERATE_JOB_NAME('JOB$_') INTO jobid FROM DUAL;
                        DBMS_SCHEDULER.CREATE_JOB(
                          jobid, 'plsql_block', 
                          'begin 
                            dbms_cube.build(
                             ''units_cube, price_cost_cube'', 
                             atomic_refresh=>true, 
                             parallelism=>0);
                           end;',
                        0, null, null, null, 'DEFAULT_JOB_CLASS', true, true, 'CUBE Job'); 
                      END;
                      /
                      The build failed with the following error.
                      JOB$_544
                      FAILED
                      37162: ORA-37162: OLAP error
                      ORA-33272: Analytic workspace GLOBAL.GLOBAL cannot be opened.
                      ORA-33275: Autonomous transaction query of an UPDATEd analytic workspace is not
                      supported
                      ORA-06512: at "SYS.DBMS_CUBE", line 234
                      ORA-06512: at "SYS.DBMS_CUBE", line 287
                      ORA-06512: at line 2
                      The following combination appears to be necessary to hit this bug.

                      (1) You are running the build through the job scheduler;
                      (2) You specify atomic_refresh=>true OR there are cube MVs defined;
                      (3) You specify parallelism=>0

                      Technically the build code is running a SQL statement against a dimension view after an UPDATE but before the COMMIT. (This is a consequence of atomic_refresh=>true + parallelism=>0.) I don't understand why it works in the main session but not when run through the job scheduler. I will file a bug on this, but in the meantime I suggest you specify parallelism=1 or atomic_refresh=>false.
                      • 8. Re: Cube Refresh error
                        DavidGreenfield
                        I logged a bug for this problem.

                        Bug 13654451 - "ORA-33275: AUTONOMOUS TRANSACTION QUERY" ERROR DURING CUBE BUILD