Create FAST Materialised View in Oracle 10g with ROWID

materialized-vieworacleoracle-10goracle-11goracle-11g-r2

I want to create FAST refresh the below Materialized view through rowID method.

`CREATE MATERIALIZED VIEW XXARG_ACC_ANALYSIS
  REFRESH FAST ON DEMAND
  AS SELECT   gjb.name "Batch_Name",
         gjs.user_je_source_name "Source",
         gjc.user_je_category_name "Category",
         gjh.default_effective_date "GL_Date",
         gjh.je_header_id,
         gjl.je_line_num "Line_Number",
         gcc.segment1,
         gcc.segment4,
         gcc.segment3,
         gjs.je_source_name,
         gjc.je_category_name,
            gcc.segment1
         || '.'
         || gcc.segment2
         || '.'
         || gcc.segment3
         || '.'
         || gcc.segment4
         || '.'
         || gcc.segment5
         || '.'
         || gcc.segment6
         || '.'
         || gcc.segment7
         || '.'
         || gcc.segment8
            "Account_Code", gjl.accounted_dr "Dr_Amount",gjl.accounted_cr "Cr_Amount",
         gjl.description "Line_Description",
         gjh.description "Journal_Description",
         flv.meaning "Batch_Status",
         fft1.description "Branch",
         fft2.description "LOB",
         fft3.description "GL_Account",
         GLGJB.rowid "A", GLGJH.rowid "B", GLGJL.rowid "C", GLGJC.rowid "D", GLGJS.rowid "E", GLGCC.rowid "F",
         AFLV.rowid "G", AFIFS.rowid "H", AFIFSE.rowid "I", AFFV.rowid "J", AFFVT.rowid "K"
  FROM   APPS.gl_je_batches gjb,
         APPS.gl_je_headers gjh,
         APPS.gl_je_lines gjl,
         APPS.GL_JE_CATEGORIES_TL gjc,
         APPS.GL_JE_SOURCES_TL gjs,
         APPS.gl_code_combinations gcc,
         APPS.FND_LOOKUP_VALUES flv,
         APPS.fnd_id_flex_structures ffs1,
         APPS.fnd_id_flex_segments fif1,
         APPS.fnd_flex_values ffv1,
         APPS.fnd_flex_values_tl fft1,
         APPS.fnd_id_flex_structures ffs2,
         APPS.fnd_id_flex_segments fif2,
         APPS.fnd_flex_values ffv2,
         APPS.fnd_flex_values_tl fft2,
         APPS.fnd_id_flex_structures ffs3,
         APPS.fnd_id_flex_segments fif3,
         APPS.fnd_flex_values ffv3,
         APPS.fnd_flex_values_tl fft3,
         GL.GL_JE_BATCHES GLGJB,
         GL.GL_JE_HEADERS GLGJH,
         GL.GL_JE_LINES GLGJL,
         GL.GL_JE_CATEGORIES_TL GLGJC,
         GL.GL_JE_SOURCES_TL GLGJS,
         GL.GL_CODE_COMBINATIONS GLGCC,
         APPLSYS.FND_LOOKUP_VALUES AFLV,
         APPLSYS.FND_ID_FLEX_STRUCTURES AFIFS,
         APPLSYS.FND_ID_FLEX_SEGMENTS AFIFSE,
         APPLSYS.FND_FLEX_VALUES AFFV,
         APPLSYS.FND_FLEX_VALUES_TL AFFVT
 WHERE       gjh.je_batch_id = gjb.je_batch_id(+)
         AND gjh.je_header_id = gjl.je_header_id(+)
         AND gjh.je_category = gjc.je_category_name
         AND gjh.je_source = gjs.je_source_name
         AND gjh.default_effective_date > '31-MAR-16'
         AND gjl.code_combination_id = gcc.code_combination_id
         AND gjb.status = flv.lookup_code
         AND flv.lookup_type = 'BATCH_STATUS'
         AND flv.VIEW_APPLICATION_ID = 101
         AND flv.lookup_code = 'P'
         AND ffs1.id_flex_code = 'GL#'
         AND ffs1.id_flex_structure_code = 'ARG_COA'
         AND fif1.id_flex_num = ffs1.id_flex_num
         AND fif1.id_flex_code = 'GL#'
         AND fif1.application_column_name = 'SEGMENT1'
         AND ffv1.flex_value = gcc.segment1
         AND ffv1.flex_value_id = fft1.flex_value_id
         AND fif1.flex_value_set_id = ffv1.flex_value_set_id
         AND ffs2.id_flex_code = 'GL#'
         AND ffs2.id_flex_structure_code = 'ARG_COA'
         AND fif2.id_flex_num = ffs2.id_flex_num
         AND fif2.id_flex_code = 'GL#'
         AND fif2.application_column_name = 'SEGMENT4'
         AND fif2.flex_value_set_id = ffv2.flex_value_set_id
         AND ffv2.flex_value = gcc.segment4
         AND ffv2.flex_value_id = fft2.flex_value_id
         AND ffs3.id_flex_code = 'GL#'
         AND ffs3.id_flex_structure_code = 'ARG_COA'
         AND fif3.id_flex_num = ffs3.id_flex_num
         AND fif3.id_flex_code = 'GL#'
         AND fif3.application_column_name = 'SEGMENT3'
         AND fif3.flex_value_set_id = ffv3.flex_value_set_id
         AND ffv3.flex_value = gcc.segment3
         AND ffv3.flex_value_id = fft3.flex_value_id;`

