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:
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.ORA-12052: cannot fast refresh materialized view
. For this:Diagnosing ORA-12004 Refresh Fast Cannot be Used (Doc ID 179469.1)
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 theMV_CAPABILITIES_TABLE
.Use DBMS_MVIEW.EXPLAIN_MVIEW to explain the mview:
After that you can find the reason why fast refresh is not possible in
MV_CAPABILITIES_TABLE
.