Oracle DBMS_SQLPATCH
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Do you remember when mainframes were replaced by client-server? When client-server was replaced by n-tier architecture? The "Cloud" is a distraction ... DevOps is not. Prepare for the future.
Purpose Undocumented support utility supporting SQL patching.
AUTHID DEFINER
Constants
Name Data Type Value
version NUMBER 1
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
 
CLEAR_STATE (new 12.2)
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;
 
GET_OPATCH_LSINVENTORY (new 12.2)
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>
 
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
 
OPATCH_REGISTRY_STATE (new 12.2)
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_flags         IN VARCHAR2,
p_description   IN VARCHAR2,
p_action        IN VARCHAR2,
p_logfile       IN VARCHAR2,
p_bundle_series IN VARCHAR2 DEFAULT NULL);
TBD
 
SESSION_INITIALIZE
Performs session initialization. Must be called before patch_initialize dbms_sqlpatch.session_initialize(
p_oh    IN VARCHAR2,
p_force IN BOOLEAN := FALSE,
p_debug IN BOOLEAN := FALSE);
DECLARE
 xRet XMLTYPE;
BEGIN
  xRet := dbms_sqlpatch.sql_registry_state;
  dbms_sqlpatch.session_initialize(USER);
END;
/
 
SET_PATCH_METADATA (new 12.2)
Because patch_initialize is part of the generated apply and rollback scripts, we can't easily change its signature. Hence this procedure is called directly by datapatch. dbms_sqlpatch.set_patch_metadata(p_patch_id IN NUMBER,
p_patch_uid         IN NUMBER,
p_superseding       IN BOOLEAN,
p_application_patch IN BOOLEAN,
p_patch_descriptor  IN XMLType := NULL,
p_patch_directory   IN BLOB    := NULL);
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>
 
VERIFY_QUERYABLE_INVENTORY
Tests the queryable inventory functionality. If QI is working properly, then the string 'OK' is returned, otherwise any errors are returned. dbms_sqlpatch.verify_queryable_inventory RETURN VARCHAR2;
SELECT dbms_sqlpatch.verify_queryable_inventory
FROM dual;

VERIFY_QUERYABLE_INVENTORY
---------------------------
OK

Related Topics
DBMS_INTERNAL_ROLLING
DBMS_QOPATCH
DBMS_ROLLING
Packages
What's New In 12cR1
What's New In 12cR2

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