DronaBlog

Monday, December 10, 2018

Issues noticed during Materialized View Creation - ORA-23413: table "CMX_ORS"."C_B_CUST" does not have a materialized view log



Did you encounter with ORA-23413 the Oracle database error while creating a Materialized view? Are you also noticing ORA-01031: insufficient privileges error? If yes, then refer below article about explanation and solution to these database errors?

SQL Error: ORA-23413: table "CMX_ORS"."C_B_CUST" does not have a materialized view log

Explanation: This type of error occurs if you try to create a Materialized view on table C_BO_CUST without creating the Materialized View Log. It is mandatory to create log definition first before creating actual Materialized View.

Error Message:
SQL Error: ORA-23413: table "CMX_ORS"."C_B_CUST" does not have a materialized view log
23413. 00000 -  "table \"%s\".\"%s\" does not have a materialized view log"
*Cause:    The fast refresh cannot be performed because the master table does not contain a materialized view log.

Solution: Use the sample below to create the Materialized View Log -
CREATE MATERIALIZED VIEW LOG ON C_B_CUST
   PCTFREE 5
   TABLESPACE CMX_TEMP
   STORAGE (INITIAL 5K NEXT 5K);



SQL Error: ORA-01031: insufficient privileges

Explanation: ORA-01031 - insufficient privileges is the generic message which normally occurs if required privileges are not available for given operation. In the case of Materialized view, if user does not have "CREATE MATERIALIZED VIEW" privileges then 'ORA-01031: insufficient privileges'  error message will be reported.

Error Message:
SQL Error: ORA-01031: insufficient privileges 01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password 
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.

Solution: In order to get required privileges we need to reach out Oracle DBA. In the current case, we need to ask DBA to provide "CREATE MATERIALIZED VIEW" privileges to given user. Assume that you are using TEST_USER to create Materialized view then ask DBA to provide privileges below -
GRANT CREATE MATERIALIZED VIEW TO TEST_USER

The video below provides a detailed explanation and prerequisites for Material views : 




No comments:

Post a Comment

Please do not enter any spam link in the comment box.

Dynatrace : An Overview

  Dynatrace, a leading provider of software intelligence, offers a powerful platform designed to monitor, analyze, and optimize the performa...