标题: 数据库安装RU的DATAPATCH报：ORA-01732 [打印本页] 作者: 郑全 时间: 2023-8-4 13:49 标题: 数据库安装RU的DATAPATCH报：ORA-01732 在安装完RU后，在执行./datapatch -verbose时，发现报错：
DBD::Oracle::st execute failed: ORA-01732: data manipulation operation not legal on this view
ORA-06512: at line 2 (DBD ERROR: OCIStmtExecute) [for Statement "BEGIN
INSERT INTO sys.dba_registry_sqlpatch_ru_info
(patch_id,
patch_uid,
patch_descriptor,
ru_version,
ru_build_description,
ru_build_timestamp,
patch_directory)
VALUES
(:patch_id,
:patch_uid,
:patch_descriptor,
:ru_version,
:ru_build_description,
TO_TIMESTAMP(:ru_build_timestamp, 'YYMMDDHH24MISS'),
:patch_directory);
COMMIT;
END;" with ParamValues: :patch_descriptor=OCIXMLTypePtr=SCALAR(0x326f5f0), :patch_directory
经MOS查询，发现是On a Materialized View environment , value of "_simple_view_merging parameter" parameter was set to FALSE 导致，这个确实是前面19.12上的一个BUG，修改了该参数，去掉该参数后，再去执行DATAPATCH ，问题消失。
附：
Datapatch Fails with "ORA-01732: data manipulation operation not legal error" (Doc ID 2058586.1)
In this Document
Symptoms
Cause
Solution
References
APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
After applying one-off Patch 21290101 , post install step produces below error
$ datapatch
Validating logfiles...
DBD::Oracle::st execute failed: ORA-01732: data manipulation operation not legal on this view (DBD ERROR: error possibly near <*> indicator at char 7 in 'UPDATE <*>dba_registry_sqlpatch
SET status = :p1, action_time = SYSTIMESTAMP
WHERE patch_id = :p2
AND patch_uid = :p3
AND action = UPPER(:p4)
AND action_time = (SELECT MAX(action_time)
FROM dba_registry_sqlpatch
WHERE patch_id = :p5
AND patch_uid = :p6
AND action = UPPER(:p7))') [for Statement "UPDATE dba_registry_sqlpatch
SET status = ?, action_time = SYSTIMESTAMP
WHERE patch_id = ?
AND patch_uid = ?
AND action = UPPER(?)
AND action_time = (SELECT MAX(action_time)
FROM dba_registry_sqlpatch
WHERE patch_id = ?
AND patch_uid = ?
AND action = UPPER(?))" with ParamValues: :p1='SUCCESS', :p2='19877336', :p3='18313828', :p4='apply', :p5='19877336', :p6='18313828', :p7='apply'] at /u01/app/oracle/product/12.1.0/dbhome/sqlpatch/sqlpatch.pm line 2659.
CAUSE
On a Materialized View environment , value of "_simple_view_merging parameter" parameter was set to FALSE
SOLUTION
1. Log in to database as sysdba
SQL>connect sys as sysdba
2. Modify the parameter "_simple_view_merging parameter" to TRUE.
SQL>ALTER SYSTEM SET "_simple_view_merging"=TRUE
3. Execute datapatch.