C:\>McOptions -h

McOptions Utility by Michel Cadot: Version 2022.02.06

Copyright (c) Michel Cadot, 2016-2022. All rights reserved.

Usage: McOptions.exe [-h] [-f [-fr]] [-c [--disable option>[...]]] [-o] [-ro] [-v] [<l

with:
  { -c  | --check }      Checks now (requires SYSDBA privilege).
  { --disable <feature or option> }
                         Disables the checking of the specified feature or option
                         (ignored if "-c" is not given).
  { -f  | --features }   Displays the paying used features in addition to the options
  { -fr | --free }       Includes the free used features (this option implies "-f").
  { -h  | --help }       Displays the version and the usage help.
  { -o  | --options }    Displays installed options.
  { -ro | --root_only }  When connected to CDB$ROOT, displays root only
                           (this option is ignored in versions before 12c).
  { -v  | --verbose }    Displays additional information about patches:
                           registry history, patches list and, if connected as SYSDBA,
                           incompatibilities between a PDB and the CDB to which it bel
                           and PDB alerts.

and <logon> is:
  { <username>[/<password>][@<connect_identifier>]
  | /[@<connect_identifier>] }
  | <proxyuser>[<username>][/<password>][@<connect_identifier>]
  | [<username>]/[@<connect_identifier>] }
  [AS SYSDBA]
in the 2 lines before the last one, [] around <username> are real [] not syntactical
characters denoting an optional parameter. These lines refer to proxy connection.

The program shows the options and packs used in an Oracle instance/database.

* If the "-c" option is given, the program checks which features, options and packs
  are used in the current instance. This option requires an account with SYSDBA
  privilege (there is no need to specify "AS SYSDBA" in the <logon> parameter:
  McOptions tries anyway a connection "AS SYSDBA" if this option is given).
* The other program feature options require an account with the SELECT privilege on
  the V$DATABASE, V$OPTION, V$VERSION, GV$CELL_STATE, DBA_FEATURE_USAGE_STATISTICS
  and DBA_REGISTRY catalog views.
* The "-v" option requires in addition SELECT privilege on the DBA_REGISTRY_HISTORY,
  DBA_REGISTRY_SQLPATCH, PDB_PLUG_IN_VIOLATIONS and PDB_ALERTS catalog views,
  and the EXECUTE privilege on the DBMS_QOPATCH package.
* The program works for Oracle versions from 10gR2 to 21c.
* In 12c+ multitenant databases, if you are connected to the root container then the
  program gives information for all the containers you have access unless the option
  "-ro" is given.
* There can be several "--disable" parameters.

The program is provided as it is without any guarantees or warranty. Although the
author has attempted to find and correct any bugs in this free program, the author
is not responsible for any damage or losses of any kind caused by the use or misuse
of the program. The author is under no obligation to provide support, service,
corrections, or upgrades to this program.

You can freely use, copy and distribute this program but you can't modify it without
the permission of the author you can contact on http://www.orafaq.com
You can post your comments, ask for improvements, report bugs... on the program at
http://www.orafaq.com/forum/t/200328/
I encourage you to subscribe to this topic (link at top of the page) if you want to
be warned when a new version is released.

C:\>McOptions-64 michel/michel@mikj3db1

McOptions Utility by Michel Cadot: Version 2021.05.26

Copyright (c) Michel Cadot, 2016-2021. All rights reserved.

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
With the Advanced Analytics, Data Mining, OLAP, Partitioning and Real Application Testing options
Version 19.11.0.0.210420

Database MIKJ3 container MIKJ3DB1 instance mikj3 on myserver on 26-MAY-2021 18:06:15

Registered components
=====================

Component       Version         Status      Description
--------------- --------------- ----------- --------------------------------------------------
APEX            18.2.0.00.12    VALID       Oracle Application Express
APS             19.0.0.0.0      VALID       OLAP Analytic Workspace
CATALOG         19.0.0.0.0      VALID       Oracle Database Catalog Views
CATJAVA         19.0.0.0.0      VALID       Oracle Database Java Packages
CATPROC         19.0.0.0.0      VALID       Oracle Database Packages and Types
CONTEXT         19.0.0.0.0      VALID       Oracle Text
DV              19.0.0.0.0      VALID       Oracle Database Vault
JAVAVM          19.0.0.0.0      VALID       JServer JAVA Virtual Machine
OLS             19.0.0.0.0      VALID       Oracle Label Security
ORDIM           19.0.0.0.0      VALID       Oracle Multimedia
OWM             19.0.0.0.0      VALID       Oracle Workspace Manager
RAC             19.0.0.0.0      OPTION OFF  Oracle Real Application Clusters
SDO             19.0.0.0.0      VALID       Spatial
XDB             19.0.0.0.0      VALID       Oracle XML Database
XML             19.0.0.0.0      VALID       Oracle XDK
XOQ             19.0.0.0.0      VALID       Oracle OLAP API