NOTE: I have created MLOGS for the base tables GL.GL_JE_BATCHES, GL.GL_JE_HEADERS, GL.GL_JE_LINES, GL.GL_JE_CATEGORIES_TL, GL.GL_JE_SOURCES_TL, GL.GL_CODE_COMBINATIONS, APPLSYS.FND_LOOKUP_VALUES, APPLSYS.FND_ID_FLEX_STRUCTURES, APPLSYS.FND_ID_FLEX_SEGMENTS, APPLSYS.FND_FLEX_VALUES,APPLSYS.FND_FLEX_VALUES_TL.

We have created synonyms from the above base tables to APPS schema. APPS.gl_je_batches, APPS.gl_je_headers, APPS.gl_je_lines, APPS.GL_JE_CATEGORIES_TL, APPS.GL_JE_SOURCES_TL, APPS.gl_code_combinations, APPS.FND_LOOKUP_VALUES, APPS.fnd_id_flex_structures, APPS.fnd_id_flex_segments, APPS.fnd_flex_values, APPS.fnd_flex_values_tl, APPS.fnd_id_flex_structures, APPS.fnd_id_flex_segments, APPS.fnd_flex_values, APPS.fnd_flex_values_tl, APPS.fnd_id_flex_structures, APPS.fnd_id_flex_segments, APPS.fnd_flex_values, APPS.fnd_flex_values_tl.

But I'm getting the below error

AND flv.VIEW_APPLICATION_ID = 101
*
ERROR at line 76:
ORA-12052: cannot fast refresh materialized view APPS.XXARG_ACC_ANALYSIS

Or if I try to do Complete refresh, it displays me the below error

AND flv.VIEW_APPLICATION_ID = 101
*
ERROR at line 76:
ORA-01114: IO error writing block to file 201 (block # 4154889)
ORA-29701: unable to connect to Cluster Manager
ORA-01114: IO error writing block to file 201 (block # 4154889)
ORA-29701: unable to connect to Cluster Manager

Please help me on this.

Best Answer

Ok, so 2 problems here:

  1. ORA-29701 this may be caused by a bug, permission issue with the executables or other environmental errors, such as deleting socket files from the tmp directory. First investigate this.

  2. ORA-12052: cannot fast refresh materialized view. For this:

Diagnosing ORA-12004 Refresh Fast Cannot be Used (Doc ID 179469.1)

1.8 Materialized View References Synonyms or Views

Fast refreshable materialized views can be created based on master tables and master materialized views only. Materialized views based on a synonym or a view must be complete refreshed. This actually is a consequence of the cause discussed in Section 1.3. From 8.1.6 onwards, use of a synonym or a view in place of a direct reference to a master table renders the materialized view complex, and thus it cannot be fast refreshed. This can be seen if a direct attempt is made to create the materialized view as fast refreshable when it references synonyms or views.

But instead of manually searching for reasons why fast refresh is not possible, let the database do the work:

Run $ORACLE_HOME/rdbms/admin/utlxmv.sql to create the MV_CAPABILITIES_TABLE.

Use DBMS_MVIEW.EXPLAIN_MVIEW to explain the mview:

begin
  DBMS_MVIEW.EXPLAIN_MVIEW ('create materialized view ....');
end;
/

After that you can find the reason why fast refresh is not possible in MV_CAPABILITIES_TABLE.