Thanks ishando for reply
I added the Id on the join and it does not work too. I create the same MV with out the outer join and it works. But i need the outer join
how to do this fast refresh ?
Thanks.
Main Topics
Browse All Topics TABLE EMP_VM_1 CASCADE CONSTRAINTS;
-- Table EMP_VM_1
CREATE TABLE EMP_VM_1
(
ID NUMBER(2) NOT NULL,
NOM VARCHAR2(30 BYTE) NOT NULL,
PRENOM VARCHAR2(30 BYTE) NOT NULL,
DEPARTEMENT NUMBER(5) NOT NULL,
VILLE VARCHAR2(50 BYTE)
)
TABLESPACE MV_TEST
;
-- Create Index and Primary Key
CREATE UNIQUE INDEX PK_EMP_VM_1 ON EMP_VM_1
(ID, NOM, PRENOM, DEPARTEMENT)
LOGGING
TABLESPACE MV_TEST
NOPARALLEL;
ALTER TABLE EMP_VM_1 ADD (
CONSTRAINT PK_EMP_VM_1
PRIMARY KEY
(ID, NOM, PRENOM, DEPARTEMENT)
USING INDEX
TABLESPACE MV_TEST
);
-- Insert Data
Insert into EMP_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE)
Values
(1, 'AYE', 'Mou', 93450, 'Ile Saint Denis');
Insert into EMP_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE)
Values
(2, 'AYE', 'Ade', 93200, 'Saint Denis');
Insert into EMP_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE)
Values
(3, 'OUN', 'Ima', 93350, 'VILLEMOMBRE');
Insert into EMP_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE)
Values
(4, 'BEN', 'You', 92000, 'Casblanca');
Insert into EMP_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE)
Values
(5, 'OBA', 'Bar', 1000, 'WASHINGTON');
Insert into EMP_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE)
Values
(6, 'OBA', 'Hus', 1000, 'WASHINGTON');
COMMIT;
-- TABLE POSTE_VM_1
ALTER TABLE POSTE_VM_1
DROP PRIMARY KEY CASCADE;
DROP TABLE POSTE_VM_1 CASCADE CONSTRAINTS;
CREATE TABLE POSTE_VM_1
(
ID NUMBER(2) NOT NULL,
NOM VARCHAR2(30 BYTE) NOT NULL,
PRENOM VARCHAR2(30 BYTE) NOT NULL,
DEPARTEMENT NUMBER(5) NOT NULL,
VILLE VARCHAR2(50 BYTE),
POSTE VARCHAR2(30 BYTE) NOT NULL,
SAL number(10,2)
)
TABLESPACE MV_TEST;
-- Indexe and Primary Key
CREATE UNIQUE INDEX PK_PERSO_VM_1 ON POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, POSTE)
LOGGING
TABLESPACE MV_TEST;
ALTER TABLE POSTE_VM_1 ADD (
CONSTRAINT PK_PERSO_VM_1
PRIMARY KEY
(ID, NOM, PRENOM, DEPARTEMENT, POSTE)
USING INDEX
TABLESPACE MV_TEST
);
-- Insert Data
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(1, 'AYE', 'Mou', 93450, 'Ile Saint Denis','DBA Oracle', 3500);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(1, 'AYE', 'Mou', 93450, 'Ile Saint Denis','Consultant', 5500);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(1, 'AYE', 'Mou', 93450, 'Ile Saint Denis','Boulanger', 2000);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(1, 'AYE', 'Mou', 93450, 'Ile Saint Denis','Epicier',3000);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(2, 'AYE', 'Ade', 93200, 'Saint Denis','Chauffeur livreur', 1800);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(2, 'AYE', 'Ade', 93200, 'Saint Denis', 'Etudiant', 0);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(2, 'AYE', 'Ade', 93200, 'Saint Denis','Dessinateur', 1890);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(2, 'AYE', 'Ade', 93200, 'Saint Denis','Géographe',2300);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(4, 'BEN', 'You', 92000,'Casblanca', 'Consultant', 3200);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(4, 'BEN', 'You', 92000, 'Casblanca', 'Businessman', 6200);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(4, 'BEN', 'You', 92000, 'Casblanca', 'Ingénieur', 3600);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(4, 'BEN', 'You', 92000, 'Casblanca', 'Commercial', 2900);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(5, 'OBA', 'Bar', 1000, 'WASHINGTON', 'Etudiant', 120);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(5, 'OBA', 'Bar', 1000, 'WASHINGTON', 'Businessman', 4700);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(5, 'OBA', 'Bar', 1000, 'WASHINGTON', 'Sénateur', 5000);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(5, 'OBA', 'Bar', 1000, 'WASHINGTON','President', 12500);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(6, 'OBA', 'Hus', 1000, 'WASHINGTON', 'Etudiant', 1000);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(6, 'OBA', 'Hus', 1000, 'WASHINGTON', 'Businessman', 6800);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(6, 'OBA', 'Hus', 1000, 'WASHINGTON', 'Sénateur', 7200);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(6, 'OBA', 'Hus', 1000, 'WASHINGTON', 'President', 13000);
COMMIT;
-- SNAPSHOT LOG ON EMP_VM_1
--
DROP SNAPSHOT LOG ON EMP_VM_1;
CREATE MATERIALIZED VIEW LOG ON EMP_VM_1
WITH ROWID
EXCLUDING NEW VALUES;
-- SNAPSHOT LOG ON POSTE_VM_1
DROP SNAPSHOT LOG ON POSTE_VM_1;
CREATE MATERIALIZED VIEW LOG ON POSTE_VM_1
WITH ROWID
EXCLUDING NEW VALUES;
-- MATERIALIZED VIEW MV_POSTE_EMP
DROP MATERIALIZED VIEW MV_POSTE_EMP;
/* Creation VM */
CREATE MATERIALIZED VIEW MV_POSTE_EMP
TABLESPACE MV_TEST
NOCACHE
ENABLE ROW MOVEMENT
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH ROWID
ENABLE QUERY REWRITE
AS
select e.rowid as rowide , p.rowid as rowidp,e.id, e.nom, e.prenom, e.departement, e.ville,p.poste , p.SAL from poste_vm_1 p, emp_vm_1 e
where e.nom = p.nom
and e.prenom = p.prenom
and e.departement = p.departement
and e.ville = p.ville(+);
COMMENT ON TABLE MV_POSTE_EMP IS 'snapshot table for snapshot MV_POSTE_EMP';
CREATE INDEX PK_MV_POSTE_EMP ON MV_POSTE_EMP
(ID, NOM, PRENOM, DEPARTEMENT, POSTE)
LOGGING
TABLESPACE MV_TEST
NOPARALLEL;
-- IS THE SNAPSHOT LOGS POPULATED : YES
SELECT *
FROM "MLOG$_EMP_VM_1"
/* Then I insert those lines */
/* Insert into EMP_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE)
Values
(7, 'DUP', 'DID', 75017, 'PARIS');
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(7, 'DUP', 'DID', 75017,'' ,'DBA Oracle', 3500);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(7, 'DUP', 'DID', 75017, 'PARIS' ,'Footballeur', 25000);
Insert into POSTE_VM_1
(ID, NOM, PRENOM, DEPARTEMENT, VILLE, POSTE,SAL)
Values
(7, 'DUP', 'DID', 75017, 'PARIS' ,'Maçon', 2000);
/* Then i try a fast refresh */
BEGIN
DBMS_SNAPSHOT.REFRESH(
LIST => 'MV_POSTE_EMP'
,METHOD => 'F'
,PUSH_DEFERRED_RPC => TRUE
,REFRESH_AFTER_ERRORS => FALSE
,PURGE_OPTION => 1
,PARALLELISM => 0
,ATOMIC_REFRESH => TRUE);
END;
Here i have the message :
ORA-12052: REFRESH FAST impossible sur la vue matérialisée MOUNIR.MV_POSTE_EMP
ORA-06512: à "SYS.DBMS_SNAPSHOT", ligne 803
ORA-06512: à "SYS.DBMS_SNAPSHOT", ligne 860
ORA-06512: à "SYS.DBMS_SNAPSHOT", ligne 841
ORA-06512: à ligne 2
PLEASE HELP
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hey ishando:
I'm sorry the solution you propose is not right, because
e.ville = nvl(p.ville,e.ville) is not the same thing as e.ville = p.ville(+);
The solution i found to be able to fast refresh my viw is that the outer join must be on all the key columns
and here it must be:
CREATE MATERIALIZED VIEW MV_POSTE_EMP
TABLESPACE MV_TEST
NOCACHE
ENABLE ROW MOVEMENT
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH ROWID
ENABLE QUERY REWRITE
AS
select e.rowid as rowide , p.rowid as rowidp,e.id, e.nom, e.prenom, e.departement, e.ville,p.poste , p.SAL from poste_vm_1 p, emp_vm_1 e
where e.nom = p.nom(+)
and e.prenom = p.prenom (+)
and e.departement = p.departement(+)
and e.ville = p.ville(+);
Thanks a lot anyway
Business Accounts
Answer for Membership
by: ishandoPosted on 2008-12-03 at 02:17:05ID: 23084593
ORA-12052 cannot fast refresh materialized view string.string
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 occurred during creation, the materialized view definition may have been changed. Refer to the documentation on materialized views.
Based on this, your inner table has a unique key on the columns (ID, NOM, PRENOM, DEPARTEMENT)
but you are not using the ID in your join condition in the MV - looks like you would need to either add the ID field to the join, or else use a complete refresh.