This discussion is archived
2 Replies Latest reply: Oct 24, 2012 1:32 AM by Joan Puig RSS

"Invalid Metadata Objects" when creating materialized views

Joan Puig Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points