Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ORA-30354: Query rewrite not allowed on SYS relations

Posted on 2014-04-07
14
889 Views
Last Modified: 2014-04-07
Hello Experts,

I am getting the below error while creating the :


CREATE MATERIALIZED VIEW MS_SRA_CH_HIS_DTL_GBI_MV
REFRESH FAST ON COMMIT 
ENABLE QUERY REWRITE AS
  select CHD_DETAIL_ID,
  SOURCE_ID,
    SOURCE_NAME,
    FIELD_NAME,
    BEFORE_VALUE,
    AFTER_VALUE,
    CHD_LAST_MODIFIED_ON,
    CHD_LAST_MODIFIED_BY
  from
    (SELECT DISTINCT CH_DETAIL_ID AS CHD_DETAIL_ID, H.OBJECT_ID AS SOURCE_ID,
      D.SOURCE_NAME,
      SUBSTR(( SUBSTR(D.FIELD_NAME,INSTR(D.FIELD_NAME,'#$#')+3)
      ||DECODE(L.TABLE_NAME, 'MS_SRA_VISIT_ACTIONS_AUD',' ['
      ||H.OBJECT_ID
      ||' - '
      ||H.SOURCE_ID
      ||']', 'MS_SRA_VISIT_SETUP',' ['
      ||H.OBJECT_ID
      ||']', 'MS_SRA_VISIT_SETUP_TPA',' ['
      ||H.OBJECT_ID
      ||']', 'MS_SRA_FACILITY_INFO',' ['
      ||H.OBJECT_ID
      ||']', 'MS_SRA_PROTOCOL_SETUP',
      (SELECT ' ['
        ||PROTOCOL_NAME
        ||']'
      FROM MS_SRA_PROTOCOL_SETUP
      WHERE PROTOCOL_SETUP_ID=H.OBJECT_ID
      ),'MS_SRA_PROTOCOL_SETUP_FLV',
      (SELECT ' ['
        ||PROTOCOL_NAME
        ||']'
      FROM MS_SRA_PROTOCOL_SETUP
      WHERE PROTOCOL_SETUP_ID=H.OBJECT_ID
      ),'MS_SRA_QUESTION_SETUP',' ['
      ||H.OBJECT_ID
      ||']','MS_SRA_VISIT_SETUP_ASN',
      (SELECT ' ['
        ||H.OBJECT_ID
        ||' - '
        ||MS_APPS_UTILITIES.GET_USER_FULL_NAME(TO_NUMBER(H.SOURCE_ID))
        ||']'
      FROM DUAL
      ) ,DECODE(FORM_ID_COLUMN,'QUESTION_ID',DECODE(QST_TYPE,1,' ['
      ||H.OBJECT_ID
      ||' - '
      ||MS_APPS_UTILITIES.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
      ||' - '
      ||QST_CODE
      ||']',2,' ['
      ||H.OBJECT_ID
      ||' - '
      ||MS_APPS_UTILITIES.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
      ||' - Facility Profile - '
      ||QST_QUESTION
      ||']',3,' ['
      ||H.OBJECT_ID
      ||' - '
      ||MS_APPS_UTILITIES.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
      ||' - Facility Profile General - '
      ||QST_QUESTION
      ||']',NULL),'FINDING_ID',' ['
      ||F.FINDING_ID
      ||' - '
      ||F.FND_NAME
      ||']',NULL))),1,200) AS FIELD_NAME,
      D.BEFORE_VALUE,
      D.AFTER_VALUE,
      D.CHD_LAST_MODIFIED_ON,
      D.CHD_LAST_MODIFIED_BY
    FROM MS_SRA_CHANGE_HIS_DETAIL_RPT D,
      MS_SRA_CHANGE_HIS_HEADER_RPT H,
      (SELECT VISIT_ID,
        QUESTION_ID,
        QST_CODE,
        QST_QUESTION,
        QST_SHORT_TEXT,
        QST_TYPE
      FROM MS_SRA_VISIT_QUESTIONS
      WHERE QST_SOURCE_OBJECT_TYPE = 'MS_SRA_VISIT'
      ) Q,
      MS_SRA_VISIT_FINDINGS F,
      MS_SRA_QUESTION_SETUP_KEY K,
      MS_SRA_CHANGE_HIS_COL_LOOKUP L
    WHERE H.SOURCE_NAME                                    = D.SOURCE_NAME
    and H.SOURCE_ID                                        = D.SOURCE_ID
    AND (((H.OBJECT_ID                                      = SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1) OR H.SOURCE_ID                                      = SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1))
    and H.SOURCE_NAME not                                                                                  in ('Question Setup','Protocol Setup'))
    OR H.OBJECT_ID                                         = D.SOURCE_ID)
    AND H.SOURCE_ID                                        = F.FINDING_ID(+)
    AND H.SOURCE_ID                                        = TO_CHAR(Q.QUESTION_ID(+))
    AND H.OBJECT_ID                                        = Q.VISIT_ID(+)
    AND H.OBJECT_ID                                        = F.VISIT_ID(+)
    AND D.SOURCE_ID                                        = TO_CHAR(K.QUESTION_ID(+))
    AND SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1) = ROWIDTONCHAR(K.rowid(+))
    AND INSTR(D.FIELD_NAME,L.COLUMN_DISPLAY_NAME)        >0
    AND D.SOURCE_NAME                                      = L.FORM_NAME
    AND ((D.FIELD_NAME NOT LIKE '%Visit ID%'
    AND D.SOURCE_NAME != 'MS_SRA_VISIT_SETUP')
    OR (D.SOURCE_NAME  ='MS_SRA_VISIT_SETUP'
    AND D.FIELD_NAME  != 'VISIT ID'))
    union all
    SELECT DISTINCT DET.CH_DETAIL_ID AS CHD_DETAIL_ID, HED.OBJECT_ID AS SOURCE_ID,
      DET.SOURCE_NAME,
      DET.FIELD_NAME,
      DET.BEFORE_VALUE,
      DET.AFTER_VALUE,
      DET.CHD_LAST_MODIFIED_ON,
      DET.CHD_LAST_MODIFIED_BY
    FROM MS_SRA_CHANGE_HIS_DETAIL_RPT DET,
      MS_SRA_CHANGE_HIS_HEADER_RPT HED
    WHERE HED.SOURCE_ID = DET.SOURCE_ID
    AND HED.SOURCE_NAME = DET.SOURCE_NAME
    and HED.SOURCE_NAME = 'MS_SRA_FACILITY_PROFILE'
    );

