Creating Materialized View on Remote Tables

[baskerk]

Hi,

I'm trying to create a Materialized View on Table residing on remote database using DB Link. It says "ORA-12014: table name does not contain a primary key constraint".

But the Remote Table does have Primary Key. Both the users[i.e. user who creates the materialized view and the user through which the db link is created] has DBA Access.

Syntax of Materialized View used

create materialized view <view_name> as
select * from <table_name>

What could be the reason for this?


Thanks,
Basker

 

[stefanhei]

Hi.
Check if the Primary key-constraint is enabled?

Stefan

 

[baskerk]

Hi,

Yes. Primary Key is enabled.

Thanks,
Basker

 

[karluk]

It looks as if you are running into the situation described by Metalink note 1038172.6. You have different ids which own the master table and materialized view. The reason the create materialized view is failing is because you need to specify a fully qualified owner.table_name. I guess this is understandable, but unfortunately Oracle somehow manages to generate a completely misleading error message

Please try this and see if it works:

create materialized view <view_name> as
select * from <owner>.<table_name>

 

[baskerk]

Hi Karluk,

Thanks a lot. It worked after giving the owner name as prefix to table name.

basker