Oracle DBMS_SQLPATCH
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
Purpose Undocumented support utility supporting SQL patching.
AUTHID DEFINER
Constants
Name Data Type Value
build_header VARCHAR2(200) '$Header: rdbms/admin/dbmssqlpatch.sql /st_rdbms_18.0/1 2017/11/28 09:52:41 surman Exp $';
Cursors CURSOR all_patches_cursor IS
SELECT *
FROM (SELECT dba_registry_sqlpatch.*, rowid registry_rowid,
RANK() OVER (PARTITION BY patch_id, patch_uid
ORDER BY install_id DESC, action_time DESC) r
FROM dba_registry_sqlpatch)
WHERE r = 1
ORDER BY patch_id, patch_uid;

-- Returns the most recent entry for all interim patches
CURSOR all_interims_cursor IS
SELECT *
FROM (SELECT dba_registry_sqlpatch.*, rowid registry_rowid,
RANK() OVER (PARTITION BY patch_id, patch_uid
ORDER BY install_id DESC, action_time DESC) r
FROM dba_registry_sqlpatch
WHERE patch_type = 'INTERIM')
WHERE r = 1
ORDER BY patch_id, patch_uid;

-- Returns the most recent release update entry, which is the
-- current RU installed. This could be any flavor of release update patch,
-- i.e. an RU or RUR or CU. Note that the most recent entry may not have
-- been successful.
CURSOR current_ru_cursor IS
SELECT *
FROM (SELECT dba_registry_sqlpatch.*, rowid registry_rowid,
RANK() OVER (ORDER BY install_id DESC, action_time DESC) r
FROM dba_registry_sqlpatch
WHERE patch_type != 'INTERIM')
WHERE r = 1;

-- Returns the most recent successful release update entry. There may
-- have been subsequent unsuccessful release update install attempts.
CURSOR last_successful_ru_cursor IS
SELECT *
FROM (SELECT dba_registry_sqlpatch.*, rowid registry_rowid,
RANK() OVER (ORDER BY install_id DESC, action_time DESC) r
FROM dba_registry_sqlpatch
WHERE patch_type != 'INTERIM'
AND status = 'SUCCESS')
WHERE r = 1;
Data Types TYPE registry_record IS RECORD (
install_id NUMBER,
patch_id NUMBER,
patch_uid NUMBER,
patch_type VARCHAR2(10),
flags VARCHAR2(10),
action VARCHAR2(15),
status VARCHAR2(25),
action_time TIMESTAMP,
description VARCHAR2(100),
source_version VARCHAR2(15),
source_build_description VARCHAR2(80),
source_build_timestamp TIMESTAMP,
target_version VARCHAR2(15),
target_build_description VARCHAR2(80),
target_build_timestamp TIMESTAMP,
registry_rowid VARCHAR2(25));

TYPE registry_table IS TABLE of registry_record;
Dependencies
DBA_REGISTRY_SQLPATCH DBMS_REGISTRY XMLTYPE
DBMS_OUTPUT DBMS_STANDARD XQSEQUENCE
DBMS_QOPATCH V$INSTANCE  
Documented No
First Available 12.1.0.2
Security Model Owned by SYS with no privileges granted.
Source {ORACLE_HOME}/rdbms/admin/dbmssqlpatch.sql
see also: catdwgrd.sql
Subprograms
 
ALL_PATCHES (new 18.1)
Returns the most recent entry for all patches in the registry dbms_sqlpatch.all_patches RETURN registry_table PIPELINED;
SELECT * FROM TABLE(dbms_sqlpatch.all_patches)
 
BODY_BUILD_HEADER (new 18.1)
Returns the header dbms_sqlpatch.body_build_header RETURN VARCHAR2;
SELECT dbms_sqlpatch.body_build_header
FROM dual;
 