Open in new window


Error at Command Line:79 Column:10
Error report:
SQL Error: ORA-30354: Query rewrite not allowed on SYS relations
30354. 00000 -  "Query rewrite not allowed on SYS relations"
*Cause:    A SYS relation was referenced in the select clause for a
           materialized view with query rewrite enabled.

Open in new window



I tried with altering the session but still the same error:

alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=trusted;

Open in new window

0
Comment
Question by:Swadhin Ray
  • 7
  • 5
  • 2
14 Comments
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 39983047
I don't know the details of this one but the error says that query rewrite is enabled and you are trying to enable it as well. Can you try disabling it instead?

alter session set query_rewrite=false;
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 39983061
Remove DUAL from the query.

Here is what you need to read:

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6002.htm#SQLRF54181
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39983152
Removed dual but seems function cannot be used :

CREATE MATERIALIZED VIEW MS_SRA_CH_HIS_DTL_GBI_MV
REFRESH FAST ON COMMIT 
ENABLE QUERY REWRITE AS
  select CHD_DETAIL_ID,
  SOURCE_ID,
    SOURCE_NAME,
    FIELD_NAME,
    BEFORE_VALUE,
    AFTER_VALUE,
    CHD_LAST_MODIFIED_ON,
    CHD_LAST_MODIFIED_BY
  from
    (SELECT DISTINCT CH_DETAIL_ID AS CHD_DETAIL_ID, H.OBJECT_ID AS SOURCE_ID,
      D.SOURCE_NAME,
      SUBSTR(( SUBSTR(D.FIELD_NAME,INSTR(D.FIELD_NAME,'#$#')+3)
      ||DECODE(L.TABLE_NAME, 'MS_SRA_VISIT_ACTIONS_AUD',' ['
      ||H.OBJECT_ID
      ||' - '
      ||H.SOURCE_ID
      ||']', 'MS_SRA_VISIT_SETUP',' ['
      ||H.OBJECT_ID
      ||']', 'MS_SRA_VISIT_SETUP_TPA',' ['
      ||H.OBJECT_ID
      ||']', 'MS_SRA_FACILITY_INFO',' ['
      ||H.OBJECT_ID
      ||']', 'MS_SRA_PROTOCOL_SETUP',
      (SELECT ' ['
        ||PROTOCOL_NAME
        ||']'
      FROM MS_SRA_PROTOCOL_SETUP
      WHERE PROTOCOL_SETUP_ID=H.OBJECT_ID
      ),'MS_SRA_PROTOCOL_SETUP_FLV',
      (SELECT ' ['
        ||PROTOCOL_NAME
        ||']'
      FROM MS_SRA_PROTOCOL_SETUP
      WHERE PROTOCOL_SETUP_ID=H.OBJECT_ID
      ),'MS_SRA_QUESTION_SETUP',' ['
      ||H.OBJECT_ID
      ||']','MS_SRA_VISIT_SETUP_ASN',
      (--SELECT
      ' ['
        ||H.OBJECT_ID
        ||' - '
        ||MS_APPS_UTILITIES.GET_USER_FULL_NAME(TO_NUMBER(H.SOURCE_ID))
        ||']'
      --FROM DUAL
      ) ,DECODE(FORM_ID_COLUMN,'QUESTION_ID',DECODE(QST_TYPE,1,' ['
      ||H.OBJECT_ID
      ||' - '
      ||MS_APPS_UTILITIES.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
      ||' - '
      ||QST_CODE
      ||']',2,' ['
      ||H.OBJECT_ID
      ||' - '
      ||MS_APPS_UTILITIES.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
      ||' - Facility Profile - '
      ||QST_QUESTION
      ||']',3,' ['
      ||H.OBJECT_ID
      ||' - '
      ||MS_APPS_UTILITIES.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
      ||' - Facility Profile General - '
      ||QST_QUESTION
      ||']',NULL),'FINDING_ID',' ['
      ||F.FINDING_ID
      ||' - '
      ||F.FND_NAME
      ||']',NULL))),1,200) AS FIELD_NAME,
      D.BEFORE_VALUE,
      D.AFTER_VALUE,
      D.CHD_LAST_MODIFIED_ON,
      D.CHD_LAST_MODIFIED_BY
    FROM MS_SRA_CHANGE_HIS_DETAIL_RPT D,
      MS_SRA_CHANGE_HIS_HEADER_RPT H,
      (SELECT VISIT_ID,
        QUESTION_ID,
        QST_CODE,
        QST_QUESTION,
        QST_SHORT_TEXT,
        QST_TYPE
      FROM MS_SRA_VISIT_QUESTIONS
      WHERE QST_SOURCE_OBJECT_TYPE = 'MS_SRA_VISIT'
      ) Q,
      MS_SRA_VISIT_FINDINGS F,
      MS_SRA_QUESTION_SETUP_KEY K,
      MS_SRA_CHANGE_HIS_COL_LOOKUP L
    WHERE H.SOURCE_NAME                                    = D.SOURCE_NAME
    and H.SOURCE_ID                                        = D.SOURCE_ID
    AND (((H.OBJECT_ID                                      = SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1) OR H.SOURCE_ID                                      = SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1))
    and H.SOURCE_NAME not                                                                                  in ('Question Setup','Protocol Setup'))
    OR H.OBJECT_ID                                         = D.SOURCE_ID)
    AND H.SOURCE_ID                                        = F.FINDING_ID(+)
    AND H.SOURCE_ID                                        = TO_CHAR(Q.QUESTION_ID(+))
    AND H.OBJECT_ID                                        = Q.VISIT_ID(+)
    AND H.OBJECT_ID                                        = F.VISIT_ID(+)
    AND D.SOURCE_ID                                        = TO_CHAR(K.QUESTION_ID(+))
    AND SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1) = ROWIDTONCHAR(K.rowid(+))
    AND INSTR(D.FIELD_NAME,L.COLUMN_DISPLAY_NAME)        >0
    AND D.SOURCE_NAME                                      = L.FORM_NAME
    AND ((D.FIELD_NAME NOT LIKE '%Visit ID%'
    AND D.SOURCE_NAME != 'MS_SRA_VISIT_SETUP')
    OR (D.SOURCE_NAME  ='MS_SRA_VISIT_SETUP'
    AND D.FIELD_NAME  != 'VISIT ID'))
    union all
    SELECT DISTINCT DET.CH_DETAIL_ID AS CHD_DETAIL_ID, HED.OBJECT_ID AS SOURCE_ID,
      DET.SOURCE_NAME,
      DET.FIELD_NAME,
      DET.BEFORE_VALUE,
      DET.AFTER_VALUE,
      DET.CHD_LAST_MODIFIED_ON,
      DET.CHD_LAST_MODIFIED_BY
    FROM MS_SRA_CHANGE_HIS_DETAIL_RPT DET,
      MS_SRA_CHANGE_HIS_HEADER_RPT HED
    WHERE HED.SOURCE_ID = DET.SOURCE_ID
    AND HED.SOURCE_NAME = DET.SOURCE_NAME
    AND HED.SOURCE_NAME = 'MS_SRA_FACILITY_PROFILE'
    );