SQL Patches
===========

Patch description                                       Patch UID Version       Flags Action   Status      Action time
------------------------------------------------------- ---------- ------------- ----- -------- ----------- -------------------
Windows Database Bundle Patch : 19.7.0.0.200414 (309013   23471163 19.1.0>19.7.0 N     APPLY    SUCCESS     2020/06/19 11:05:37
OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)           23504850 19.7.0>19.7.0 NJ    APPLY    SUCCESS     2020/06/19 15:23:32
OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)           23504850 19.8.0>19.8.0 NJ    ROLLBACK SUCCESS     2020/08/09 07:25:37
Windows Database Bundle Patch : 19.8.0.0.200714 (312476   23674370 19.7.0>19.8.0 N     APPLY    SUCCESS     2020/08/09 07:25:39
OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897)           23690485 19.8.0>19.8.0 NJ    APPLY    SUCCESS     2020/08/09 08:11:01
OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897)           23690485 19.11.>19.11. NJ    ROLLBACK SUCCESS     2021/05/20 18:01:16
Windows Database Bundle Patch : 19.11.0.0.210420 (32409   24073904 19.8.0>19.11. N     APPLY    SUCCESS     2021/05/20 18:01:20

Used options and packs
======================

Option or Pack                        Usage                 Detected usages
------------------------------------- --------------------- ---------------
Entreprise Edition (no option/pack)   Currently used                    163
Diagnostics Pack                      Currently used                     56
Multitenant                           Currently used                     32
Tuning Pack                           Currently used                     55 (requires Diagnostics Pack)

C:\>McOptions-64 -f michel/michel@mikj3db1

McOptions Utility by Michel Cadot: Version 2021.05.26

<...>

Used features
=============

Feature                                                EE  Option or Pack              Usage Comment
------------------------------------------------------ --- ------------------------- ------- --------------------------------
Adaptive Plans                                         Yes                                32 Currently used
Automatic Maintenance - SQL Tuning Advisor             Yes Tuning Pack                    22 Last: 2021/01/13
control_management_pack_access DIAGNOSTIC              Yes Diagnostics Pack                1 Currently used
control_management_pack_access TUNING                  Yes Tuning Pack                     1 Currently used
Deferred Segment Creation                              Yes                                32 Currently used
Oracle Multitenant                                     Yes Multitenant                    32 Currently used
Oracle Utility Datapump (Export)                       Yes                                 2 Last: 2020/06/07
Parallel SQL Query Execution                           Yes                                32 Currently used
Result Cache                                           Yes                                32 Currently used
SQL Plan Directive                                     Yes Tuning Pack                    32 Currently used
SQL Tuning Set (system)                                Yes                                32 Currently used
Streams (user)                                         Yes                                 1 Currently used
Tuning Pack                                            Yes Diagnostics Pack               55 Currently used

C:\>McOptions-64 --free michel/michel@mikj3db1

McOptions Utility by Michel Cadot: Version 2021.05.26

<...>

Used features
=============

EE  Option or Pack            Feature                                                  Usage Comment
--- ------------------------- ------------------------------------------------------ ------- --------------------------------
No                            Automatic Maintenance - Optimizer Statistics Gathering      23 Currently used
No                            Automatic Maintenance - Space Advisor                       23 Currently used
No                            Automatic Reoptimization                                    32 Currently used
No                            Automatic Segment Space Management (system)                 32 Currently used
No                            Automatic Segment Space Management (user)                   32 Currently used
No                            Automatic SQL Execution Memory                              32 Currently used
No                            Automatic Undo Management                                   32 Currently used
No                            Character Semantics                                         32 Currently used
No                            Character Set                                               32 Currently used
No                            Extensibility                                                8 Currently used
No                            Index Organized Tables                                       1 Currently used
No                            Job Scheduler                                               32 Currently used
No                            LOB                                                         32 Currently used
No                            Locally Managed Tablespaces (system)                        32 Currently used
No                            Locally Managed Tablespaces (user)                          32 Currently used
No                            Logfile Multiplexing                                        32 Currently used
No                            Object                                                      27 Currently used
No                            Oracle Call Interface (OCI)                                  3 Last: 2020/05/31
No                            Oracle Java Virtual Machine (system)                        32 Currently used
No                            Oracle Java Virtual Machine (user)                          27 Currently used
No                            Oracle Utility Metadata API                                  5 Last: 2020/06/15
No                            Partitioning (system)                                       32 Currently used
No                            Recovery Area                                               32 Currently used
No                            Recovery Manager (RMAN)                                      2 Last: 2020/11/08
No                            RMAN - Disk Backup                                           2 Last: 2020/11/08
No                            SecureFiles (system)                                        32 Currently used
No                            SecureFiles (user)                                          32 Currently used
No                            Server Parameter File                                       32 Currently used
No                            Services                                                    32 Currently used
No                            SQL Plan Management                                         23 Currently used
No                            SQL*Plus                                                     2 Last: 2020/08/28
No                            Statistics Advisor                                          29 Currently used
No                            Streams (user)                                               1 Currently used
No                            Traditional Audit                                           32 Currently used
No                            Unified Audit                                               32 Currently used

Yes                           Adaptive Plans                                              32 Currently used
Yes                           Deferred Segment Creation                                   32 Currently used
Yes                           Parallel SQL Query Execution                                32 Currently used
Yes                           Result Cache                                                32 Currently used
Yes                           SQL Tuning Set (system)                                     32 Currently used
Yes                           Streams (user)                                               1 Currently used
Yes Diagnostics Pack          control_management_pack_access DIAGNOSTIC                    1 Currently used
Yes Diagnostics Pack          Tuning Pack                                                 55 Currently used
Yes Multitenant               Oracle Multitenant                                          32 Currently used
Yes Tuning Pack               Automatic Maintenance - SQL Tuning Advisor                  22 Last: 2021/01/13
Yes Tuning Pack               control_management_pack_access TUNING                        1 Currently used
Yes Tuning Pack               SQL Plan Directive                                          32 Currently used

C:\>McOptions-64 -c michel/michel@mikj3db1

McOptions Utility by Michel Cadot: Version 2021.05.26

<...>

Options currently used
======================

Entreprise Edition (no option/pack) -----> Adaptive Plans
                                           Deferred Segment Creation
                                           Parallel SQL Query Execution
                                           Result Cache
                                           SQL Tuning Set (system)
                                           Streams (user)
Diagnostics Pack ------------------------> control_management_pack_access DIAGNOSTIC
                                           Tuning Pack
Multitenant -----------------------------> Oracle Multitenant
                                           Oracle Pluggable Databases
Tuning Pack -----------------------------> control_management_pack_access TUNING
                                           SQL Plan Directive
                                           SQL Plan Management

C:\>McOptions-64 -o michel/michel@mikj3db1

McOptions Utility by Michel Cadot: Version 2021.05.26

Copyright (c) Michel Cadot, 2016-2021. All rights reserved.

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
With the Advanced Analytics, Data Mining, OLAP, Partitioning and Real Application Testing options
Version 19.11.0.0.210420

Database MIKJ3 container MIKJ3DB1 instance mikj3 on myserver on 26-MAY-2021 18:12:56

Installed options
=================

Active Data Guard
Adaptive Execution Plans
Advanced Analytics
Advanced Compression
Advanced Index Compression
Advanced replication
Application Role
Automatic Data Optimization
Backup Encryption
Basic Compression
Bit-mapped indexes
Block Change Tracking
Block Media Recovery
Cache Fusion Lock Accelerator
Centrally Managed User
Change Data Capture
Coalesce Index
Connection multiplexing
Connection pooling
Cross Transportable Backups
DICOM
Data Mining
Data Redaction
Database queuing
Database resource manager
Deferred Segment Creation
Duplexed backups
Enterprise User Security
Exadata Discovery
Export transportable tablespaces
Fast-Start Fault Recovery
File Mapping
Fine-grained Auditing
Fine-grained access control
Flashback Data Archive
Flashback Database
Flashback Table
Global Data Services
Heat Map
In-Memory Aggregation
In-Memory Column Store
Incremental backup and recovery
Instead-of triggers
Java
Join index
Managed Standby
Materialized view rewrite
OLAP
OLAP Window Functions
Objects
Online Index Build
Online Redefinition
Oracle Data Guard
Parallel backup and recovery
Parallel execution
Parallel load
Partitioning
Plan Stability
Point-in-time tablespace recovery
Privilege Analysis
Proxy authentication/authorization
Real Application Security
Real Application Testing
Result Cache
SQL Plan Management
Sample Scan
SecureFiles Encryption
Server Flash Cache
Snapshot time recovery
Spatial
Streams Capture
Table Clustering
Transparent Application Failover
Transparent Data Encryption
Transparent Sensitive Data Protection
Trial Recovery
Unused Block Compression
XStream
Zone Maps

<...>

>wmic os get osarchitecture
OSArchitecture
64-bit

DEBUG: Real-Time SQL Monitoring
DEBUG: SELECT used, sofar_exec, dbf_clob
         FROM   (SELECT count(*) used
                 FROM   dba_sql_monitor_usage
                 WHERE  num_db_reports > 0 AND
                        last_db_report_time >=  (select nvl(max(last_sample_date
), sysdate-7) from wri$_dbu_usage_sample) ),
                (SELECT num_db_reports sofar_exec,
                        xmlelement("sqlmon_usage",
                         xmlelement("num_em_reports", num_em_reports),
                         xmlelement("first_db_report_time",
                           to_char(first_db_report_time,
                                   'dd-mon-yyyy hh24:mi:ss')),
                         xmlelement("last_db_report_time",
                           to_char(last_db_report_time,
                                   'dd-mon-yyyy hh24:mi:ss')),
                         xmlelement("first_em_report_time",
                           to_char(first_em_report_time,
                                   'dd-mon-yyyy hh24:mi:ss')),
                         xmlelement("last_em_report_time",
                           to_char(last_em_report_time,
                                   'dd-mon-yyyy hh24:mi:ss'))
                        ).getClobVal(2,2) dbf_clob
                FROM dba_sql_monitor_usage)
DEBUG: ORA-01422: exact fetch returns more than requested number of rows
DEBUG: Real-Time SQL Monitoring

SQL> set lines 200
SQL> SELECT used, sofar_exec, dbf_clob
         FROM   (SELECT count(*) used
               2      FROM   dba_sql_monitor_usage
  3    4                   WHERE  num_db_reports > 0 AND
                        last_db_report_time >=  (select nvl(max(last_sample_date
), sysdate-7) from wri$_dbu_usage_sample) ),
  5                  (SELECT num_db_reports sofar_exec,
                        xmlelement("sqlmon_usage",
  6                           xmlelement("num_em_reports", num_em_reports),
                         xmlelement("first_db_report_time",
  7    8    9   10   11                             to_char(first_db_report_time,
                                   'dd-mon-yyyy hh24:mi:ss')),
                         xmlelement("last_db_report_time",
                           to_char(last_db_report_time,
                                   'dd-mon-yyyy hh24:mi:ss')),
                         xmlelement("first_em_report_time",
                           to_char(first_em_report_time,
                                   'dd-mon-yyyy hh24:mi:ss')),
 12   13   14   15   16   17   18   19                           xmlelement("last_em_report_time",
                           to_char(last_em_report_time,
                                   'dd-mon-yyyy hh24:mi:ss'))
                        ).getClobVal(2,2) dbf_clob
                FROM dba_sql_monitor_usage) 20   21   22   23  ;

      USED SOFAR_EXEC DBF_CLOB
---------- ---------- --------------------------------------------------------------------------------
         3        280 <sqlmon_usage>
                        <num_em_reports>0</num_em_reports>
                        <first_db_report_time>04-a

         3        280 <sqlmon_usage>
                        <num_em_reports>0</num_em_reports>
                        <first_db_report_time>04-a

         3        280 <sqlmon_usage>
                        <num_em_reports>0</num_em_reports>
                        <first_db_report_time>04-a

McOptions -h

SYSTEMINFO

SET

C:\>McOptions-64 -ro -v michel/michel

McOptions Utility by Michel Cadot: Version 2020.06.27

Copyright (c) Michel Cadot, 2016-2020. All rights reserved.

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
With the Advanced Analytics, Data Mining, OLAP, Partitioning and Real Application Testing options

Database MIKJ3 container CDB$ROOT instance mikj3 on arda on 27-JUN-2020 17:17:26

Component       Version         Status      Description
--------------- --------------- ----------- --------------------------------------------------
APEX            18.2.0.00.12    VALID       Oracle Application Express
APS             19.0.0.0.0      VALID       OLAP Analytic Workspace
CATALOG         19.0.0.0.0      VALID       Oracle Database Catalog Views
CATJAVA         19.0.0.0.0      VALID       Oracle Database Java Packages
CATPROC         19.0.0.0.0      VALID       Oracle Database Packages and Types
CONTEXT         19.0.0.0.0      VALID       Oracle Text
DV              19.0.0.0.0      VALID       Oracle Database Vault
JAVAVM          19.0.0.0.0      VALID       JServer JAVA Virtual Machine
OLS             19.0.0.0.0      VALID       Oracle Label Security
ORDIM           19.0.0.0.0      VALID       Oracle Multimedia
OWM             19.0.0.0.0      VALID       Oracle Workspace Manager
RAC             19.0.0.0.0      OPTION OFF  Oracle Real Application Clusters
SDO             19.0.0.0.0      LOADING     Spatial
XDB             19.0.0.0.0      VALID       Oracle XML Database
XML             19.0.0.0.0      VALID       Oracle XDK
XOQ             19.0.0.0.0      VALID       Oracle OLAP API

Operation time   Operation    Space     Version           Patch Comments
---------------- ------------ --------- --------------- ------- ------------------------------------------------------------
                 BOOTSTRAP    DATAPATCH 19                      RDBMS_19.3.0.0.0DBRU_WINDOWS.X64_190528
2020/06/19 10:59 RU_APPLY     SERVER    19.0.0.0.0              Patch applied from 19.3.0.0.0 to 19.7.0.0.0: Release_Update
2020/06/19 15:22 jvmpsu.sql   SERVER    19.7.0.0.200414       0 RAN jvmpsu.sql
2020/06/19 15:22 APPLY        SERVER    19.7.0.0.200414       0 OJVM RU post-install

Patch description                                       Patch UID Version       Flags Action   Status      Action time
------------------------------------------------------- ---------- ------------- ----- -------- ----------- -------------------
Windows Database Bundle Patch : 19.7.0.0.200414 (309013   23471163 19.1.0>19.7.0 N     APPLY    SUCCESS     19/06/2020 11:05:29
OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)           23504850 19.7.0>19.7.0 NJ    APPLY    SUCCESS     19/06/2020 15:23:32


Patch Id : 30901317
        Action : APPLY
        Action Time : 19-JUN-2020 11:05:29
        Description : Windows Database Bundle Patch : 19.7.0.0.200414 (30901317)
        Logfile : D:\Oracle2\cfgtoollogs\sqlpatch\30901317\23471163/30901317_apply_MIKJ3_CDBROOT_2020Jun19_10_55_54.log
        Status : SUCCESS

Patch Id : 30805684
        Action : APPLY
        Action Time : 19-JUN-2020 15:23:32
        Description : OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)
        Logfile : D:\Oracle2\cfgtoollogs\sqlpatch\30805684\23504850/30805684_apply_MIKJ3_CDBROOT_2020Jun19_15_21_01.log
        Status : SUCCESS