CLEAR_STATE
Removes all saved state from the dbms_sqlptach_state table (and what a poorly named table if ever there was one. dbms_sqlpatch.clear_state;
SQL> desc dbms_sqlpatch_state
Name                          Null?    Type
----------------------------- ----- --------------------
ACTIVE                              CHAR(1)
S_CURRENT_PATCH_ID                  NUMBER
S_CURRENT_PATCH_UID                 NUMBER
S_CURRENT_PATCH_VERSION             VARCHAR2(20)
S_CURRENT_PATCH_FLAGS               VARCHAR2(10)
S_CURRENT_PATCH_ACTION              VARCHAR2(15)
S_CURRENT_PATCH_STATUS              VARCHAR2(15)
S_CURRENT_PATCH_DESCRIPTION         VARCHAR2(100)
S_CURRENT_PATCH_BUNDLE_SERIES       VARCHAR2(30)
S_CURRENT_PATCH_BUNDLE_ID           NUMBER
S_CURRENT_REGISTRY_ROWID            ROWID
S_BUNDLEDATA                        XMLTYPE
S_NOTHING_SQL                       VARCHAR2(30)
S_INSTALLED_BUNDLE                  NUMBER
S_INSTALLING_BUNDLE                 NUMBER
S_DATABASE_VERSION                  VARCHAR2(20)
S_DEBUG                             CHAR(1)
S_FORCE                             CHAR(1)
S_ORACLE_HOME                       VARCHAR2(500)
S_INIT_COMPLETE                     CHAR(1)
S_CACHED_LSINVENTORY                XMLTYPE
S_CURRENT_PATCH_DESCRIPTOR          XMLTYPE
S_SUPERSEDING_BUNDLE                CHAR(1)
S_SESSION_INSTALL_ID                NUMBER
S_EXISTING_BUNDLEDATA               XMLTYPE
S_EXISTING_BUNDLE_SERIES            VARCHAR2(30)
S_EXISTING_BUNDLE_ID                NUMBER
S_APPLICATION_PATCH                 CHAR(1)
S_PATCH_DIRECTORY_ZIP               BLOB

exec dbms_sqlpatch.clear_state;
 
CURRENT_RU_VERSION (new 18.1)
Returns the current RU installed dbms_sqlpatch.current_ru_version RETURN registry_table PIPELINED;
SELECT * FROM TABLE(dbms_sqlpatch.current_ru_version);
 
EVENT_VALUE (new 18.1)
Returns the value of the specified event dbms_sqlpatch.event_value(p_event IN NUMBER) RETURN NUMBER;
SELECT dbms_sqlpatch.event_value
FROM dual;
 
GET_OPATCH_LSINVENTORY
Wrapper around queryable inventory's get_opatch_lsinventory function. Caches the result and returns the full inventory. dbms_sqlpatch.get_opatch_lsinventory RETURN XMLTYPE;
SQL> SELECT dbms_sqlpatch.get_opatch_lsinventory
  2  FROM dual;

GET_OPATCH_LSINVENTORY
---------------------------------------------------
<?xml version="1.0" encoding="WINDOWS-1252" standalone='yes'?>
<InventoryInstance>
 <oracleHome>
    <UId>OracleHome-19b5847a-d5fa-442b-b165-b2442ae0c09b</UId>
    <targetTypeId>oracle_home</targetTypeId>
    <inventoryLocation>C:\Program Files\Oracle\Inventory</inventoryLocation>
    <isShared>false</isShared>
    <patchingModel>oneoff</patchingModel>
    <path>C:\app2\oracle\product\12.2.0\dbhome_1</path>
  </oracleHome>
  <osPlatform id="233">
    <UId>FlexibleDataType-232ac590-9e6e-4530-96c5-ffb109fac604</UId>
    <version>Microsoft Windows (64-bit AMD)
    </version>
  </osPlatform>
  <patches/>
  <components>
    <component xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="OUIComponent" id="oracle.server" name="Oracle Database 12c">
    <UId>OUIComponent-1e232848-b465-4235-a59d-a2ea978d95e8</UId>
    <description>Installs an optional preconfigured starter database, product options, management tools, networking services, utilities, and basic client software for an Oracle Database server. This option also supports Automatic Storage Management database configuration.</description>
    <version>12.2.0.1.0</version>
    <installedLanguage>en</installedLanguage>
    <installedLanguage>fr</installedLanguage>
    <installedLanguage>ar</installedLanguage>
    ...
    <installedLanguage>uk</installedLanguage>
    <installedLanguage>vi</installedLanguage>
  </component>
    <component xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="OUIComponent" id="oracle.rdbms.partitioning" name="Oracle Partitioning">
      <UId>OUIComponent-4a7d7b07-d236-4340-9fc0-71d1ca52eb89</UId>
      <description>provides the functionality to manage a table in smaller components (partitions) for manageability, performance, and availability benefits</description>
      <version>12.2.0.1.0</version>
      <installedLanguage>en</installedLanguage>
      </component>
      <component xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="OUIComponent" id="oracle.options" name="Enterprise Edition Options">
      <UId>OUIComponent-8e975575-cd85-4d34-a651-b58778fd90c7</UId>
      <description>lists the optional features of Oracle Database 12c Enterprise Edition</description>
      <version>12.2.0.1.0</version>
      <installedLanguage>en</installedLanguage>
      </component>
    </components>
  <patchsets/>
  <nodelist>
    <node xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Host">
      <UId>Host-d2d34d9e-09b5-40ca-b9e6-e3ea5f2a0782</UId>
      <targetTypeId>host</targetTypeId>
      <nodeName/>
    </node>
  </nodelist>
</InventoryInstance>
 
INSTALLED_PATCHES (new 18.1)
Returns the current state of the SQL registry dbms_sqlpatch.installed_patches RETURN registry_table PIPELINED;
SELECT * FROM TABLE(dbms_sqlpatch.installed_patches);
 
INSTALL_FILE
For the current patch and mode, determines if the file should be run. Returns the name of the file to be run, which will either be the supplied input file (prefixed with top_directory supplied during initialize) or dbms_registry.nothing_script if the file does not need to be run. dbms_sqlpatch.install_file(sql_file IN VARCHAR2) RETURN VARCHAR2;
TBD
 
LAST_SUCCESSFUL_RU_VERSION (new 18.1)
Returns the most recent successful RU installed dbms_sqlpatch.last_successful_ru_version RETURN registry_table PIPELINED;
SELECT * FROM TABLE(dbms_sqlpatch.last_successful_ru_version);
 
OPATCH_REGISTRY_STATE
Wrapper around queryable inventory's get_pending_activity function returning an XML string representing the state of SQL patches installed in the opatch inventory. dbms_sqlpatch.opatch_registry_state RETURN XMLTYPE;
SQL> SELECT dbms_sqlpatch.opatch_registry_state
  2  FROM dual;

OPATCH_REGISTRY_STATE
-----------------------------------------------
<activityRoot>
</activityRoot>
 
PATCH_FINALIZE
Performs any finalization necessary for the current patch. This includes clearing the package state and updating the SQL registry dbms_sqlpatch.patch_finalize;
exec dbms_sqlpatch.patch_finalize;
 
PATCH_INITIALIZE
Performs any initialization necessary for the given patch, including the initial insert to the SQL registry dbms_sqlpatch.patch_initialize(
p_patch_id  IN NUMBER,
p_patch_uid IN NUMBER,
p_logfile   IN VARCHAR2);
TBD
 
SESSION_INITIALIZE
Performs session initialization before patch_initialize can begin dbms_sqlpatch.session_initialize(
p_force       IN BOOLEAN  := FALSE,
p_debug       IN BOOLEAN  := FALSE,
p_app_mode    IN BOOLEAN  := FALSE,
p_nothing_sql IN VARCHAR2 := NULL,
p_attempt     IN NUMBER   := NULL);
exec dbms_sqlpatch.session_initialize(TRUE);
 
SET_FILE_METADATA (new 18.1)
Sets file metadata before patch_initialize can begin dbms_sqlpatch.set_file_metadata(
p_patch_id     IN NUMBER,
p_patch_uid    IN NUMBER,
p_install_file IN VARCHAR2,
p_actual_file  IN VARCHAR2);
TBD
 
SET_PATCH_METADATA
Called directly by datapatch prior to patch_initialize dbms_sqlpatch.set_patch_metadata(p_patch IN dba_registry_sqlpatch%ROWTYPE);
TBD
 
SQL_REGISTRY_STATE
Performs session initialization. Must be called before patch_initialize dbms_sqlpatch.sql_registry_state RETURN XMLType;
SELECT dbms_sqlpatch.sql_registry_state
FROM dual;

SQL_REGISTRY_STATE
------------------------------
<sql_registry_state>
<!-- Non bundle patches -->

<!-- Bundle patches -->
</sql_registry_state>
 
UPDATE_PATCH_METADATA (new 18.1)
Updates patch metadata before patch_initialize can begin dbms_sqlpatch.update_patch_metadata(
p_patch_id   IN NUMBER,
p_patch_uid  IN NUMBER,
p_ru_logfile IN VARCHAR2 := NULL,
p_flags      IN VARCHAR2 := NULL);
TBD

Related Topics
Built-in Functions
Built-in Packages
DBMS_INTERNAL_ROLLING
DBMS_QOPATCH
DBMS_ROLLING
What's New In 12cR2
What's New In 18cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved