重庆思庄Oracle、Redhat认证学习论坛

标题: 创建物化视图时出现ORA-12054告警 [打印本页]

作者: denglj 时间: 2022-11-11 13:17
标题: 创建物化视图时出现ORA-12054告警

问题描述：创建物化视图时出现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(*)

----------

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同样也会更新信息.

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)