No question at this time
DBA Top 10
1 B. Vroman 13400
2 M. Cadot 10400
3 J. Schnackenberg 8300
4 T. Boles 7850
5 A. Kavsek 5700
6 M. Hidayathullah ... 2200
7 G. Lambregts 1100
8 T. P 1000
8 P. Wisse 1000
10 . Lauri 800
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48302
Total active users1583
Act. users last 24h3
Act. users last hour0
Registered user hits last week198
Registered user hits last month812
Go up

Creation of Materialized View on View fails
Next thread: Archives have to be deleted by RMAN from Standby Site
Prev thread: oracle weblogic (Reports issue)

Message Score Author Date
Hello, Yet another strange problem... I have...... Cedric Carbier Sep 26, 2018, 09:56
Firstly, the terminal version of Oracle 9i was 9.2...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Gareth Graham Sep 26, 2018, 10:31
Hello, Unfortunately, I cannot patch nor upgrad...... Cedric Carbier Sep 26, 2018, 11:53
Hi, out of curiosity, what happens if you creat...... Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts Ales Kavsek Sep 26, 2018, 14:33
Hello, Indeed, it does work with the "WITH ROWI...... Cedric Carbier Sep 26, 2018, 17:01

Follow up by mail Click here


Subject: Creation of Materialized View on View fails
Author: Cedric Carbier, France
Date: Sep 26, 2018, 09:56, 81 days ago
Os info: AIX 5.3 and AIX 7.1
Oracle info: 9.2.0.6 and 11.2.0.4
Error info: ORA-12014
Message: Hello,

Yet another strange problem...

I have a first database, called A, in version 11.2.0.4. This database contains several static views.

I have a second database, called B, in version 9.2.0.6, on a separate server.

I have a database link, called DBLINK_TO_A, going from database B to database A.

I would like to create, on database B, materialized views pointing to the static views of database A, with DDL instructions such as :


CREATE MATERIALIZED VIEW OWNER_B.GAGENT_V2
TABLESPACE TS_G
NOLOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE
WITH PRIMARY KEY
AS
select *
from OWNER_A.GAGENT_V@DBLINK_TO_A;


But most of time the MV creation fails with the following message :

ORA-12014: Table GAGENT_V Does Not Contain a Primary Key Constraint


Note that the underlying table of the GAGENT_V view does contain a primary key.

Any ideas ?

Thank you very much.

Best regards,
Cedric
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Creation of Materialized View on View fails
Author: Gareth Graham, United Kingdom
Date: Sep 26, 2018, 10:31, 81 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Firstly, the terminal version of Oracle 9i was 9.2.0.8.

Secondly, 11.2.0.4 knows more about 9.2.0.8 than vice versa.

Can you patch/upgrade?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Creation of Materialized View on View fails
Author: Cedric Carbier, France
Date: Sep 26, 2018, 11:53, 81 days ago
Message: Hello,

Unfortunately, I cannot patch nor upgrade this old database : the softwares that connect to it would not support another version.

Best regards,
Cedric

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Creation of Materialized View on View fails
Author: Ales Kavsek, Slovenia
Date: Sep 26, 2018, 14:33, 80 days ago
Score:   Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts
Message: Hi,

out of curiosity, what happens if you create mv WITH ROWID, instead of primary key?

lp,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Creation of Materialized View on View fails
Author: Cedric Carbier, France
Date: Sep 26, 2018, 17:01, 80 days ago
Message: Hello,

Indeed, it does work with the "WITH ROWID" clause.

The 12 MV I needed are now successfully created, and included in a refresh group that fires every minute.

Thank you very much :-)

Best regards,
Cedric
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here