ORA-30372: fine grain access policy conflicts with materialized view ISSUE :


Receiving the following error when trying to create the materialize view

oracle@bakuexa1dbadm01:~$ echo $ORACLE_SID
MISUAT1
oracle@bakuexa1dbadm01:~$ sqlplus "/ as sysdba";

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 6 09:55:05 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> conn MIS_MAT/MIS_MAT
Connected.
SQL> 
SQL> 
SQL> CREATE MATERIALIZED VIEW MIS_MAT.TXN_CREDIT_MV 
 2 TABLESPACE USERS
 3 PCTUSED 0
 4 PCTFREE 10
 5 INITRANS 2
 6 MAXTRANS 255
 7 STORAGE (
 8 INITIAL 64K
 9 NEXT 1M
 10 MINEXTENTS 1
 11 MAXEXTENTS UNLIMITED
 12 PCTINCREASE 0
 13 BUFFER_POOL DEFAULT
 14 )
 15 NOCACHE
 16 LOGGING
 17 NOCOMPRESS
 18 NOPARALLEL
 19 BUILD IMMEDIATE
 20 USING INDEX
 21 TABLESPACE USERS
 22 PCTFREE 10
 23 INITRANS 2
 24 MAXTRANS 255
 25 STORAGE (
 26 INITIAL 64K
 27 NEXT 1M
 28 MINEXTENTS 1
 29 MAXEXTENTS UNLIMITED
 30 PCTINCREASE 0
 31 BUFFER_POOL DEFAULT
 32 )
 33 REFRESH FORCE ON DEMAND
 34 WITH ROWID
 35 AS 
 36 SELECT 
 37 GUID ,
 38 STATUS ,
 39 LASTUPDATED ,
 40 IRC ,
 41 SYS_MTI ,
 42 SYS_MTI_ORG ,
 43 TRM_MTI ,
 44 TRM_MTI_ORG ,
 45 BATCH_NO ,
 46 TXN_SOURCE ,
 47 TXN_TRM ,
 48 TXN_TRM_UPD ,
 49 TXN_ENTRY ,
 50 OTC ,
 51 OTS ,
 52 OTE ,
 53 TXN_INSTALL_TYPE ,
 54 INSTALL_CNT ,
 55 F2 ,
 56 F3_TRM ,
 57 F3_SYS ,
 58 F4 ,
 59 F4_ORG ,
 60 F6 ,
 61 F6_ORG ,
 62 LOCAL_TXN_AMNT ,
 63 LOCAL_CRR_CODE ,
 64 LYL_CAMPAIGN_CODE ,
 65 LYL_BANK_POINT ,
 66 LYL_BANK_CAMP_POINT ,
 67 LYL_MRC_POINT ,
 68 LYL_MRC_CAMP_POINT ,
 69 LYL_MRC_CHAIN_POINT ,
 70 LYL_MRC_CHAIN_CAMP_POINT ,
 71 F7 ,
 72 F7_ORG ,
 73 F11_TRM ,
 74 F11_SYS ,
 75 F11_SYS_ORG ,
 76 F12 ,
 77 F13 ,
 78 F14 ,
 79 F18 ,
 80 F22 ,
 81 F23 ,
 82 F24 ,
 83 F25 ,
 84 F32 ,
 85 F33 ,
 86 F37 ,
 87 F37_ORG ,
 88 F38 ,
 89 F39 ,
 90 F41 ,
 91 F42 ,
 92 F43 ,
 93 F43_NAME ,
 94 F43_CITY ,
 95 F43_STATE ,
 96 F43_COUNTRY ,
 97 F47 ,
 98 F49 ,
 99 F51 ,
100 F54 ,
101 F55_T82 ,
102 F55_T84 ,
103 F55_T95 ,
104 F55_T9A ,
105 F55_T9C ,
106 F55_T5F2A ,
107 F55_T5F34 ,
108 F55_T9F02 ,
109 F55_T9F03 ,
110 F55_T9F09 ,
111 F55_T9F10 ,
112 F55_T9F1A ,
113 F55_T9F1E ,
114 F55_T9F26 ,
115 F55_T9F27 ,
116 F55_T9F33 ,
117 F55_T9F34 ,
118 F55_T9F35 ,
119 F55_T9F36 ,
120 F55_T9F37 ,
121 F55_T9F41 ,
122 F55_T9F53 ,
123 F55_T9F5B ,
124 F55_TCVN ,
125 F55_TDKI ,
126 F55_T71 ,
127 F55_T72 ,
128 F55_T91 ,
129 F55_TC0 ,
130 F55_LEN ,
131 CVM_TYPE ,
132 CRD_SRC ,
133 CRD_CLS ,
134 CRD_BRD ,
135 CRD_DCI ,
136 ACCOUNTING_SEGMENT ,
137 ACCOUNT_NO ,
138 MAIN_ACCOUNT_NO ,
139 TXN_SETTLE ,
140 TXN_SETTLE_DATE ,
141 TXN_STT ,
142 TXN_STT_FUP ,
143 MRC_DAILY_MOVED_DATE ,
144 MRC_DAILY_MOVED ,
145 CYCLE_MOVED_DATE ,
146 CYCLE_MOVED ,
147 IS_PARTIAL ,
148 IS_DEFERED ,
149 CLEARING_CHANNEL ,
150 CLEARING_NO ,
151 CLEARING_F4 ,
152 CLEARING_F49 ,
153 CLEARING_F6 ,
154 CLEARING_F51 ,
155 CLEARING_COMM_AMOUNT ,
156 COMMISSION_PROFILE_GUID ,
157 COMISSION_AMNT ,
158 TAX_AMOUNT ,
159 TAX_AMOUNT_PNT ,
160 STOPPAGE_TAX_AMOUNT ,
161 DST_GATE_CODE ,
162 FWD_GATE_ADDR ,
163 TPDU ,
164 TPDU_ORG ,
165 PRC_TIME ,
166 SSF_SPND_TIME ,
167 SSF_RSP_CODE ,
168 SSF_DET_CODE ,
169 REQ_DATE ,
170 REQ_TIME ,
171 REQ_MSEC ,
172 RSP_DATE ,
173 RSP_TIME ,
174 RSP_MSEC ,
175 OPERATOR_CODE ,
176 GUID_GATE ,
177 F60 ,
178 F61 ,
179 F90 ,
180 F95 ,
181 IS_RECURRING ,
182 TERM_ENTRY_CAPABILITY ,
183 CARDHOLDER_PRESENCE_IND ,
184 GIFT_CODE ,
185 INSTALLMENT_CODE ,
186 LYL_DEFERED_DAYS ,
187 LYL_ADD_INST_CNT ,
188 F31 ,
189 REIMB_ATTR ,
190 EOD_INSTALL_STAT ,
191 BANKING_INV_NO ,
192 BANKING_INV_DATE ,
193 BANKING_SETTLEMENT_NO ,
194 OFFLINE_PIN_STATUS ,
195 CONFIRM_STAT ,
196 CHAIN_GUID ,
197 PAYMENT_SEQ_NO ,
198 PAN_USED ,
199 APP_CODE ,
200 EOD_INSTALL_DATE ,
201 COMP_8A ,
202 COMP_95 ,
203 COMP_9F10 ,
204 COMP_9F26 ,
205 COMP_9F27 ,
206 COMP_9F36 ,
207 COMP_9B ,
208 COMP_SCR_RESULT ,
209 REV_REASON ,
210 TERM_BRANCH ,
211 BANK_DECL_STAT ,
212 BANK_DECL_DATE ,
213 CARD_FIRST_INSTALL_DATE ,
214 CARD_FIRST_INSTALL_AMNT ,
215 BANK_ACCOUNT_BRANCH ,
216 BANK_ACCOUNT ,
217 MRC_COMM_TEMPLATE_DET_GUID ,
218 MRC_PRODUCT_TYPE ,
219 MRC_TERM_TYPE ,
220 CAVV_IND ,
221 CAVV_RESULT ,
222 CAVV_DATA ,
223 ECI ,
224 SRC_NTW_DRC ,
225 SRC_GATE_CODE ,
226 POINT_TYPE ,
227 GUID_ORG ,
228 INTEREST_RATE ,
229 FRD_TIME ,
230 BONUS_COLL_ACCOUNT_NO ,
231 IS_BONUS_USED ,
232 BANK_REF_NO ,
233 F2_USED ,
234 FROM_BANK_ACCOUNT ,
235 FROM_BRANCH ,
236 TO_BANK_ACCOUNT ,
237 TO_BRANCH ,
238 BANK_ACCOUNTING_SERVICE_CODE ,
239 BANK_ACCOUNTING_CONTRACT_NO ,
240 F22_PCC ,
241 OPENED_LIMIT ,
242 CLEARING_F5 ,
243 CLEARING_F50 ,
244 IS_ORG_TXN_REC ,
245 COMM_APPLIED ,
246 COMM_CALCULATED ,
247 COMM_REF_GUID ,
248 SPC_COMM_AMNT ,
249 CLOSED_AMOUNT ,
250 ORDER_NO ,
251 INSTITUTION_CODE ,
252 SUBSCRIBER_NO ,
253 USE_IN_EXPIRE ,
254 POINT_VALID_DATE ,
255 LYL_DISCOUNT
256 FROM
257 OC_TXN.TXN_CREDIT@VIEW_NEW_KK1;
OC_TXN.TXN_CREDIT@VIEW_NEW_KK1
 *
