DBMS_SCN 的Package是在 11.2.0.3.9 中引入，在Opatch应用补丁的过程中，可以观察到这个执行过程。
整个Package 由两个源码文件组成，分别是：dbmsscnc.sql 和 prvtscnc.plb 。后者是加密Wrap过的代码。
以下引用了补丁修正安装后的代码执行过程，作为参考：
$ opatch napply -oh $ORACLE_HOME -local /u01/app/oracle/patch/17540582
Oracle 中间补丁程序安装程序版本 11.2.0.3.5
版权所有 (c) 2013, Oracle Corporation。保留所有权利。
Oracle Home : /u01/app/oracle/product/11.2.0.3/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.3/db_1/oraInst.loc
OPatch version : 11.2.0.3.5
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2015-01-10_11-12-31上午_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 17540582
是否继续? [y|n]
y
User Responded with: Y
All checks passed.
请关闭本地系统上在此 ORACLE_HOME 之外运行的 Oracle 实例。
(Oracle 主目录 = '/u01/app/oracle/product/11.2.0.3/db_1')
本地系统是否已准备打补丁? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '17540582' to OH '/u01/app/oracle/product/11.2.0.3/db_1'
ApplySession: Oracle 主目录中不存在可选组件 [ oracle.precomp.lang, 11.2.0.3.0 ] , 或找到更高版本。
正在为组件 oracle.rdbms, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.rsf, 11.2.0.3.0 打补丁...
正在为组件 oracle.sdo, 11.2.0.3.0 打补丁...
正在为组件 oracle.ldap.rsf, 11.2.0.3.0 打补丁...
正在为组件 oracle.precomp.common, 11.2.0.3.0 打补丁...
正在为组件 oracle.ordim.client, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.util, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.dbscripts, 11.2.0.3.0 打补丁...
正在为组件 oracle.sdo.locator, 11.2.0.3.0 打补丁...
正在为组件 oracle.rdbms.rman, 11.2.0.3.0 打补丁...
正在为组件 oracle.ordim.jai, 11.2.0.3.0 打补丁...
Verifying the update...
Composite patch 17540582 successfully applie
查看更新的结果
$ opatch lspatches
17540582;Database Patch Set Update : 11.2.0.3.9 (17540582)
查看更新补丁的内容
$ opatch lsinventory
Oracle 中间补丁程序安装程序版本 11.2.0.3.5
版权所有 (c) 2013, Oracle Corporation。保留所有权利。
Oracle Home : /u01/app/oracle/product/11.2.0.3/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.3/db_1/oraInst.loc
OPatch version : 11.2.0.3.5
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2015-01-10_11-12-31上午_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/lsinv/lsinventory2015-01-10_11-12-31上午_1.txt
--------------------------------------------------------------------------------
已安装的顶级产品 (1):
Oracle Database 11g 11.2.0.3.0
此 Oracle 主目录中已安装 1 个产品。
中间补丁程序 (1) :
Patch 17540582 : applied on Thu Feb 20 11:33:06 CST 2014
Unique Patch ID: 16985511
Patch description: "Database Patch Set Update : 11.2.0.3.9 (17540582)"
Created on 7 Jan 2014, 03:01:22 hrs PST8PDT
Sub-patch 16902043; "Database Patch Set Update : 11.2.0.3.8 (16902043)"
Sub-patch 16619892; "Database Patch Set Update : 11.2.0.3.7 (16619892)"
Sub-patch 16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Sub-patch 14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch 14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch 13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch 13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch 13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
Bugs fixed:
13593999, 10350832, 14138130, 12919564, 13561951, 14198511, 13588248
13080778, 13804294, 16710324, 12873183, 14472647, 12880299, 13369579
14409183, 13492735, 12857027, 13496884, 14263036, 14263073, 13015379
16038929, 17748833, 16563678, 13732226, 13866822, 13742434, 13944971
12950644, 17748831, 12899768, 13063120, 13958038, 14613900, 13972394
11877623, 17088068, 13072654, 12395918, 13814739, 17343514, 13649031
13981051, 12797765, 17333200, 12923168, 16761566, 16279401, 13384182
13466801, 15996344, 14207163, 13724193, 13642044, 11063191, 13945708
12797420, 12865902, 15869211, 13041324, 14003090, 16314468, 16019955
11708510, 14637368, 13026410, 13737746, 13742438, 15841373, 16347904
15910002, 16362358, 14398795, 13579992, 16344871, 10400244, 14275605
13742436, 9858539, 14841812, 16338983, 9703627, 13483354, 14207317
14393728, 12764337, 16902043, 14459552, 14191508, 12964067, 12780983
12583611, 14383007, 14546575, 15862016, 13476583, 13489024, 17748830
14088346, 13448206, 16314466, 13419660, 14110275, 13430938, 13467683
14548763, 12834027, 13632809, 13377816, 13036331, 14727310, 16175381
13584130, 12829021, 15862019, 12794305, 14546673, 12791981, 13787482
13503598, 10133521, 12744759, 13399435, 13553883, 14023636, 14762511
9095696, 14343501, 13860201, 13257247, 14176879, 16014985, 12312133
14480675, 16306019, 13559697, 9706792, 12974860, 12940620, 13098318
13773133, 15883525, 16794244, 13340388, 13366202, 13528551, 12894807
12747437, 13454210, 12748240, 13385346, 15987992, 13923995, 13582702
14571027, 12784406, 13907462, 13493847, 13857111, 13035804, 16710363
13544396, 14128555, 8547978, 14226599, 17478415, 17333197, 9397635
14007968, 12925089, 12693626, 14189694, 12815057, 17761775, 16721594
13332439, 14038787, 11071989, 14207902, 14062796, 12913474, 14390252
16314470, 13370330, 14062794, 13358781, 17333202, 12960925, 9659614
14546638, 13699124, 13936424, 9797851, 14301592, 16794240, 13338048
12938841, 12620823, 12656535, 12678920, 14488943, 16850197, 14791477
14062792, 13807411, 16794238, 15862022, 12594032, 13250244, 9761357
12612118, 14053457, 13527323, 10625145, 15862020, 13910420, 12780098
13696216, 10263668, 14841558, 16794242, 16944698, 15862023, 16056266
13834065, 14351566, 13723052, 13011409, 14063280, 13566938, 13737888
13624984, 16024441, 17333199, 13914613, 17540582, 14258925, 14222403
14755945, 13645875, 12571991, 14664355, 12998795, 13719081, 14469008
14188650, 17019974, 13742433, 16368108, 16314469, 12905058, 6690853
16212405, 12849688, 13742435, 13464002, 13534412, 12879027, 12585543
13790109, 12535346, 16382448, 12588744, 13916549, 13786142, 12847466
13855490, 13551402, 12582664, 14262913, 17332800, 14695377, 12912137
13612575, 13484963, 14163397, 17437634, 13772618, 16694777, 13070939
14369664, 12391034, 13605839, 16314467, 16279211, 12976376, 12755231
13680405, 14589750, 13742437, 14318397, 11868640, 14644185, 13326736
13596521, 13001379, 12898558, 17752121, 13099577, 9873405, 16372203
16344758, 11715084, 16231699, 9547706, 14040433, 12662040, 12617123
17748832, 16530565, 12845115, 16844086, 17748834, 13354082, 13397104
13913630, 16462834, 12983611, 13550185, 13810393, 14121009, 13065099
11840910, 13903046, 15862017, 13572659, 16294378, 13718279, 13657605
14480676, 13632717, 14668670, 14063281, 13420224, 13812031, 16299830
12646784, 14512189, 12755116, 13616375, 17230530, 14035825, 13427062
12861463, 13092220, 15862021, 13043012, 16619892, 13685544, 15862018
13499128, 13561750, 12718090, 13848402, 13725395, 12401111, 12796518
13362079, 12917230, 13042639, 13923374, 14220725, 12621588, 13524899
14751895, 14480674, 13916709, 14076523, 15905421, 12731940, 13343438
14205448, 17748835, 14127231, 17082364, 15853081, 14273397, 16844448
14467061, 12971775, 16864562, 14497307, 12748538, 10242202, 14230270
16382353, 13686047, 14095982, 17333203, 13591624, 14523004, 13440516
16794241, 14062795, 13035360, 13040943, 13843646, 16794243, 14841409
13059165, 14062797, 12959852, 12345082, 16703112, 13890080, 17333198
16450169, 12658411, 13780035, 14062793, 13038684, 16742095, 13742464
14052474, 13060271, 13911821, 13457582, 7509451, 13791364, 12821418
13502183, 13705338, 16794239, 15862024, 13554409, 13645917, 13103913, 12772404
--------------------------------------------------------------------------------
OPatch succeeded.
数据库启动，并加载修改SQL Files到数据库
enmo@oracle> @?/rdbms/admin/catbundle.sql psu apply
PL/SQL procedure successfully completed.
Function created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ENMO_GENERATE_2014Feb20_11_42_25.log
Apply script: /u01/app/oracle/product/11.2.0.3/db_1/rdbms/admin/catbundle_PSU_APPLY.sql
Rollback script: /u01/app/oracle/product/11.2.0.3/db_1/rdbms/admin/catbundle_PSU_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...
enmo@oracle> COLUMN spool_file NEW_VALUE spool_file NOPRINT
enmo@oracle> SELECT '/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;
enmo@oracle> SPOOL &spool_file
enmo@oracle> exec sys.dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
enmo@oracle> PROMPT Skipping EM Repository because it is not installed or versions mismatch...
Skipping EM Repository because it is not installed or versions mismatch...
enmo@oracle> PROMPT Processing Oracle Database Packages and Types...
Processing Oracle Database Packages and Types...
enmo@oracle> ALTER SESSION SET current_schema = sys;
Session altered.
enmo@oracle> @?/rdbms/admin/dbmsscnc.sql
enmo@oracle> Rem
enmo@oracle> Rem $Header: rdbms/admin/dbmsscnc.sql /st_rdbms_11.2.0.3.0dbpsu/1 2013/11/06 04:17:31 mtiwary Exp $
enmo@oracle> Rem
enmo@oracle> Rem dbmsscn.sql
enmo@oracle> Rem
enmo@oracle> Rem Copyright (c) 2012, 2013, Oracle and/or its affiliates.
enmo@oracle> Rem All rights reserved.
enmo@oracle> Rem
enmo@oracle> Rem NAME
enmo@oracle> Rem dbmsscnc.sql - dbms_scn package definition
enmo@oracle> Rem
enmo@oracle> Rem DESCRIPTION
enmo@oracle> Rem
enmo@oracle> Rem
enmo@oracle> Rem NOTES
enmo@oracle> Rem
enmo@oracle> Rem
enmo@oracle> Rem MODIFIED (MM/DD/YY)
enmo@oracle> Rem mtiwary 05/26/12 - Declarations and definitions related to DBMS_SCN
enmo@oracle> Rem package.
enmo@oracle> Rem mtiwary 05/26/12 - Created
enmo@oracle> Rem
enmo@oracle>
enmo@oracle> Rem
enmo@oracle> Rem BEGIN SQL_FILE_METADATA
enmo@oracle> Rem SQL_SOURCE_FILE: rdbms/admin/dbmsscn.sql
enmo@oracle> Rem SQL_SHIPPED_FILE:
enmo@oracle> Rem SQL_PHASE:
enmo@oracle> Rem SQL_STARTUP_MODE: NORMAL
enmo@oracle> Rem SQL_IGNORABLE_ERRORS: NONE
enmo@oracle> Rem SQL_CALLING_FILE:
enmo@oracle> Rem END SQL_FILE_METADATA
enmo@oracle>
enmo@oracle> SET ECHO ON
enmo@oracle> SET FEEDBACK 1
enmo@oracle> SET NUMWIDTH 10
enmo@oracle> SET LINESIZE 80
enmo@oracle> SET TRIMSPOOL ON
enmo@oracle> SET TAB OFF
enmo@oracle> SET PAGESIZE 100
enmo@oracle>
enmo@oracle> CREATE OR REPLACE LIBRARY DBMS_SCN_LIB TRUSTED AS STATIC;
2 /
Library created.
enmo@oracle>
enmo@oracle> CREATE OR REPLACE PACKAGE DBMS_SCN AUTHID CURRENT_USER IS
2
3 DBMS_SCN_API_MAJOR_VERSION CONSTANT NUMBER := 1;
4 DBMS_SCN_API_MINOR_VERSION CONSTANT NUMBER := 0;
5
6 PROCEDURE GetCurrentSCNParams(
7 rsl OUT number,
8 headroom_in_scn OUT number,
9 headroom_in_sec OUT number,
10 cur_scn_compat OUT number,
11 max_scn_compat OUT number);
12
13 -- Currently no exceptions are thrown.
14 -- rsl - Reasonable SCN Limit as of 'now'
15 -- headroom_in_scn - Difference between current SCN and RSL
16 -- headroom_in_sec - number of seconds it would take to reach RSL
17 -- assuming a constant SCN consumption rate associated
18 -- with current SCN compatibility level
19 -- cur_scn_compat - current value of SCN compatibility
20 -- max_scn_compat - max value of SCN compatibility this database
21 -- understands
22
23 FUNCTION GetSCNParamsByCompat(
24 compat IN number,
25 rsl OUT number,
26 headroom_in_scn OUT number,
27 headroom_in_sec OUT number
28 ) RETURN boolean;
29
30 -- compat -- SCN compatibility value
31 -- rsl -- Reasonable SCN Limit
32 -- headroom_in_scn -- Difference between current SCN and RSL
33 -- headroom_in_sec -- number of seconds it would take to reach RSL
34 -- assuming a constant SCN consumption rate associated
35 -- with specified database SCN compatibility
36 --
37 -- Returns False if 'compat' parameter value is invalid, and OUT parameters
38 -- are not updated.
39
40 PROCEDURE GetSCNAutoRolloverParams(
41 effective_auto_rollover_ts OUT DATE,
42 target_compat OUT number,
43 is_enabled OUT boolean);
44
45 -- effective_auto_rollover_ts - timestamp at which rollover becomes
46 -- effective
47 -- target_compat - SCN compatibility value this database
48 -- will move to, as a result of
49 -- auto-rollover
50 -- is_enabled - TRUE if auto-rollover feature is
51 -- currently enabled
52
53 PROCEDURE EnableAutoRollover;
54
55 PROCEDURE DisableAutoRollover;
56
57 END DBMS_SCN;
58 /
Package created.
enmo@oracle>
enmo@oracle> @?/rdbms/admin/prvtscnc.plb
enmo@oracle> SET ECHO ON
enmo@oracle> SET FEEDBACK 1
enmo@oracle> SET NUMWIDTH 10
enmo@oracle> SET LINESIZE 80
enmo@oracle> SET TRIMSPOOL ON
enmo@oracle> SET TAB OFF
enmo@oracle> SET PAGESIZE 100
enmo@oracle> CREATE OR REPLACE PACKAGE BODY DBMS_SCN 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 6c0 243
21 QlmAiY1dAl0ShRRHlX+HGNAfF7Mwgw23ACAVfC9A2k7VVhtmMilHXbSA4+y0szHoAcIlGGvF
22 LFznjZK7HsiO4405ad7otP6DvBJPmF/CgKv7vWxPthzol8UbWtg5Rsh0bB1IL1o27IiiL4Pp
23 ghghXIzy7qpN8ZKAqy5GoYTd+NFVjhaAPl79bXMSsYU3kLeYwwq6YrfeYIGtMvJPmD01eYTm
24 6ZHFbXW65+zhiLyd4n6gFjHiFm8ewsIUlps9n1Qmhi8+HDugSGp5JJUj8nWOq0ENurliNrJN
25 hU0xgcfHK5K6QfbtOHA/U80YLHmYL19b0SJ/rClUGJ61NxJXZGyQ5KEL4FaSdiRh+mztwHkD
26 0vUMuhwvNnlpUxmcvWlSy/43x86V3wrQNDQ+u0hWeLus6JG2IndfBYS5uYxgDImhZhepALfL
27 t71Ti3U3O8u0T7YrCu/D3Cr1ZiWOVQsf/xfYVuerG93+lzkruPtiRdV4U5PReE9tBiwb0r+Z
28 zwEKhyQwCZo3l/PypHsCJbpX2E6cQwagpSSNihdqCzJce+R5Ek7PZ6VqrwhVeOL4icI=
29
30 /
Package body created.
enmo@oracle> CREATE OR REPLACE PUBLIC SYNONYM dbms_scn FOR sys.dbms_scn;
Synonym created.
enmo@oracle> /
Synonym created.
enmo@oracle> GRANT EXECUTE ON dbms_scn TO PUBLIC;
Grant succeeded.
enmo@oracle> /
Grant succeeded.
enmo@oracle> PROMPT Skipping Oracle Workspace Manager because it is not installed or versions mismatch...
Skipping Oracle Workspace Manager because it is not installed or versions mismatch...
enmo@oracle> PROMPT Skipping Oracle interMedia because it is not installed or versions mismatch...
Skipping Oracle interMedia because it is not installed or versions mismatch...
enmo@oracle> PROMPT Skipping Spatial because it is not installed or versions mismatch...
Skipping Spatial because it is not installed or versions mismatch...
enmo@oracle> ALTER SESSION SET current_schema = SYS;
Session altered.
enmo@oracle> PROMPT Updating registry...
Updating registry...
enmo@oracle> 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.3',
9 9,
10 'PSU',
11 'PSU 11.2.0.3.9');
1 row created.
enmo@oracle> COMMIT;
Commit complete.复制
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
618次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
609次阅读
2025-04-15 17:24:06
XTTS跨版本迁移升级方案（11g to 19c RAC for Linux）
zwtian
505次阅读
2025-04-08 09:12:48
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
487次阅读
2025-04-20 10:07:02
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
469次阅读
2025-04-22 00:20:37
Oracle 19c RAC更换IP实战，运维必看！
szrsu
446次阅读
2025-04-08 23:57:08
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
441次阅读
2025-04-22 00:13:51
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
435次阅读
2025-04-17 17:02:24
一页概览：Oracle GoldenGate
甲骨文云技术
432次阅读
2025-04-30 12:17:56
火焰图--分析复杂SQL执行计划的利器
听见风的声音
378次阅读
2025-04-17 09:30:30
