datapatch fails with Error:ORA-22973

刘泽宇

现象：
datapatch fails with errors:

Validating logfiles...
Patch 11836540 apply (pdb WP027P): WITH ERRORS
logfile: /opt/oracle/cfgtoollogs/sqlpatch/11836540/21145521/11836540_apply_WC027P_WP027P_2017Jun27_06_33_54.log (errors)
Error at line 7690: ORA-22973: size of object identifier exceeds maximum size allowed
Error at line 7696: ORA-22973: size of object identifier exceeds maximum size allowed
Error at line 7702: ORA-22973: size of object identifier exceeds maximum size allowed
Error at line 7706: Warning: View created with compilation errors.
Error at line 7709: Warning: View created with compilation errors.
Error at line 7712: Warning: View created with compilation errors.
Checking the apply log shows these errors:

select * from ku$_m_view_view_base b
               *
ERROR at line 3:
ORA-22973: size of object identifier exceeds maximum size allowed


select * from ku$_m_view_view_base b
               *
ERROR at line 3:
ORA-22973: size of object identifier exceeds maximum size allowed


select * from ku$_m_view_view_base b
               *
ERROR at line 3:
ORA-22973: size of object identifier exceeds maximum size allowed
Another case,could be due to Patch rollback and datapatch would fail even if nls_length_semantics="BYTE"

Patch 25296876 rollback (pdb CDB$ROOT): WITH ERRORS
logfile: $ORACLE_BASE/cfgtoollogs/sqlpatch/25296876/21134475/25296876_rollback_DBNAME_CDBROOT_2019Dec16_14_26_11.log (errors)
   Error at line 715: ORA-22973: size of object identifier exceeds maximum size allowed
   Error at line 746: ORA-22973: size of object identifier exceeds maximum size allowed

Patch 25296876 rollback (pdb PDB$SEED): WITH ERRORS
logfile: $ORACLE_BASE/cfgtoollogs/sqlpatch/25296876/21134475/25296876_rollback_DBNAME_PDBSEED_2019Dec16_14_29_59.log (errors)
   Error at line 715: ORA-22973: size of object identifier exceeds maximum size allowed
   Error at line 746: ORA-22973: size of object identifier exceeds maximum size allowed


原因：
NLS_LENGTH_SEMANTICS parameter was set to CHAR

show parameter nls_length_semantics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      CHAR
This issue is described in below bug:

BUG:22540778 - ORA-22973 WITH NLS_LENGTH_SEMANTICS=CHAR WHEN CREATING VIEW


处理方法：
Set NLS_LENGTH_SEMANTICS parameter to BYTE as shown below:

alter system set nls_length_semantics="BYTE";

show parameter nls_length_semantics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      BYTE

Rerun the datapatch -verbose

For the Patch rollback failure,Determine the Script being called & edit as below-


INSTALL_FILE
--------------------------------------------------------------------------------
?/sqlpatch/25296876/21134475/rollback_files/rdbms/admin/catmacc.sql

Please do the following -
1. Backup the following file in Oracle Home - $ORACLE_HOME/sqlpatch/25296876/21134475/rollback_files/rdbms/admin/catmacc.sql
2. Edit the below lines as described below in $ORACLE_HOME/sqlpatch/25296876/21134475/rollback_files/rdbms/admin/catmacc.sql

create or replace force view dvsys.ku$_dv_realm_member_view
      of ku$_dv_realm_member_t
with object identifier (object_name, name) as
select '0','0',
         rlmt.name,
         rlmo.owner,
         SUBSTR(rlmo.object_name,1,90),                                                                                    <<<<<<<<<<<<Edit the value to 90
         rlmo.object_type
from    dvsys.realm$        rlm,
         dvsys.realm_t$      rlmt,
         dvsys.dv$realm_object rlmo
where   rlm.id# = rlmt.id#
   and   rlmo.realm_id# = rlm.id#
   and   rlm.id# > 5000
   and   (SYS_CONTEXT('USERENV','CURRENT_USERID') = 1279990
          or exists ( select 1
                        from sys.session_roles
                       where role='DV_OWNER' ))
/

create or replace force view dvsys.ku$_dv_rule_set_member_view
      of dvsys.ku$_dv_rule_set_member_t
with object identifier (rule_set_name,rule_name) as
select '0','0',
         SUBSTR(rulst.name,1,90),                                                                                    <<<<<<<<<<<<Edit the value to 90
         rult.name,
         rsr.rule_order,
         rsr.enabled
from    dvsys.rule_set_rule$          rsr,
         dvsys.rule_set$               ruls,
         dvsys.rule_set_t$             rulst,
         dvsys.rule$                   rul,
         dvsys.rule_t$                 rult
where   ruls.id# = rsr.rule_set_id#
   and   ruls.id# = rulst.id#
   and    rul.id# = rsr.rule_id#
   and    rul.id# = rult.id#
   and   ruls.id# >= 5000
   and   (SYS_CONTEXT('USERENV','CURRENT_USERID') = 1279990
          or exists ( select 1
                        from sys.session_roles
                       where role='DV_OWNER' ))
/
3. Save & run datapatch
4. If successful,Revert back to the old value.