Open in new window



Error at Command Line:69 Column:27
Error report:
SQL Error: ORA-30357: this PL/SQL function cannot be supported for query rewrite
30357. 00000 -  "this PL/SQL function cannot be supported for query rewrite"
*Cause:    The statement referenced a PL/SQL function that is not marked
           DETERMINISTIC.
*Action:   Perform one of the following actions
           - Remove the use of the PL/SQL function.
           - Mark the PL/SQL function as DETERMINISTIC.
           - Disable the REWRITE option on the materialized view.
           The function should be marked DETERMINISTIC only if it always
           returns the same result value for any given set of input argument
           values, regardless of any database state or session state.
           Do not mark the function as DETERMINISTIC if it has any
           meaningful side-effects.

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 39983211
Remove the query rewrite from line #3?
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 39983226
Recompile your user-defined function "MS_APPS_UTILITIES.GET_USER_FULL_NAME" as DETERMINISTIC.

Read more here:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/function.htm#LNPLS1580
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39983471
Changed my functions to deterministic but still inline view having a problem to create :

CREATE MATERIALIZED VIEW MS_SRA_CH_HIS_DTL_GBI_MV
REFRESH FAST ON COMMIT 
ENABLE QUERY REWRITE AS
select CHD_DETAIL_ID,
  SOURCE_ID,
    SOURCE_NAME,
    FIELD_NAME,
    BEFORE_VALUE,
    AFTER_VALUE,
    CHD_LAST_MODIFIED_ON,
    CHD_LAST_MODIFIED_BY
  from
    (SELECT DISTINCT CH_DETAIL_ID AS CHD_DETAIL_ID, H.OBJECT_ID AS SOURCE_ID,
      D.SOURCE_NAME,
      SUBSTR(( SUBSTR(D.FIELD_NAME,INSTR(D.FIELD_NAME,'#$#')+3)
      ||DECODE(L.TABLE_NAME, 'MS_SRA_VISIT_ACTIONS_AUD',' ['
      ||H.OBJECT_ID
      ||' - '
      ||H.SOURCE_ID
      ||']', 'MS_SRA_VISIT_SETUP',' ['
      ||H.OBJECT_ID
      ||']', 'MS_SRA_VISIT_SETUP_TPA',' ['
      ||H.OBJECT_ID
      ||']', 'MS_SRA_FACILITY_INFO',' ['
      ||H.OBJECT_ID
      ||']', 'MS_SRA_PROTOCOL_SETUP',
      (SELECT ' ['
        ||PROTOCOL_NAME
        ||']'
      FROM MS_SRA_PROTOCOL_SETUP
      WHERE PROTOCOL_SETUP_ID=H.OBJECT_ID
      ),'MS_SRA_PROTOCOL_SETUP_FLV',
      (SELECT ' ['
        ||PROTOCOL_NAME
        ||']'
      FROM MS_SRA_PROTOCOL_SETUP
      WHERE PROTOCOL_SETUP_ID=H.OBJECT_ID
      ),'MS_SRA_QUESTION_SETUP',' ['
      ||H.OBJECT_ID
      ||']','MS_SRA_VISIT_SETUP_ASN',
      (--SELECT
      ' ['
        ||H.OBJECT_ID
        ||' - '
        ||MS_APPS_UTILITIES_NEW.GET_USER_FULL_NAME(TO_NUMBER(H.SOURCE_ID))
        ||']'
      --FROM DUAL
      ) ,DECODE(FORM_ID_COLUMN,'QUESTION_ID',DECODE(QST_TYPE,1,' ['
      ||H.OBJECT_ID
      ||' - '
      ||MS_APPS_UTILITIES_NEW.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
      ||' - '
      ||QST_CODE
      ||']',2,' ['
      ||H.OBJECT_ID
      ||' - '
      ||MS_APPS_UTILITIES_NEW.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
      ||' - Facility Profile - '
      ||QST_QUESTION
      ||']',3,' ['
      ||H.OBJECT_ID
      ||' - '
      ||MS_APPS_UTILITIES_NEW.GET_USER_FULL_NAME(D.CHD_LAST_MODIFIED_BY)
      ||' - Facility Profile General - '
      ||QST_QUESTION
      ||']',NULL),'FINDING_ID',' ['
      ||F.FINDING_ID
      ||' - '
      ||F.FND_NAME
      ||']',NULL))),1,200) AS FIELD_NAME,
      D.BEFORE_VALUE,
      D.AFTER_VALUE,
      D.CHD_LAST_MODIFIED_ON,
      D.CHD_LAST_MODIFIED_BY
    FROM MS_SRA_CHANGE_HIS_DETAIL_RPT D,
      MS_SRA_CHANGE_HIS_HEADER_RPT H,
      (SELECT VISIT_ID,
        QUESTION_ID,
        QST_CODE,
        QST_QUESTION,
        QST_SHORT_TEXT,
        QST_TYPE
      FROM MS_SRA_VISIT_QUESTIONS
      WHERE QST_SOURCE_OBJECT_TYPE = 'MS_SRA_VISIT'
      ) Q,
      MS_SRA_VISIT_FINDINGS F,
      MS_SRA_QUESTION_SETUP_KEY K,
      MS_SRA_CHANGE_HIS_COL_LOOKUP L
    WHERE H.SOURCE_NAME                                    = D.SOURCE_NAME
    and H.SOURCE_ID                                        = D.SOURCE_ID
    AND (((H.OBJECT_ID                                      = SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1) OR H.SOURCE_ID                                      = SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1))
    and H.SOURCE_NAME not                                                                                  in ('Question Setup','Protocol Setup'))
    OR H.OBJECT_ID                                         = D.SOURCE_ID)
    AND H.SOURCE_ID                                        = F.FINDING_ID(+)
    AND H.SOURCE_ID                                        = TO_CHAR(Q.QUESTION_ID(+))
    AND H.OBJECT_ID                                        = Q.VISIT_ID(+)
    AND H.OBJECT_ID                                        = F.VISIT_ID(+)
    AND D.SOURCE_ID                                        = TO_CHAR(K.QUESTION_ID(+))
    AND SUBSTR(D.FIELD_NAME,1,INSTR(D.FIELD_NAME,'#$#')-1) = ROWIDTONCHAR(K.rowid(+))
    AND INSTR(D.FIELD_NAME,L.COLUMN_DISPLAY_NAME)        >0
    AND D.SOURCE_NAME                                      = L.FORM_NAME
    AND ((D.FIELD_NAME NOT LIKE '%Visit ID%'
    AND D.SOURCE_NAME != 'MS_SRA_VISIT_SETUP')
    OR (D.SOURCE_NAME  ='MS_SRA_VISIT_SETUP'
    AND D.FIELD_NAME  != 'VISIT ID'))
    union all
    SELECT DISTINCT DET.CH_DETAIL_ID AS CHD_DETAIL_ID, HED.OBJECT_ID AS SOURCE_ID,
      DET.SOURCE_NAME,
      DET.FIELD_NAME,
      DET.BEFORE_VALUE,
      DET.AFTER_VALUE,
      DET.CHD_LAST_MODIFIED_ON,
      DET.CHD_LAST_MODIFIED_BY
    FROM MS_SRA_CHANGE_HIS_DETAIL_RPT DET,
      MS_SRA_CHANGE_HIS_HEADER_RPT HED
    WHERE HED.SOURCE_ID = DET.SOURCE_ID
    AND HED.SOURCE_NAME = DET.SOURCE_NAME
    AND HED.SOURCE_NAME = 'MS_SRA_FACILITY_PROFILE'
    );

