Materialized View - Cannot specify ON COMMIT clause

Hello there,
I'm getting an error
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
when I try creating the following materialized view. From the documentation, I cannot see any constraints on the ON COMMIT clause being violated but can someone help please?

create materialized view mv_sca_liability_pool_factors
build immediate
refresh force on commit
with primary key
enable query rewrite
as
   SELECT ISIN,
          POOL_FACTOR,
          POOL_FACTOR_DATE,
          CREATED,
          CREATED_BY,
          LAST_EDIT,
          LAST_EDIT_BY
     FROM SCA_SECURITY_POOL_FACTORS spf
    WHERE NOT EXISTS
                 (SELECT *
                    FROM SCA_SECURITY_WRITEDOWN_PF wpf
                   WHERE     wpf.ISIN = spf.ISIN
                         AND wpf.POOL_FACTOR_DATE = spf.POOL_FACTOR_DATE
                         AND wpf.POOL_FACTOR = spf.POOL_FACTOR
                         AND wpf.VALID_YN = 'Y'
                         AND wpf.CREATED =
                                (SELECT max(wpf1.CREATED)
                                   FROM SCA_SECURITY_WRITEDOWN_PF wpf1
                                  WHERE wpf1.VALID_YN = 'Y'
                                        AND wpf1.ISIN = wpf.ISIN
                                        AND wpf.POOL_FACTOR_DATE =
                                               wpf1.POOL_FACTOR_DATE))
   UNION
   SELECT ISIN,
          POOL_FACTOR_PRIOR_WRITEDOWN,
          POOL_FACTOR_DATE,
          CREATED,
          CREATED_BY,
          CREATED,
          CREATED_BY
     FROM SCA_SECURITY_WRITEDOWN_PF swpf;


Thanks,
Leo
create materialized view log on SCA_SECURITY_POOL_FACTORS;
create materialized view log on SCA_SECURITY_WRITEDOWN_PF;

base tables definition:

CREATE TABLE SCA_OWNER.SCA_SECURITY_POOL_FACTORS
(
  ISIN              VARCHAR2(24 BYTE),
  POOL_FACTOR       NUMBER                      NOT NULL,
  POOL_FACTOR_DATE  DATE,
  CREATED           DATE,
  CREATED_BY        VARCHAR2(15 BYTE),
  LAST_EDIT         DATE,
  LAST_EDIT_BY      VARCHAR2(15 BYTE)
)
ALTER TABLE SCA_OWNER.SCA_SECURITY_POOL_FACTORS ADD (
  CONSTRAINT SCA_SECURITY_POOL_FACTORS_PK
  PRIMARY KEY
  (ISIN, POOL_FACTOR_DATE, CREATED)
  USING INDEX SCA_OWNER.SCA_SECURITY_POOL_FACTORS_PK);



CREATE TABLE SCA_OWNER.SCA_SECURITY_WRITEDOWN_PF
(
  ISIN                         VARCHAR2(25 BYTE),
  POOL_FACTOR_DATE             DATE,
  POOL_FACTOR                  NUMBER,
  POOL_FACTOR_PRIOR_WRITEDOWN  NUMBER,
  VALID_YN                     VARCHAR2(1 BYTE),
  CREATED_BY                   VARCHAR2(50 BYTE),
  CREATED                      DATE
)

ALTER TABLE SCA_OWNER.SCA_SECURITY_WRITEDOWN_PF ADD (
  CONSTRAINT SCA_SECURITY_WRITEDOWN_PF_PK
  PRIMARY KEY
  (ISIN, CREATED)
  USING INDEX SCA_OWNER.SCA_SECURITY_WRITEDOWN_PF_PK);

Open in new window

lm78Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

devindCommented:
As per oracle documentation
http://download.oracle.com/docs/cd/B10500_01/server.920/a96520/mv.htm
ON COMMIT
...... This can be specified as long as the materialized view is fast refreshable (in other words, not complex).....

Materialized View you are trying to create in not fast refreshable, so you can not use ON COMMIT with it.

To verify the same

1) Create view using ON DEMAND
2) Run EXEC dbms_mview.explain_mview('MV_SCA_LIABILITY_POOL_FACTORS');
3) Query the View Capabilities
   SELECT capability_name,
            possible,
            substr(msgtxt,1,60) AS msgtxt
       FROM mv_capabilities_table
      WHERE capability_name like '%FAST%';

Note: If mv_capabilities_table table is missing then create it using $ORACLE_HOME/rdbms/admin/utlxmv.sql

Output will be as follows
Capability_name             Possible   MSGTXT
===============             ========   =======  
REFRESH_FAST	               N	
REFRESH_FAST_AFTER_INSERT	   N	      tables must be identical across the UNION operator
REFRESH_FAST_AFTER_INSERT	   N	      select lists must be identical across the UNION operator
REFRESH_FAST_AFTER_INSERT	   N	      the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML	N	      see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML	   N	      see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT	            N	      PCT is not possible on any of the detail tables in the mater

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lm78Author Commented:
Thanks - that answered the question on the UNION clause. I used that to check another materialized view which wasn't FAST refreshing and couldn't understand what it was so have put another question if you would like to take a look?

http://www.experts-exchange.com/Database/Oracle/Q_26936954.html

Thanks again,
Leo
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.