Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Is it not possible to create a fast refresh MV without ROWID's

PraneethGSep 3 2015 — edited Sep 4 2015

I have used the below script to create my fast refresh mv. do i need to capture rowid's for sure or i am going wrong in creating the below mv.

ALTER TABLE si_users_t

ADD CONSTRAINT user_id_pk PRIMARY KEY(user_id);

ALTER TABLE si_org_roles

ADD CONSTRAINT org_role_id_pk PRIMARY KEY(org_role_id);

ALTER TABLE si_user_org_roles

ADD CONSTRAINT org_role_user_id_pk PRIMARY KEY(org_role_id,user_id);

ALTER TABLE si_role_activities

ADD CONSTRAINT act_role_id_pk PRIMARY KEY(activity_id,role_id);

ALTER TABLE si_activities_t

ADD CONSTRAINT activity_id_pk PRIMARY KEY(activity_id);

ALTER TABLE si_org_entities_t

ADD CONSTRAINT org_entity_id_pk PRIMARY KEY(org_entity_id);

ALTER TABLE si_roles_t

ADD CONSTRAINT role_id_pk PRIMARY KEY(role_id);

CREATE MATERIALIZED VIEW LOG ON si_org_roles        WITH PRIMARY KEY,SEQUENCE INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON si_users_t           WITH PRIMARY KEY,SEQUENCE INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON si_user_org_roles   WITH PRIMARY KEY,SEQUENCE INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON si_role_activities  WITH PRIMARY KEY,SEQUENCE INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON si_activities_t     WITH PRIMARY KEY,SEQUENCE INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON si_org_entities_t   WITH PRIMARY KEY,SEQUENCE INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON si_roles_t         WITH PRIMARY KEY,SEQUENCE INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW MS_APPS_USER_ORG_ACTVITIES_MV

BUILD IMMEDIATE

REFRESH FAST

ON DEMAND

WITH PRIMARY KEY

AS

SELECT 

      users.user_name        user_name

     ,users.user_id          user_id

     ,act.activity_name      activity_name

     ,act.activity_id        activity_id

     ,orgs.org_entity_name   org_entity_name

     ,orgs.org_entity_id     org_entity_id

     ,roles.role_id          role_id

     ,roles.role_name        role_name

FROM si_users_t users,

     si_user_org_roles usr_org_roles,

     si_org_roles org_roles,

     si_role_activities role_act,

     si_activities_t act,

    si_org_entities_t orgs,

    si_roles_t roles

WHERE users.user_id = usr_org_roles.user_id

  AND usr_org_roles.org_role_id      = org_roles.org_role_id

  AND role_act.role_id               = org_roles.role_id

  AND role_act.activity_id           = act.activity_id

  AND orgs.org_entity_id             = org_roles.org_entity_id

  AND roles.role_id                  = org_roles.role_id;

and i come across

ORA-12052: cannot fast refresh materialized view SG61SP5PLAB.MS_APPS_USER_ORG_ACTVITIES_MV

12052. 00000 -  "cannot fast refresh materialized view %s.%s"

*Cause:    Either ROWIDs of certain tables were missing in the definition or

           the inner table of an outer join did not have UNIQUE constraints on

           join columns.

*Action:   Specify the FORCE or COMPLETE option. If this error is got

           during creation, the materialized view definition may have be

           changed. Refer to the documentation on materialized views.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 2 2015
Added on Sep 3 2015
3 comments
828 views