ORA-12015 or ORA-28133 When Creating Materialized Views If The Base Table Has A FGA Policy (Doc ID 1065944.1)

Last updated on JANUARY 21, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.2 to 10.2.0.4 [Release 9.2 to 10.2]
Information in this document applies to any platform.
Checked for relevance on 08-APR-2013


Symptoms

When trying to create a materialized view on a table, which has a FGA policy defined, one of the following errors occurs: 


ORA-12015: cannot create a fast refresh materialized view from a complex query

ORA-28133: full table access is restricted by fine-grained security

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



connect / as sysdba
create user test identified by test ;
grant connect , resource to test ;

connect test/test ;
create table t (col1 number , col2  number ) ;
alter table t add constraint pk_t primary key ( col1 ) ;
insert into t values ( 1 , 1234 ) ;
commit ;

connect  / as sysdba
begin
    dbms_fga.add_policy(
    object_schema => 'TEST',
    object_name => 'T',
    policy_name => 'TEST_POLICY',
    audit_condition => 'col2 > 5',
    audit_column => 'col2',
    enable => true
    ) ;
end ;
/
create materialized view test.v1 refresh fast with primary key as select * from test.t ;
ERROR at line 1:
ORA-12015: cannot create a fast refresh materialized view from a complex query




Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms