Thursday, June 07, 2012

Comments on Materialized Views



In Oracle 9.2.0.7.0 you had to put comments on materialized views as follows:
SQL> create table andrews_table (col1 number)
  2  /
 
Table created.
 
SQL> create materialized view andrews_mv
  2  refresh with rowid
  3  as select * from andrews_table
  4  /
 
Materialized view created.
 
SQL> comment on table andrews_mv is
  2  'My first materialized view'
  3  /
 
Comment created.
 
SQL>
This no longer works in Oracle 11.2.0.1.0. You have to do it like this instead:
SQL> create table andrews_table (col1 number)
  2  /
 
Table created.
 
SQL> create materialized view andrews_mv
  2  refresh with rowid
  3  as select * from andrews_table
  4  /
 
Materialized view created.
 
SQL> comment on table andrews_mv is
  2  'My first materialized view'
  3  /
comment on table andrews_mv is
                 *
ERROR at line 1:
ORA-12098: cannot comment on the materialized view
 
SQL> comment on materialized view andrews_mv is
  2  'My first materialized view'
  3  /
 
Comment created.
 
SQL>

No comments: