创建物化视图时出现ORA-12054告警

denglj

问题描述：创建物化视图时出现ORA-12054告警，如下所示：

数据库：oracle19.12 64位

异常现象:

SQL>desc emp

Name                                                 Null?    Type

------------------------------------------------------------- ------------------------------------

EMPLOYEE_ID                                           NOTNULL NUMBER(6)

FIRST_NAME                                                    VARCHAR2(9)

LAST_NAME                                            NOT NULL VARCHAR2(25)

EMAIL                                                 NOT NULL VARCHAR2(25)

PHONE_NUMBER                                                  VARCHAR2(20)

HIRE_DATE                                            NOT NULL TIMESTAMP(6)

JOB_ID                                                NOT NULL VARCHAR2(12)

SALARY                                                        NUMBER(8,2)

COMMISSION_PCT                                                NUMBER(2,2)

MANAGER_ID                                                    NUMBER(6)

DEPARTMENT_ID                                                 NUMBER(4)

SQL>select count(*) from emp;

  COUNT(*)

----------

        20

SQL>create materialized view v_emp

  2 refresh force on commit

  3  as

  4 select employee_id,first_name,last_name,email,phone_number,hire_datefrom emp;

selectemployee_id,first_name,last_name,email,phone_number,hire_date from emp

                                                                         *

ERROR atline 4:

ORA-12054:cannot set the ON COMMIT refresh attribute for the materialized view

说明：如上所示，ORA-12054告警异常重现.

异常原因：

创建物化视图的基表没有主键.

解决过程：

查emp表是否存在主键.

SQL>SELECT                                                                 

  2      col.column_name

  3  FROM

  4      user_constraints con,user_cons_columns col

  5 WHERE

  6      con.constraint_name=col.constraint_name and con.constraint_type='P'

  7      and col.table_name='EMP';

no rowsselected

给emp表添加主键.

SQL>alter table emp add constraint pk_emp_employee_id primary key (employee_id);

Table altered.

SQL>SELECT                                                                 

  2      col.column_name

  3  FROM

  4      user_constraints con,user_cons_columns col

  5 WHERE

  6      con.constraint_name=col.constraint_name and con.constraint_type='P'

  7      and col.table_name='EMP';

COLUMN_NAME

--------------------

EMPLOYEE_ID

确定表emp存在主键后，再次创建物化视图，无异常发生.

SQL>create materialized view v_emp

  2 refresh force on commit

  3  as

  4 select employee_id,first_name,last_name,email,phone_number,hire_datefrom emp;

Materializedview created.

SQL>select segment_name,segment_type,segment_subtype,tablespace_name,bytes fromuser_segments where segment_name like '%PK_EMP_EMPLOYEE_ID%' orsegment_name='V_EMP'

SEGMENT_NAME                        SEGMENT_TYPE       SEGMENT_SU TABLESPACE_NAME      BYTES

----------------------------------------------------- ---------- --------------- ----------

PK_EMP_EMPLOYEE_ID                  INDEX              ASSM       USERS                65536

SYS_C_SNAP$_5PK_EMP_EMPLOYEE_ID     INDEX              ASSM       USERS                65536

V_EMP                               TABLE              ASSM       USERS                65536

说明：如上所示，物化视图创建好后实则为实实在在的表，而且系统会自动产生名为SYS_C_SNAP$_5PK_EMP_EMPLOYEE_ID的索引，都会也会占用空间.这是与普通视图最大的区别.

SQL>update emp set email='aaaa' where employee_id=100;

1 rowupdated.

SQL>select * from v_emp where employee_id=100;

EMPLOYEE_IDFIRST_NAM LAST_NAME                EMAIL                     PHONE_NUMBER         HIRE_DATE

-------------------- ------------------------- --------------------------------------------- ------------------------------

        100 Steven    King                      SKING                     515.123.4567         17-JUN-11 12.00.00.000000 AM

SQL>commit;

Commitcomplete.

SQL>select * from v_emp where employee_id=100;

EMPLOYEE_IDFIRST_NAM LAST_NAME                EMAIL                    PHONE_NUMBER         HIRE_DATE

-------------------- ------------------------- --------------------------------------------- ------------------------------

        100 Steven    King                      aaaa                      515.123.4567         17-JUN-11 12.00.00.000000 AM

SQL>update v_emp set email='bbbb' where employee_id=100;

updatev_emp set email='bbbb' where employee_id=100

       *

ERROR atline 1:

ORA-01732:data manipulation operation not legal on this view

说明：一旦提交更新主表emp相关信息后，物化视图v_emp同样也会更新信息.