Create Materialized View Fails With ORA-30372

ORA-30372 error has been declared when user would like to refresh materialized view . Detailed explanation of error ,

Oracle Error: ORA-30372

Error Description:

Fine grain access policy conflicts with materialized view

Error Cause:

A fine grain access control procedure has applied a non-null policy to the query for the materialized view.

Action:

In order for the materialized view to work correctly, any fine grain access control procedure in effect for the query must return a null policy when the materialized view is being created or refreshed. This may be done by ensuring that the usernames for the creator, owner, and invoker of refresh procedures for the materialized view all receive a null policy by the user-written fine grain access control procedures.

If any table used in materialized view has VPD policy should return null while refreshing. This causes error and in order to solve this Oracle suggests to define policy function to user which will do operations to return NULL policy during create or refresh operations on materialized view.But it can cause security issues for this user who has some data access restrictions.

There is another solution .Following code can be put into security policy function and only while refreshing policy returns null.

if dbms_mview.i_am_a_refresh then

   return null;

end if;

SQL Profile ignores hints

Sometimes , hints can be very powerful to let optimizer choose proper execution plans. This lets application execute with good performance. But sometimes given hints to sqls can be harmful choosing wrong execution plans.At this situations , development can take care of this sqls and by removing hints problems can be solved. But if development process can not be done immediately . DBA can solve this issues by telling optimizer to ignore hints.

alter session set "_optimizer_ignore_hints"=true 

command ignores all hints during session. This can solve your problem but also can be trouble.At sql level ignoring hints can be done by import_sql_profile procedure in dbms_sqltune package.

For sample below sql is used . Firstly given hint for sql tell optimizer not to use index. But for some reason hint losed its validity and index should be used for query.

SQL> explain plan for select /*+no_index(s) */ count(1) from xdba_dcyear s where cust_name like 'ER%';

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3491557762
--------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |     1 |   252 |     2   (0)| 0
|   1 |  SORT AGGREGATE    |                    |     1 |   252 |            |
|*  2 |   TABLE ACCESS FULL| XDBA_DCYEAR |    20 |  5040 |     2   (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("CUST_NAME" LIKE 'ER%')
Note
-----
   - dynamic sampling used for this statement (level=2)
18 rows selected
Executed in 0.076 seconds

Now dbms_sqltune.import_sql_profile procudure is used with attribute "IGNORE_OPTIM_EMBEDDED_HINTS".

begin
 dbms_sqltune.import_sql_profile(
 name => 'SQLPROF1',
 category => 'DEFAULT',
 sql_text => 'select /*+no_index(s) */ count(1) from xdba_dim_cust_year s where cust_name like ''ER%''',

 profile => sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS'));
end;
/


SQL> explain plan for select /*+no_index(s) */ count(1) from xdba_dcyear s where cust_name like 'ER%';

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2253536563
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   252 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |   252 |            |          |
|*  2 |   INDEX RANGE SCAN| X1   |    20 |  5040 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUST_NAME" LIKE 'ER%')
       filter("CUST_NAME" LIKE 'ER%')
Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL profile "SQLPROF1" used for this statement
20 rows selected
Executed in 0.126 seconds

As a result no_index hint is ignored and index is being used according to explain plan. Also usage of "SQLPROF1" profile can be observe from explain plan notes

In order to remove sql profile following procedure is used.

begin
 dbms_sqltune.import.drop_sql_profile('SQLPROF1');
end;
/

Note : dbms_sqltune package is under Oracle Tuning Pack license.This must be considered.