Open in new window



Error at Command Line:84 Column:38
Error report:
SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 -  "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause:    The materialized view did not satisfy conditions for refresh at
           commit time.
*Action:   Specify only valid options.

Open in new window

0
 
LVL 23

Expert Comment

by:paquicuba
ID: 39983516
Have you created the MV log ?
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39983565
nope ... on which table I should create it ?

i created it on :

CREATE MATERIALIZED VIEW LOG ON MS_SRA_VISIT_QUESTIONS;

after that when I try to create it says:

Error at Command Line:107 Column:38
Error report:
SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 -  "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause:    The materialized view did not satisfy conditions for refresh at
           commit time.
*Action:   Specify only valid options.

Open in new window

0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39983606
I created the new log like :

CREATE MATERIALIZED VIEW LOG ON MS_SRA_VISIT_QUESTIONS WITH ROWID, SEQUENCE
(VISIT_ID,
        QUESTION_ID,
        QST_CODE,
        QST_QUESTION,
        QST_SHORT_TEXT,
        QST_TYPE,QST_SOURCE_OBJECT_TYPE)
INCLUDING NEW VALUES;

and then executed the MV but still I get the below error:

Error at Command Line:113 Column:38
Error report:
SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 -  "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause:    The materialized view did not satisfy conditions for refresh at
           commit time.
*Action:   Specify only valid options.

Open in new window

0
 
LVL 23

Expert Comment

by:paquicuba
ID: 39983637
Sorry, I just saw your problems with fast refresh:

the "union all" and the "distinct keyword" don't allow fast refresh -you have a complex materialized view.

In case, you eliminate both restrictions, you have to make sure that the full primary key for your master table is part of the SELECT statement.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 39983651
Try eliminating the DISTINCT keywords and change "Union All" to "Union" -The query may still be too complex for fast refresh, but let's try...
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 500 total points
ID: 39983672
FYI. If you're going to use ROWID, then you have to expose the ROWID in the SELECT statement:

SELECT
        ROWID ROWID_Q
        VISIT_ID,
        QUESTION_ID,
        QST_CODE,
        QST_QUESTION,
        QST_SHORT_TEXT,
        QST_TYPE
      FROM MS_SRA_VISIT_QUESTIONS
      WHERE QST_SOURCE_OBJECT_TYPE = 'MS_SRA_VISIT'
0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 39984511
Thanks..
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 39984904
Anytime!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
passing parameters to sql script oracle 4 60
help on oracle query 5 53
pl/sql - query very slow 26 71
Oracle 12c Default Isolation Level 17 41
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question