ERROR at line 257:
ORA-30372: fine grain access policy conflicts with materialized view

SOLUTION :
We are able to Create the MV by using the clause USING TRUSTED CONSTRAINTS in the syntax for example as below

SQL> sho user
USER is "MIS_MAT"
SQL> 
SQL> CREATE MATERIALIZED VIEW MIS_MAT.TXN_CREDIT_MV 
 2 TABLESPACE USERS
 3 PCTUSED 0
 4 PCTFREE 10
 5 INITRANS 2
 6 MAXTRANS 255
 7 STORAGE (
 8 INITIAL 64K
 9 NEXT 1M
 10 MINEXTENTS 1
 11 MAXEXTENTS UNLIMITED
 12 PCTINCREASE 0
 13 BUFFER_POOL DEFAULT
 14 )
 15 NOCACHE
 16 LOGGING
 17 NOCOMPRESS
 18 NOPARALLEL
 19 BUILD IMMEDIATE
 20 USING INDEX
 21 TABLESPACE USERS
 22 PCTFREE 10
 23 INITRANS 2
 24 MAXTRANS 255
 25 STORAGE (
 26 INITIAL 64K
 27 NEXT 1M
 28 MINEXTENTS 1
 29 MAXEXTENTS UNLIMITED
 30 PCTINCREASE 0
 31 BUFFER_POOL DEFAULT
 32 )
 33 REFRESH FORCE ON DEMAND
 34 WITH ROWID USING TRUSTED CONSTRAINTS
 35 AS 
 36 SELECT 
 37 GUID ,
 38 STATUS ,
 39 LASTUPDATED ,
 40 IRC ,
 41 SYS_MTI ,
 42 SYS_MTI_ORG ,
 43 TRM_MTI ,
 44 TRM_MTI_ORG ,
 45 BATCH_NO ,
 46 TXN_SOURCE ,
 47 TXN_TRM ,
 48 TXN_TRM_UPD ,
 49 TXN_ENTRY ,
 50 OTC ,
 51 OTS ,
 52 OTE ,
 53 TXN_INSTALL_TYPE ,
 54 INSTALL_CNT ,
 55 F2 ,
 56 F3_TRM ,
 57 F3_SYS ,
 58 F4 ,
 59 F4_ORG ,
 60 F6 ,
 61 F6_ORG ,
 62 LOCAL_TXN_AMNT ,
 63 LOCAL_CRR_CODE ,
 64 LYL_CAMPAIGN_CODE ,
 65 LYL_BANK_POINT ,
 66 LYL_BANK_CAMP_POINT ,
 67 LYL_MRC_POINT ,
 68 LYL_MRC_CAMP_POINT ,
 69 LYL_MRC_CHAIN_POINT ,
 70 LYL_MRC_CHAIN_CAMP_POINT ,
 71 F7 ,
 72 F7_ORG ,
 73 F11_TRM ,
 74 F11_SYS ,
 75 F11_SYS_ORG ,
 76 F12 ,
 77 F13 ,
 78 F14 ,
 79 F18 ,
 80 F22 ,
 81 F23 ,
 82 F24 ,
 83 F25 ,
 84 F32 ,
 85 F33 ,
 86 F37 ,
 87 F37_ORG ,
 88 F38 ,
 89 F39 ,
 90 F41 ,
 91 F42 ,
 92 F43 ,
 93 F43_NAME ,
 94 F43_CITY ,
 95 F43_STATE ,
 96 F43_COUNTRY ,
 97 F47 ,
 98 F49 ,
 99 F51 ,
