一套测试环境，操作系统是RedHat Linux，Oracle 11.2.0.4，出现了个很诡异的现象，咋都不能解释。
某张表，有个字段，存在默认值，并且设置了NOT NULL约束，例如，NEED_PO VARCHAR2(1) default 'N' not null,
尝试删除该字段，提示ORA-12899，
SQL> alter table t drop column need_po;
alter table t drop column need_po
*
ERROR at line 1:
ORA-12899: value too large for column "A"."T"."NEED_PO"
(actual: 7, maximum: 1)
ORA-12899错误，明显说的是insert或者update列的时候，实际值的长度，大于定义长度，这是种常见错误，但是在DDL中出现，确实不常见的，
[oracle@DB trace]$ oerr ora 12899
12899, 00000, "value too large for column %s (actual: %s, maximum: %s)"
// *Cause: An attempt was made to insert or update a column with a value
// which is too wide for the width of the destination column.
// The name of the column is given, along with the actual width
// of the value, and the maximum allowed width of the column.
// Note that widths are reported in characters if character length
// semantics are in effect for the column, otherwise widths are
// reported in bytes.
// *Action: Examine the SQL statement for correctness. Check source
// and destination column data types.
// Either make the destination column wider, or use a subset
// of the source column (i.e. use substring).
当不知道一条SQL究竟做什么时，最直接的方式，就是通过10046，暴露出他的每步操作，从10046的trace可以看到，drop column的操作过程中，会执行这条update，向VARCHAR2(1)的NEED_PO字段，插入值“NEED_PO"，很显然，7个长度的字符，插入1个长度的列，肯定会抛出异常，
PARSING IN CURSOR #140623645148936 len=56 dep=2 uid=32 oct=6 lid=32 tim=1576158576911826 hv=4140637593 ad='77263f20' sqlid='dkf66p3vcu7ct'
update "A"."T" set "NEED_PO" = "NEED_PO"
END OF STMT
PARSE #140623645148936:c=56991,e=57432,p=9,cr=321,cu=16,mis=1,r=0,dep=2,og=1,plh=1074080344,tim=1576158576911825
EXEC #140623645148936:c=0,e=313,p=0,cr=3,cu=1,mis=0,r=0,dep=2,og=1,plh=1074080344,tim=1576158576912215
ERROR #140623645148936:err=12899 tim=1576158576912242
err=12899，正是ORA-12899，因此alter table抛的ORA-12899，其实就是这条update导致的，单就这个错误，抛的正确，毕竟update列的值长度有错，但是，为什么执行删除列的操作，会执行这个update？
由于我的MOS账号过期了，拜托一位朋友，搜了MOS，发现有个Bug 17325413和这个现象非常相近，标题是"删除一个带DEFAULT默认值并且设置了NOT NULL的列时，因将待删除列的数据写入磁盘导致执行失败"，
直译过来，确实有些绕口，但是结合10046，就可以猜出些许，我执行删除列的操作，他会跟着执行这个操作，用待删除列名更新待删除列，这里因为NEED_PO列定义小于列名长度，所以提示错误，导致删除列的操作执行失败，有趣的推测是，若NEED_PO列定义长度是7，是不是就能执行成功？从现象上来看，列存在了默认值，设置非空约束，执行删除列操作，提示ORA-12899，都和这个bug描述对应上了，
update "A"."T" set "NEED_PO" = "NEED_PO"bug在通用平台的11.2.0.4上得到了确认，
从描述看，这种列删除操作之所以失败的根源，是因为他产生了不能使用的回滚信息，导致回滚失败。复现场景，他说如果使用“add column optimization”的方式将非空列写入磁盘，就可能产生这个bug，并且提示这个操作能导致块损坏，进而报出ORA-00600，或者提示ORA-12899，
“add column optimization”指的是11g对新增列的一种优化改进，当为表增加一个含默认值且设置了非空约束的列时，他是不会实际更新这张表的，而是在数据字典中增加该列信息，当用到这张表的该字段，就会从数据字典中读取，一方面满足读取字段的需求，另一方面让增加字段的操作很快地执行，而在11g以前的版本，这种增加列操作，是需要实际更新这张表的字段，如果表的数据量非常大，则增加字段的操作会执行很久，关于新增字段，可以参考之前做的一些实验，
《alter table新增字段操作究竟有何影响？(上篇)》
《alter table新增字段操作究竟有何影响？(下篇)》
但是，此时，我尝试创建带NOT NULL和default默认值列的表，删除列，可以正常执行，说明确实，是“可能产生这个bug”，不是一定产生，
SQL> alter table a add a varchar2(1) default 'X' not null;
Table altered.
SQL> desc a
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
A NOT NULL VARCHAR2(1)
SQL> insert into a values(1, 'a');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table a drop column a;
Table altered.对这个的问题，MOS给出的workaround是表不得不重建，或者打个PSU，
PSU(Patch Set Update)简单讲就是多个补丁的集合，他减轻了小版本周期长而带来的不能及时更新的影响，解决了多个个别补丁冲突和相互影响的问题,同时减轻DBA安装补丁的负担，减少补丁安装次数。
咱就熟悉下打PSU的过程，首先11.2.0.4要打这个PSU，要求opatch当前版本，要高于11.2.0.3.5，看下实际情况，显示opatch当前版本是11.2.0.4，
[oracle@DB OPatch]$ ./opatch version
OPatch Version: 11.2.0.3.4
OPatch succeeded.升级opatch是很简单的，只需要下载对应的版本，因为他存在32和64位两个安装包(p6880880_112000_LINUX.zip)，解压覆盖到$ORACLE_HOME中，替换现在的OPatch，如果有必要，可备份下原始OPatch，
[oracle@DB oracle]$ unzip p6880880_112000_Linux-x86-64.zip
[oracle@DB 11.2.0.4]$ mv OPatch OPatch.bak
[oracle@DB oracle]$ cp -R OPatch $ORACLE_HOME此时，opatch的版本是11.2.0.3.6，
[oracle@DB OPatch]$ opatch version
OPatch Version: 11.2.0.3.6
OPatch succeeded.查看当前补丁，发现是空的，没任何的补丁，
[oracle@DB OPatch]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation. All rights reserved.
Oracle Home : /DATA/oracle/u01/app/oracle/product/11.2.0.4
Central Inventory : /home/oracle/oraInventory
from : /DATA/oracle/u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.6
OUI version : 11.2.0.4.0
Log file location : /DATA/oracle/u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2019-12-12_21-09-36PM_1.log
Lsinventory Output file location : /DATA/oracle/u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/lsinv/lsinventory2019-12-12_21-09-36PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 11g 11.2.0.4.0
Oracle Database 11g Examples 11.2.0.4.0
There are 2 product(s) installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.17325413这个bug在11.2.0.4没单独的补丁，只可以使用PSU，文章给出的PSU是这个p18031668_112040_Linux-x86-64.zip，从说明看，包含了这个问题的解决，
参考PSU的README.html，
1.Determine whether any currently installed one-off patches conflict with the PSU patch as follows:
unzip p18031668_112040_.zip
cd 18031668
opatch prereq CheckConflictAgainstOHWithDetail -ph ./2.The report will indicate the patches that conflict with PSU 18031668 and the patches for which PSU 18031668 is a superset.
Note that Oracle proactively provides PSU 11.2.0.4.2 one-off patches for common conflicts.3.Use My Oracle Support Document 1321267.1 Database Patch conflict resolution to determine, for each conflicting patch, whether a conflict resolution patch is already available, and if you need to request a new conflict resolution patch or if the conflict may be ignored.
4.When all the one-off patches that you have requested are available at My Oracle Support, proceed with Section 3.2, "Patch Installation Instructions".
首先执行预检，没冲突，
[oracle@DB 18031668]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /DATA/oracle/u01/app/oracle/product/11.2.0.4
Central Inventory : /home/oracle/oraInventory
from : /DATA/oracle/u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.6
OUI version : 11.2.0.4.0
Log file location : /DATA/oracle/u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2019-12-12_21-17-07PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
在打PSU前，需要停下监听，并关闭数据库，记得要退出sqlplus，
[oracle@DB ~]$ lsnrctl stop
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
开始打PSU，
[oracle@DB 18031668]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation. All rights reserved.
Oracle Home : /DATA/oracle/u01/app/oracle/product/11.2.0.4
Central Inventory : /home/oracle/oraInventory
from : /DATA/oracle/u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.6
OUI version : 11.2.0.4.0
Log file location : /DATA/oracle/u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2019-12-12_21-18-01PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 17478514 18031668
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/DATA/oracle/u01/app/oracle/product/11.2.0.4')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '17478514' to OH '/DATA/oracle/u01/app/oracle/product/11.2.0.4'
Patching component oracle.rdbms, 11.2.0.4.0...
Patching component oracle.rdbms.rsf, 11.2.0.4.0...
Patching component oracle.sdo, 11.2.0.4.0...
Patching component oracle.sysman.agent, 10.2.0.4.5...
Patching component oracle.xdk, 11.2.0.4.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...
Patching component oracle.sdo.locator, 11.2.0.4.0...
Patching component oracle.nlsrtl.rsf, 11.2.0.4.0...
Patching component oracle.xdk.rsf, 11.2.0.4.0...
Patching component oracle.rdbms.rman, 11.2.0.4.0...
Verifying the update...
Applying sub-patch '18031668' to OH '/DATA/oracle/u01/app/oracle/product/11.2.0.4'
Patching component oracle.rdbms, 11.2.0.4.0...
Patching component oracle.rdbms.rsf, 11.2.0.4.0...
Patching component oracle.ldap.rsf, 11.2.0.4.0...
Patching component oracle.rdbms.crs, 11.2.0.4.0...
Patching component oracle.precomp.common, 11.2.0.4.0...
Patching component oracle.ldap.rsf.ic, 11.2.0.4.0...
Patching component oracle.rdbms.deconfig, 11.2.0.4.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...
Patching component oracle.rdbms.rman, 11.2.0.4.0...
Verifying the update...
OPatch found the word "warning" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ins_emagent.mk:113: warning: overriding commands for target `nmosudo'
ins_emagent.mk:52: warning: ignoring old commands for target `nmosudo'
/DATA/oracle/u01/app/oracle/product/11.2.0.4/sysman/lib/ins_emagent.mk:113: warning: overriding commands for target `nmosudo'
/DATA/oracle/u01/app/oracle/product/11.2.0.4/sysman/lib/ins_emagent.mk:52: warning: ignoring old commands for target `nmosudo'
Composite patch 18031668 successfully applied.
OPatch Session completed with warnings.
Log file location: /DATA/oracle/u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2019-12-12_21-18-01PM_1.log
OPatch completed with warnings.显示已经打上了这个PSU，
[oracle@DB 18031668]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation. All rights reserved.
Oracle Home : /DATA/oracle/u01/app/oracle/product/11.2.0.4
Central Inventory : /home/oracle/oraInventory
from : /DATA/oracle/u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.6
OUI version : 11.2.0.4.0
Log file location : /DATA/oracle/u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2019-12-12_21-29-17PM_1.log
Lsinventory Output file location : /DATA/oracle/u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/lsinv/lsinventory2019-12-12_21-29-17PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 11g 11.2.0.4.0
Oracle Database 11g Examples 11.2.0.4.0
There are 2 product(s) installed in this Oracle Home.
Interim patches (1) :
Patch 18031668 : applied on Thu Dec 12 21:19:00 CST 2019
Unique Patch ID: 17255543
Patch description: "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Created on 20 Feb 2014, 05:15:58 hrs PST8PDT
Sub-patch 17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
Bugs fixed:
17288409, 16399083, 17205719, 17811429, 10136473, 16472716, 17614227
17050888, 17040764, 17865671, 17325413, 14010183, 17726838, 13364795
17389192, 17612828, 17080436, 16613964, 17284817, 17441661, 17761775
17721717, 16721594, 17390431, 18203837, 17551709, 17344412, 16043574
17446237, 16863422, 18139690, 17071721, 17501491, 17610798, 17239687
17752121, 17468141, 17602269, 18203835, 17375354, 17313525, 17811456
16731148, 14133975, 18203838, 16956380, 17385178, 17235750, 13866822
17394950, 17478514, 17027426, 12905058, 14338435, 16450169, 13944971
18094246, 16929165, 16785708, 17265217, 17465741, 16220077, 16180763
16069901, 17546973, 16285691, 17323222, 18180390, 17088068, 16875449
17016369, 17443671, 16228604, 17811438, 17811447, 16837842, 18031668
16912439, 17332800, 17393683, 17622427, 17545847, 17186905, 16943711
16850630, 17082359, 17346671, 14852021, 17783588, 17437634, 16618694
17341326, 17296856, 17546761, 17716305
--------------------------------------------------------------------------------
OPatch succeeded.
启动数据库，
SQL> startup
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 624952200 bytes
Database Buffers 436207616 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.因为是PSU，需要更新下数据字典，
SQL> @catbundle.sql psu apply
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/DATA/oracle/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_DEP07_GENERATE_2019Dec12_21_32_46.log
Apply script: /DATA/oracle/u01/app/oracle/product/11.2.0.4/rdbms/admin/catbundle_PSU_DEP07_APPLY.sql
Rollback script: /DATA/oracle/u01/app/oracle/product/11.2.0.4/rdbms/admin/catbundle_PSU_DEP07_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/DATA/oracle/u01/app/oracle/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
SQL> PROMPT Processing Oracle Database Packages and Types...
Processing Oracle Database Packages and Types...
SQL> ALTER SESSION SET current_schema = sys;
Session altered.
SQL> @?/rdbms/admin/prvtdadv.plb
SQL> CREATE OR REPLACE PACKAGE BODY dbms_advisor wrapped
2 a000000
3 1
4 abcd
5 abcd
6 abcd
7 abcd
8 abcd
9 abcd
10 abcd
11 abcd
12 abcd
13 abcd
14 abcd
15 abcd
16 abcd
17 abcd
18 abcd
19 b
20 104fe 2839
21 mgnPYoOWABPIrz+XwtchwZ0ytz0wg81M9iAFV0UiT3PV49ZMtCKGb7wh3toipGenfaK2THrx
22 biH46YVhWDFwBclhPp9LZ6rk7i7ToczToT6f2kmvfmFKQkIZ1fqclvqQhodXFy9+4LurNLX4
23 xgfki3Oduxy5JNDkjl/OT0zTmFAFSS9T3F0VynJu0uFZpbt6wObnfdAsNwxJUnKQka2ka9A+
24 kg03Fz8hFZpFwVDthb/0qljigF2WmF1l2aS7Ja6GXa2xwD2liY1ZcGGuUSqSeF96HNsjGpL7
25 aCgoIjfcOYgwPJbQDxQ4GyFqc4WR/TzScPzssUbu0WePN1IuJNeUjTInQcbqrQFYsOIxbF1F
...
Package body created.
SQL> show errors
No errors.
SQL> /
Package body created.
SQL> PROMPT Skipping Spatial because it is not installed or versions mismatch...
Skipping Spatial because it is not installed or versions mismatch...
SQL> ALTER SESSION SET current_schema = SYS;
Session altered.
SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '11.2.0.4',
9 2,
10 'PSU',
11 'PSU 11.2.0.4.2');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/DATA/oracle/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_DEP07_APPLY_2019Dec12_21_32_48.log
从日志中可看到，已经完成PSU安装，
Composite patch 18031668 successfully applied.
搜索我要补丁的17325413，确认已经打上，
[oracle@DB admin]$ opatch lsinventory | grep 17325413
17050888, 17040764, 17865671, 17325413, 14010183, 17726838, 13364795
再次执行删除列的操作，此时就可以正常执行了，
SQL> alter table t_dep_atst_task drop column need_po;
Table altered.
这个问题，从最初的一个DDL，报了ORA-12899，一个本应该是DML语句报的错误，通过10046，看到做了update更新列的操作，按常规的思路，不太可能，这和MOS上17325413的bug描述的现象，从版本号、到错误提示，基本一致，按照文档描述，打了PSU，确实是解决了，验证了这个问题，就是这个bug，另外，就是熟悉了下打PSU的整个过程。
这个案例告诉我，在Oracle的世界中，没什么事儿是不可能的，要是有，就看看10046，究竟他做了什么，总会得到些线索，层层剥茧，找到他的奥秘，这就是Oracle的乐趣了。
1342
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