==> No PDB alerts

Option or Pack                        Usage                 Detected usages
------------------------------------- --------------------- ---------------
Entreprise Edition (no option/pack)   Currently used                     40
Diagnostics Pack                      Currently used                     32
Multitenant                           Currently used                     10
Tuning Pack                           Currently used                     31 (requires Diagnostics Pack)

Operation time   Type       Error Status    Cause
---------------- --------- ------ --------- ---------------------------------------------------------------------------------
PDBs                                        Action
------------------------------------------- ---------------------------------------------------------------------------------
Message
-----------------------------------------------------------------------------------------------------------------------------
2016/10/20 19:48 WARNING        0 RESOLVED  missing XDB schema type
MIKCDB1,MIKCDB2                             Please execute xdb.DBMS_XMLSTORAGE_MANAGE.GetTypeDDL
There are common XDB schema types missing from ROOT.
=============================================================================================================================
2018/10/23 10:35 ERROR          0 RESOLVED  SQL Patch
PDB$SEED                                    Call datapatch to install in the PDB or the CDB
PSU bundle patch 160719 (WINDOWS DB BUNDLE PATCH 12.1.0.2.160719(64bit):23530387): Installed in the CDB but not in the PDB.

set echo off feedback off heading off linesize 160 trimout on trimspool on pagesize 1000
col dt new_value dt
select to_char(systimestamp,'YYYYMMDDHH24MISSFF3') dt from dual
/
spool McOptions_&dt..txt
prompt
select banner from v$version where banner like 'Oracle%'
/
prompt
select banner_full from v$version
/
prompt
select 'ODA_INFRASTRUCTURE -> '||sys_context('SYS_CLUSTER_PROPERTIES','ODA_INFRASTRUCTURE') from dual
/
prompt
select 'ODA_TYPE -> '||sys_context('SYS_CLUSTER_PROPERTIES','ODA_TYPE') from dual
/
prompt
select 'EXADATA -> '||decode(count(*), 0,'NO', 'YES') from (select distinct cell_name from gv$cell_state)
/
prompt
set heading on feedback on
select distinct name features from dba_feature_usage_statistics order by 1
/
spool off

set