Replace complex Materialized Views with table
Materialized views, also known as snapshots in the earlier versions of Oracle E-Business Suite, are replicas of a target master from a single point in time. This means, that material views are used to replicate data to other non-master sites in a replication environment and to cache expensive queries in a data warehouse environment.
In this blog we will review the procedure to be followed when materialized views have become too complex to be handled in a standard way during patching and especially cut-over.
ORA-22818: subquery expressions not allowed here
22818. 00000 - "subquery expressions not allowed here"
*Cause: An attempt was made to use a subquery expression where these
are not supported.
*Action: Rewrite the statement without the subquery expression.
Error report -
ORA-12008: error in materialized view refresh path
ORA-30372: fine grain access policy conflicts with materialized view
ORA-24256: EXPAND_SQL_TEXT failed with ORA-00933: SQL command not properly ended
Sometimes, Case 3 error can be omitted by connecting to another session and re-execute mview generate procedure. So please try this first.
In all cases, you should either consider rewriting your code, or/and replacing materialized view with table that will be refreshed with concurrent program EAM – Refresh Materialized View.
XXBI_PO_DISTRIBUTIONS_MV was a materialized view on 12.1.3 and you cannot recreate it in a standard way.
Follow the procedure
XXBI_PO_DISTRIBUTIONS
Same column definitions as XXBI_PO_DISTRIBUTIONS_MV.
CREATE TABLE XXBI.XXBI_PO_DISTRIBUTIONS
(
PO_HEADER_ID NUMBER
, PO_LINE_ID NUMBER
, LINE_LOCATION_ID NUMBER
, PO_DISTRIBUTION_ID NUMBER
, REQ_DISTRIBUTION_ID NUMBER
…
CREATE UNIQUE INDEX XXBI.XXBI_PO_DISTRIBUTIONS_U1 ON XXBI.XXBI_PO_DISTRIBUTIONS(po_distribution_id) TABLESPACE APPS_TS_TX_IDX;
CREATE INDEX XXBI.XXBI_PO_DISTRIBUTIONS_N1 ON XXBI.XXBI_PO_DISTRIBUTIONS(po_header_id) TABLESPACE APPS_TS_TX_IDX;
CREATE INDEX XXBI.XXBI_PO_DISTRIBUTIONS_N2 ON XXBI.XXBI_PO_DISTRIBUTIONS(item_id) TABLESPACE APPS_TS_TX_IDX;
CREATE INDEX XXBI.XXBI_PO_DISTRIBUTIONS_N3 ON XXBI.XXBI_PO_DISTRIBUTIONS(budget_code_combination_id) TABLESPACE APPS_TS_TX_IDX;
CREATE INDEX XXBI.XXBI_PO_DISTRIBUTIONS_N4 ON XXBI.XXBI_PO_DISTRIBUTIONS(req_distribution_id) TABLESPACE APPS_TS_TX_IDX;
EXIT;
exec ad_zd_table.upgrade('XXBI', 'XXBI_PO_DISTRIBUTIONS'); Connect as xx__ user:
Example:
grant all on XXBI_PO_DISTRIBUTIONS to apps with grant option;
grant all on XXBI_PO_DISTRIBUTIONS# to apps with grant option;
XXBI_PO_DISTRIBUTIONS_SV
This view should include original query from former materialized view XXBI_PO_DISTRIBUTIONS_MV.
XXBI_PO_DISTRIBUTIONS_MV
select * from XXBI_PO_DISTRIBUTIONS
Application Developer > Applications > Lookups > Application Object Library
Code: <table name> e.g. XXBI_PO_DISTRIBUTIONS
Meaning: <table name> e.g. XXBI_PO_DISTRIBUTIONS
Tag: <sql retrieving data from source view> e.g.,
select * from XXBI_PO_DISTRIBUTIONS_SV
System Administrator > Requests > AD - Refresh Table or Materialized View
Enter table name as a parameter.
perl $FND_TOP/bin/xdfgen.pl apps/[appspassword] XXBI_PO_DISTRIBUTIONS xdf_filename=path/XXBI_PO_DISTRIBUTIONS
perl $FND_TOP/bin/xdfgen.pl apps/[appspassword] XXBI_PO_DISTRIBUTIONS_SV xdf_filename=path/XXBI_PO_DISTRIBUTIONS_SV
perl $FND_TOP/bin/xdfgen.pl apps/[appspassword] XXBI_PO_DISTRIBUTIONS_MV xdf_filename=path/XXBI_PO_DISTRIBUTIONS_MV
Table XXBI_PO_DISTRIBUTIONS:
Directory: admin/xdf
File Name: XXBI_PO_DISTRIBUTIONS.xdf
Object Type: xdf_tab
Description: table
DB Object Name:
View XXBI_PO_DISTRIBUTIONS_SV:
Directory: admin/xdf
File Name: XXBI_PO_DISTRIBUTIONS_SV.xdf
Object Type: xdf_view
Description: table
DB Object Name:
View XXBI_PO_DISTRIBUTIONS_MV:
Directory: admin/xdf
File Name: XXBI_PO_DISTRIBUTIONS_MV.xdf
Object Type: xdf_view
Description: table
DB Object Name:
Did you find our solution interesting?
You’re in luck as we are hiring!
We would love to get to know you better!
Tehnološki park 24, 1000 Ljubljana, Slovenia
Folnegovićeva 6C, HR-10000 Zagreb, Croatia
info@osir-erpis.eu
