-
Materialized Views
Hi All
Is it Possible to create a materialized view if the underlying table has no primary key?
I tried creating a materialized view without specifying the refresh mode and I get an
ORA-12014: table 'Table_name' does not contain a primary key constraint.
Please Assist
-
ORA-12014: table name does not contain a primary key constraint
Cause: The CREATE SNAPSHOT LOG command was issued with the WITH PRIMARY KEY option and the master table does not contain a primary key constraint or the constraint is disabled.
Action: Reissue the command using only the WITH ROWID option, create a primary key constraint on the master table, or enable an existing primary key constraint.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
Re: Materialized Views
Originally posted by clarence
Please Assist
Please try to read the manual first
-
Thanx for the Reply.
on the database that I am trying to create a MV there are no snapshot_logs. I ran these queries to check.
SQL> select count(*) from USER_SNAPSHOT_LOGS;
COUNT(*)
----------
0
SQL> select * from ALL_SNAPSHOT_LOGS;
no rows selected
Thanx.
-
Well have you created any? They are not created automatically you know?
-
Originally posted by pando
Well have you created any? They are not created automatically you know?
Sanjay suggeted that the cause of the problem might be that the snapshot log was created with a primary key clause
Cause: The CREATE SNAPSHOT LOG command was issued with the WITH PRIMARY KEY option and the master table does not contain a primary key constraint or the constraint is disabled.
That's why I checked if there are any existing snapshot logs.
-
Code:
WITH PRIMARY KEY
Specify WITH PRIMARY KEY to indicate that a primary key materialized view is to be created. This is the default, and should be used in all cases except those described for WITH ROWID. Primary key materialized views allow materialized view master tables to be reorganized without affecting the materialized view's ability to continue to fast refresh. The master table must contain an enabled primary key constraint.
See Also: Oracle8i Replication for detailed information about primary key materialized views
WITH ROWID
Specify WITH ROWID to indicate that a rowid materialized view is to be created. Rowid materialized views provide compatibility with master tables in releases of Oracle prior to 8.0.
You can also use rowid materialized views if the materialized view does not include all primary key columns of the master tables. Rowid materialized views must be based on a single remote table and cannot contain any of the following:
Distinct or aggregate functions
GROUP BY or CONNECT BY clauses
Subqueries
Joins
Set operations
As you can see by default creating MV the refresh is based on PK, and in order for that to work you must have PK in master table
Spend sometime read the manual for your own benefit
-
Thanx for your input. This was the part i did not clearly understand , and now it make perfect sense.
Thanx a 1 000000
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|