Materialized Views
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Materialized Views

Hybrid View

  1. #1
    Join Date
    Mar 2002
    Posts
    25

    Question 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

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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"

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

    Re: Materialized Views

    Originally posted by clarence

    Please Assist
    Please try to read the manual first

  4. #4
    Join Date
    Mar 2002
    Posts
    25
    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.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Well have you created any? They are not created automatically you know?

  6. #6
    Join Date
    Mar 2002
    Posts
    25
    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.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  8. #8
    Join Date
    Mar 2002
    Posts
    25
    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
  •  


Click Here to Expand Forum to Full Width