100 F54 ,
101 F55_T82 ,
102 F55_T84 ,
103 F55_T95 ,
104 F55_T9A ,
105 F55_T9C ,
106 F55_T5F2A ,
107 F55_T5F34 ,
108 F55_T9F02 ,
109 F55_T9F03 ,
110 F55_T9F09 ,
111 F55_T9F10 ,
112 F55_T9F1A ,
113 F55_T9F1E ,
114 F55_T9F26 ,
115 F55_T9F27 ,
116 F55_T9F33 ,
117 F55_T9F34 ,
118 F55_T9F35 ,
119 F55_T9F36 ,
120 F55_T9F37 ,
121 F55_T9F41 ,
122 F55_T9F53 ,
123 F55_T9F5B ,
124 F55_TCVN ,
125 F55_TDKI ,
126 F55_T71 ,
127 F55_T72 ,
128 F55_T91 ,
129 F55_TC0 ,
130 F55_LEN ,
131 CVM_TYPE ,
132 CRD_SRC ,
133 CRD_CLS ,
134 CRD_BRD ,
135 CRD_DCI ,
136 ACCOUNTING_SEGMENT ,
137 ACCOUNT_NO ,
138 MAIN_ACCOUNT_NO ,
139 TXN_SETTLE ,
140 TXN_SETTLE_DATE ,
141 TXN_STT ,
142 TXN_STT_FUP ,
143 MRC_DAILY_MOVED_DATE ,
144 MRC_DAILY_MOVED ,
145 CYCLE_MOVED_DATE ,
146 CYCLE_MOVED ,
147 IS_PARTIAL ,
148 IS_DEFERED ,
149 CLEARING_CHANNEL ,
150 CLEARING_NO ,
151 CLEARING_F4 ,
152 CLEARING_F49 ,
153 CLEARING_F6 ,
154 CLEARING_F51 ,
155 CLEARING_COMM_AMOUNT ,
156 COMMISSION_PROFILE_GUID ,
157 COMISSION_AMNT ,
158 TAX_AMOUNT ,
159 TAX_AMOUNT_PNT ,
160 STOPPAGE_TAX_AMOUNT ,
161 DST_GATE_CODE ,
162 FWD_GATE_ADDR ,
163 TPDU ,
164 TPDU_ORG ,
165 PRC_TIME ,
166 SSF_SPND_TIME ,
167 SSF_RSP_CODE ,
168 SSF_DET_CODE ,
169 REQ_DATE ,
170 REQ_TIME ,
171 REQ_MSEC ,
172 RSP_DATE ,
173 RSP_TIME ,
174 RSP_MSEC ,
175 OPERATOR_CODE ,
176 GUID_GATE ,
177 F60 ,
178 F61 ,
179 F90 ,
180 F95 ,
181 IS_RECURRING ,
182 TERM_ENTRY_CAPABILITY ,
183 CARDHOLDER_PRESENCE_IND ,
184 GIFT_CODE ,
185 INSTALLMENT_CODE ,
186 LYL_DEFERED_DAYS ,
187 LYL_ADD_INST_CNT ,
188 F31 ,
189 REIMB_ATTR ,
190 EOD_INSTALL_STAT ,
191 BANKING_INV_NO ,
192 BANKING_INV_DATE ,
193 BANKING_SETTLEMENT_NO ,
194 OFFLINE_PIN_STATUS ,
195 CONFIRM_STAT ,
196 CHAIN_GUID ,
197 PAYMENT_SEQ_NO ,
198 PAN_USED ,
199 APP_CODE ,
200 EOD_INSTALL_DATE ,
201 COMP_8A ,
202 COMP_95 ,
203 COMP_9F10 ,
204 COMP_9F26 ,
205 COMP_9F27 ,
206 COMP_9F36 ,
207 COMP_9B ,
208 COMP_SCR_RESULT ,
209 REV_REASON ,
210 TERM_BRANCH ,
211 BANK_DECL_STAT ,
212 BANK_DECL_DATE ,
213 CARD_FIRST_INSTALL_DATE ,
214 CARD_FIRST_INSTALL_AMNT ,
215 BANK_ACCOUNT_BRANCH ,
216 BANK_ACCOUNT ,
217 MRC_COMM_TEMPLATE_DET_GUID ,
218 MRC_PRODUCT_TYPE ,
219 MRC_TERM_TYPE ,
220 CAVV_IND ,
221 CAVV_RESULT ,
222 CAVV_DATA ,
223 ECI ,
224 SRC_NTW_DRC ,
225 SRC_GATE_CODE ,
226 POINT_TYPE ,
227 GUID_ORG ,
228 INTEREST_RATE ,
229 FRD_TIME ,
230 BONUS_COLL_ACCOUNT_NO ,
231 IS_BONUS_USED ,
232 BANK_REF_NO ,
233 F2_USED ,
234 FROM_BANK_ACCOUNT ,
235 FROM_BRANCH ,
236 TO_BANK_ACCOUNT ,
237 TO_BRANCH ,
238 BANK_ACCOUNTING_SERVICE_CODE ,
239 BANK_ACCOUNTING_CONTRACT_NO ,
240 F22_PCC ,
241 OPENED_LIMIT ,
242 CLEARING_F5 ,
243 CLEARING_F50 ,
244 IS_ORG_TXN_REC ,
245 COMM_APPLIED ,
246 COMM_CALCULATED ,
247 COMM_REF_GUID ,
248 SPC_COMM_AMNT ,
249 CLOSED_AMOUNT ,
250 ORDER_NO ,
251 INSTITUTION_CODE ,
252 SUBSCRIBER_NO ,
253 USE_IN_EXPIRE ,
254 POINT_VALID_DATE ,
255 LYL_DISCOUNT
256 FROM
257 OC_TXN.TXN_CREDIT@VIEW_NEW_KK1;

Materialized view created.

SQL> 
SQL>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: