2 Replies Latest reply: Oct 24, 2012 3:32 AM by Joan Puig-Oracle RSS

    "Invalid Metadata Objects" when creating materialized views

    Joan Puig-Oracle
      Hi experts,

      I have run into some trouble. I had an analytic workspace that grew too fast (see 11.2.0.2 AW size grows steadily with every cube build so I deleted it and created a new one.

      It seemed to build fine using the tip that David Greenfield gave us in the mentioned forum post, but when I try to enable materialized views (which I had enabled in the previous workspace) I'm gettig the following error:

      -----
      Your metadata changes have been saved, with the following errors
      Invalid Metadata Objects:
      Invalid Object "TABLESPACE.LECTURAS": "CREATE MATERIALIZED VIEW "TABLESPACE"."CB$LECTURAS"
      ORGANIZATION CUBE ON TABLESPACE.TABLESPACE_AW(
      FACT "LECTURAS_STORED"("LECTURAS_MEASURE_DIM" 'LECTURA') IS "LECTURA",
      DIMENSION "TIEMPO" IS "TIEMPO" USING "TIEMPO_TIEMPO_HOUR_ID_UNIQUE_KEY" ,
      DIMENSION "GEOGRAFIA" IS "GEOGRAFIA" USING "GEOGRAFIA_GEOGRAFIA_CONTADOR_ID_UNIQUE_KEY" )
      BUILD DEFERRED
      REFRESH ON DEMAND
      FORCE
      USING TRUSTED CONSTRAINTS
      AS
      (
      SELECT
      TO_CHAR(T1."FEC_LECTURA", 'dd/mm/yyyy hh24:mi:ss') "TIEMPO",
      T1."COD_METERID" "GEOGRAFIA",
      SUM(T1."VAL_AI_HOR") "LECTURA"
      FROM
      TABLESPACE."LECTURA_HORARIA_FINAL" T1
      GROUP BY
      (TO_CHAR(T1."FEC_LECTURA", 'dd/mm/yyyy hh24:mi:ss') , T1."COD_METERID")
      )
      ORA-00942: table or view does not exist
      "
      -----

      Running this same script in SQLDeveloper yields the same error at line 17, which is the FROM clause. BUT I can run the SELECT statement by itself and returns the expected result. So the table exists in the correct tablespace.

      I must be missing something big...

      Thanks in advance.

      Joan

      P.S.: In the code above I'm using "TABLESPACE" in substitution for the real username and tablespace name (which is the same) for privacy reasons.
        • 1. Re: "Invalid Metadata Objects" when creating materialized views
          DavidGreenfield
          When you ran the select statement, were you connected as the same user that you used to try to enable the MVs?

          Can you create a standard (non cube) MV with the same select statement? (Connected as the same user you used in AWM.)
          "CREATE MATERIALIZED VIEW "TABLESPACE"."MV_TEST"
            BUILD DEFERRED
            REFRESH ON DEMAND
            FORCE
            USING TRUSTED CONSTRAINTS
          AS
          (
            SELECT
             TO_CHAR(T1."FEC_LECTURA", 'dd/mm/yyyy hh24:mi:ss') "TIEMPO",
             T1."COD_METERID" "GEOGRAFIA",
             SUM(T1."VAL_AI_HOR") "LECTURA"
            FROM
             TABLESPACE."LECTURA_HORARIA_FINAL" T1
            GROUP BY
             (TO_CHAR(T1."FEC_LECTURA", 'dd/mm/yyyy hh24:mi:ss') , T1."COD_METERID")
          ) 
          {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
          • 2. Re: "Invalid Metadata Objects" when creating materialized views
            Joan Puig-Oracle
            Hi David,

            I guess I rushed too much when I posted this on the OLAP forum. Creating a regular MV (using the code you provided) didn't work either. The SELECT statement still works, though.

            Yes, I was using the same user. In fact, I'm using the same SQL Developer tab.

            F5 to run the whole script results in ORA-00942. Selecting the inner SELECT statement and Ctrl+Enter to run that section only gives the correct result (without creating the MV, of course).

            BUT

            Your post gave me a broader view of the problem, so after doing some research I stumbled upon this:
            http://dbaspot.com/oracle-server/66632-create-materialized-view-table-doesnt-exist.html

            There was a Materialized View Log called just like my table (which I didn't create, at least not voluntarily), but no materialized view with that name. I assume the DB saw the log and looked for the MV instead of the table, but only when the statement is a subquery. Weird.

            I know it's hard, but more specific errors would come in handy, i.e. "ORA-XXXXX: Found MV log, but MV does not exist"

            Thank you for your time, and my apologies for posting too quick.

            Joan


            UPDATE: The MV Log was created by running the script suggested by the Relational Schema Advisor in AWM. The problem came after dropping the AW and creating it again manually. I've run the Relational Schema Advisor again and it created the MV Log, but this time I don't get the ORA-00942 when enabling or disabling MV Refresh. Maybe because the MVs are already created?

            I'm not sure if I want to get to the bottom of this, just in case...

            Edited by: JPuig on Oct 24, 2012 1:32 AM