12c Database : View database patches from the sql prompt

From 12c onwards, you can view the database home patch list from sql prompt itself . A new package dbms_qopatch has been introduced to accomplish this.

Few cool sub routines in this package are get_opatch_install, lsinventory, opatch_bugs etc.

	SQL> set longchunksize 1000
	SQL> select DBMS_QOPATCH.get_opatch_install_info() from dual


	DBMS_QOPATCH.GET_OPATCH_INSTALL_INFO()
	-----------------------------------------------------------------------
	OracleHome-5958ed72-136c-4adc-a078-e3ec7081a6e8
	oracle_home
	oneoff
	/u01/app/oracle/product/12.1.0.1/db_1
	oracle_home
	/u01/app/oraInventory
	false
	

Opatch lsinventory (Typically inventory.xml file)

SQL> select DBMS_QOPATCH.get_opatch_lsinventory() from dual;

DBMS_QOPATCH.GET_OPATCH_LSINVENTORY()
---------------------------------------------------------------------------
< ?xml version="1.0" encoding="US-ASCII" standalone='yes'?>


OracleHome-2979699c-6748-4b7a-9477-ae79a63c0580
oracle_home oneoff /u01/app/oracle/product/12.1.0.1/db_1 oracle_home
/u01/app/oraInventory
false


OUIComponent-7eb53931-ee95-43b7-b37d-c41fdf749926
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.
12.1.0.1.0

en

Oracle Database 12c
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.
12.1.0.1.0


OUIComponent-77467009-bbea-4623-907d-f3bbee4a810b
Installs Sun JDK
1.6.0.37.0

en

Sun JDK
Installs Sun JDK
1.6.0.37.0


OUIComponent-370d483f-6754-443c-a3a6-36f0e7285a09
New OUI core minimum component that contains only the jars that are required for a) Patching via OPatch b) Applying Patchsets via OUI c) Inventory Management
12.1.0.1.0

en

oracle.swd.oui.core.min
New OUI core minimum component that contains only the jars that are required for a) Patching via OPatch b) Applying Patchsets via OUI c) Inventory Management
12.1.0.1.0


OUIComponent-625b11d1-f708-499f-be7c-1b3f7f51b4a9
Install SDK libraries available for install developers
12.1.0.1.0

en

Installer SDK Component
Install SDK libraries available for install developers
12.1.0.1.0


OUIComponent-d86f1ee1-a5e3-44a6-aca4-85f1c40ce689
Oracle One-Off Patch Installer
12.1.0.1.0

en

Oracle One-Off Patch Installer
Oracle One-Off Patch Installer
12.1.0.1.0


OUIComponent-b6be1c57-c02c-4078-9a99-2e749ed05995
Installs the components developed using the Oracle Software Packager.
12.1.0.1.0

en

Oracle Universal Installer
Installs the components developed using the Oracle Software Packager.
12.1.0.1.0


OUIComponent-38ccf7f6-b749-448d-affd-0bdca211dbb9
USM-specific files necessary for deconfig/deinstall
12.1.0.1.0

en

Oracle USM Deconfiguration
USM-specific files necessary for deconfig/deinstall
12.1.0.1.0


OUIComponent-cb68a64b-f01e-47ec-a222-61e80de54526
Deconfig tool for Oracle Configuration Manager
10.3.1.0.0

en

Oracle Configuration Manager Deconfiguration
Deconfig tool for Oracle Configuration Manager
10.3.1.0.0


OUIComponent-309edf5e-6112-46e2-b231-ed325a576cd6
HAS-specific files necessary for deconfig/deinstall
12.1.0.1.0

en

Oracle RAC Deconfiguration
HAS-specific files necessary for deconfig/deinstall
12.1.0.1.0


OUIComponent-44227088-7174-4d40-b5ba-6ddb85d2152b
DBCA-specific files necessary for deconfig/deinstall
12.1.0.1.0

en

Oracle DBCA Deconfiguration
DBCA-specific files necessary for deconfig/deinstall
12.1.0.1.0


OUIComponent-61d02517-7a84-496e-aa53-7eaa27e2227d
Oracle Database Plugin for Oracle Virtual Assembly Builder
12.1.0.1.0

en

Oracle Database Plugin for Oracle Virtual Assembly Builder
Oracle Database Plugin for Oracle Virtual Assembly Builder
12.1.0.1.0


OUIComponent-0aac8ffb-1a01-4c54-9da0-6bb9f2c880a6
Installs Oracle Configuration Manager Client jars
10.3.2.1.0

en

Oracle Configuration Manager Client
Installs Oracle Configuration Manager Client jars
10.3.2.1.0


OUIComponent-5fa99099-4ca2-4462-9995-71469e1586de
Installs Oracle Configuration Manager
10.3.7.0.3

en

Oracle Configuration Manager
Installs Oracle Configuration Manager
10.3.7.0.3

	SQL> select dbms_qopatch.GET_OPATCH_BUGS from dual;
		As i have no patches applied on this home, no rows returned.

-Thanks
Geek DBA