数据库安装RU的DATAPATCH报：ORA-01732

原创不为什么 2023-08-11

802

在安装完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.

oracle 12c

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

数据库安装RU的DATAPATCH报：ORA-01732

评论