Home » Other » Marketplace » McOptions: a program which allows to know which options are used in a database (10gR2 to 19c)
| McOptions: a program which allows to know which options are used in a database [message #649263] |
Sun, 20 March 2016 11:35  |
 |
Michel Cadot
Messages: 68049
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I have posted a program in our Oracle licensing wiki page.
This is a Windows program which allows you to know the installed and used options and to immediately check these later ones.
The help is the following one:
C:\>McOptions -h
McOptions Utility by Michel Cadot: Version 2021.11.23
Copyright (c) Michel Cadot, 2016-2021. All rights reserved.
Usage: McOptions.exe [-h] [-f [-fr]] [-c [--disable option>[...]]] [-o] [-ro] [-v] [<logon>]
where <options> can be one or several of:
{ -c | --check } Checks now (requires SYSDBA privilege).
{ --disable <feature or option> }
Disable the checking of the given feature or option
(ignored if "-c" is not given)
{ -f | --features } Displays the paying used features in addition to the options and packs.
{ -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, display 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 belongs,
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 database.
* If the "-c" option is given, the program checks which ones 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 feature options requires an account with SELECT privilege on the
V$DATABASE, V$OPTION, V$VERSION, DBA_FEATURE_USAGE_STATISTICS, DBA_REGISTRY
and DBA_REGISTRY_SQLPATCH catalog views.
* The "-v" option requires SELECT privileges 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 10gR1 to 19c.
* 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)
The program first displays the standard Oracle banner (from the server not the client):
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
Then it displays a database specific banner:
Database MIKJ3 container MIKJ3DB1 instance mikj3 on myserver on 26-MAY-2021 18:06:15
Then the registered components with their version and status (these information come from DBA_REGISTRY view):
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
Then, in versions 12c and up, the SQL or database patches (these information come from the DBA_REGISTRY_SQLPATCH, see the Oracle Reference book for an explaination of the columns):
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
And in the end the used options. The last 2 columns are similar to the CURRENTLY_USED/LAST_USAGE_DATE and DETECTED_USAGES columns of the DBA_FEATURE_USAGE_STATISTICS view.
"Currently used" is currently used in Oracle meaning that is it was in use the last time Oracle checked.
You can see which features are used to justify these options using the "-f" option ("EE" means "Enterprise Edition") (common minimal output has been removed and noted <...>):
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
Now the program can immediately check the currently used options and features using the "-c" option:
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
"Options currently used" here are options really currently used not the same than the "Currently used" of the previous chart which, I repeat, means used when Oracle checked it the last time.
Note: the "-c" option requires an account with SYSDBA privilege (it is then not needed to specify AS SYSDBA in the <logon> parameter).
In the end, if you want to know which options are installed you can use the "-o" option (this information is directly gotten from the V$OPTION view):
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
<...>
Note: McOptions is written for "On-Premise" systems and does not check for "Cloud" systems specific features, options or packs, then it associates features with XE, PE, SE* or EE editions and not with DBCS* or ExaCS* ones.
Note: McOptions cannot determine if the option is mandatory or not in some very specific cases; in these cases when it detects the use of the objects it reports as a used feature with the associated option or pack.
For instance, in the following cases (extract from 19c Licensing Manual, underlining is mine):
Quote:If you are licensed for the on-premise Oracle Database Enterprise Edition, then you can use certain subprograms of the DBMS_SQLTUNE package even if you do not have an Oracle Tuning Pack license.
Quote:Some data in DBA_STREAMS_TP_COMPONENT_STAT requires Oracle Diagnostics Pack. The following filter clause to any query on DBA_STREAMS_TP_COMPONENT_STAT shows Diagnostics-Pack-dependent data:
where STATISTIC_UNIT = 'PERCENT'
Note: McOptions does not take into account some restricted use licenses included in other ones; in these cases when it detects the use of the features it reports them as a used option or pack.
For instance (extract from 19c Licensing Manual):
Quote:Oracle Label Security includes a restricted use license for the following Oracle Enterprise Manager features:
• Application Data Models (previously called Data Discovery and Modeling)
• Sensitive Column Types and Sensitive Data Discovery
• Application Accelerators
If you want to use the Oracle Enterprise Manager features listed above not solely for the purposes of Oracle Label Security, then you must purchase a full-use license for Oracle Data Masking and Subsetting Pack.
References initially used:
Oracle® Database Licensing Information 10g Release 2 (10.2) B14199-28 (June 2014, final version)
Oracle® Database Licensing Information 11g Release 2 (11.2) E47877-05 (September 2014)
Oracle® Database Licensing Information 12c Release 1 (12.1) E49208-10 (May 2015)
Latest references used:
Oracle® Database Licensing Information 10g Release 2 (10.2) B14199-28 (June 2014, final version)
Oracle® Database Licensing Information 11g Release 2 (11.2) E47877-11 (December 2016, final version)
Oracle® Database Licensing Information User Manual 12c Release 1 (12.1) E49208-26 (April 2020, final version)
Oracle® Database Licensing Information User Manual 12c Release 2 (12.2) E85683-23 (September 2021)
Oracle® Database Licensing Information User Manual 18c E87202-26 (September 2021)
Oracle® Database Licensing Information User Manual 19c E94254-30 (September 2021)
Latest version: 2021.11.23, download in Oracle licensing wiki page
32-bit:
MD5: f4275ede66ab31420d91ccb885a4e95c
SHA-1: c547fbb1fe0071c4d9e8910e93810b34b01bc5f4
64-bit:
MD5: f1cacf4c344afd248003720c5535d266
SHA-1: 28de0c0c760ae03562601a4886aee1c7bd7aa1b7
[Updated on: Tue, 23 November 2021 12:14] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: McOptions: a program which allows to know which options are used in a database [message #655293 is a reply to message #655247] |
Fri, 26 August 2016 02:31  |
 |
willnb10
Messages: 5
Registered: August 2016
|
Junior Member |
|
|
Here is the result
Toggle SpoilerResult of set:
>set
ALLUSERSPROFILE=C:\ProgramData
APPDATA=C:\Users\Administrator\AppData\Roaming
CommonProgramFiles=C:\Program Files\Common Files
CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files
CommonProgramW6432=C:\Program Files\Common Files
COMPUTERNAME=ERASED
ComSpec=C:\Windows\system32\cmd.exe
DATA_PUMP_DIR=c:\oracle11\admin\DB\dpdump\
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Users\Administrator
JAVA_HOME=C:\Progra~1\Java\jdk1.7.0_79\bin
LOCALAPPDATA=C:\Users\Administrator\AppData\Local
LOGONSERVER=\\ESM-VALID-INT
NUMBER_OF_PROCESSORS=8
ORACLE_BASE=c:\oracle11\
ORACLE_HOME=c:\oracle11\DatabaseOra11\
ORACLE_SID=DB
OS=Windows_NT
Path=c:\oracle11\DatabaseOra11\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;;C:\Program Files (x86)\NTP\bin;C:\Program Files\Java\jre7\bin
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
PROCESSOR_ARCHITECTURE=AMD64
PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 62 Stepping 4, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=3e04
ProgramData=C:\ProgramData
ProgramFiles=C:\Program Files
ProgramFiles(x86)=C:\Program Files (x86)
ProgramW6432=C:\Program Files
PROMPT=$P$G
PSModulePath=C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
PUBLIC=C:\Users\Public
RANDFILE=C:\Program Files (x86)\NTP\etc\.rnd
SESSIONNAME=RDP-Tcp#0
SID=DB
SystemDrive=C:
SystemRoot=C:\Windows
TEMP=C:\Users\ADMINI~1\AppData\Local\Temp\1
TMP=C:\Users\ADMINI~1\AppData\Local\Temp\1
USERDOMAIN=ERASED
USERNAME=Administrator
USERPROFILE=C:\Users\Administrator
windir=C:\Windows
Result of dir C:\Perl
>dir C:\Perl
Volume in drive C is SYSTEM
Volume Serial Number is F204-16BD
Directory of C:\
File Not Found
|
|
|
|
|
|
|
|
|
|
|
|
| Re: McOptions: a program which allows to know which options are used in a database [message #661156 is a reply to message #656366] |
Thu, 09 March 2017 04:23 |
|
Michel Cadot
Messages: 68049
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Changes in the new version are the following ones:
- Fix minor bugs in all versions
- Several improvements in all versions
- Support of multitenant databases in 12c: if you connect to root container, McOptions returns you the information about all containers
- Updated today with the latest licensing information:
Oracle® Database Licensing Information 11g Release 2 (11.2) E47877-11 (December 2016)
Oracle® Database Licensing Information User Manual 12c Release 1 (12.1) E49208-20 (December 2016)
Next step: Support of 12.2.
[Updated on: Thu, 09 March 2017 04:24] Report message to a moderator |
|
|
|
 Re: McOptions: a program which allows to know which options are used in a database [message #664473 is a reply to message #649263] |
Wed, 19 July 2017 17:17 |
 |
ridham
Messages: 1
Registered: July 2017
|
Junior Member |
|
|
I ran this utility against 12.1.0.2 database with "-c" option and I got following error message:
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
Please update the query appropriately...
FYI - when I executed this SQL in the database, I got following result:
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
|
|
|
|
|
|
|
|
| Re: McOptions: a program which allows to know which options are used in a database [message #665436 is a reply to message #664513] |
Tue, 05 September 2017 11:15 |
|
Michel Cadot
Messages: 68049
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
New version with the following changes:
- Add option "-ro" for "root only" when connected to CDB$ROOT (see help in first post)
- Add synonyms to program parameters (see help in first post)
- Add registered components in default display (see example in first post)
- Add SQL/database patches for 12.1+ versions (see example in first post)
- Add support of version 12.2 using E49729-17 book (May 2017)
- Because of bug 23734270 with no workaround (wrong/unavailable data in dictionary), ignore "Real-Time SQL Monitoring" feature in 12.*
[Updated on: Tue, 05 September 2017 11:17] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: McOptions: a program which allows to know which options are used in a database [message #672813 is a reply to message #665436] |
Sun, 28 October 2018 03:52 |
|
Michel Cadot
Messages: 68049
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
New version with the following changes:
- Add option "--disable" (see help in first post and below)
- Updated with the latest licensing information:
Oracle® Database Licensing Information User Manual 12c Release 1 (12.1) E49208-23 (May 2018)
Oracle® Database Licensing Information User Manual 12c Release 2 (12.2) E85683-04 (August 2018)
- Apply fix for bug 1993134.1 in the code
- Code for option "-c" has been completely rewritten; now the program executes, whatever is your patch level, either the code of the latest PSU at the time McOptions is delivered (currently PSU 181016) or the code of your database if it is newer; more, it backports the code of the latest versions to the previous ones when appropriate so you have fixes on versions that are no more updated by Oracle
Sometimes "-c" option aborts with an error in the OCI layer and then you have not the result of the checking. This has been discovered in 10g for features "Change Data Capture" and "Oracle Database Vault" but may happen in other cases.
The new option "--disable" has been introduced to prevent from this as you can disable the checking of the features or options that lead to this error in your case.
You can give several "--disable" options on the command line.
[Updated on: Tue, 30 October 2018 07:02] Report message to a moderator |
|
|
|
|
|
|
|
| Re: McOptions: a program which allows to know which options are used in a database [message #681229 is a reply to message #673581] |
Sat, 27 June 2020 11:07 |
|
Michel Cadot
Messages: 68049
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
New version (2020.06.27) with the following changes:
- Support of Oracle version 19c with licensing information from:
Oracle® Database Licensing Information User Manual 19c E94254-18 (April 2020)
- Update the previous versions with the latest licensing information:
Oracle® Database Licensing Information User Manual 12c Release 1 (12.1) E49208-26 (April 2020)
Oracle® Database Licensing Information User Manual 12c Release 2 (12.2) E85683-16 (April 2020)
Oracle® Database Licensing Information User Manual 18c E87202-18 (April 2020)
- Improve the previous versions, for instance check the control_management_pack_access init.ora parameter
- Fix a couple of minor bugs
- New option "-v" or "--verbose" which gives the following information (if you are allowed to get it) (see example below):
- registry history
- list of patches
- PDB violations and alerts
Verbose option
Here's an example of use of the verbose option:
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)
- The registry history which is the chart with first column "Operation time" giving the operation, its type ("Space" column), its version and some comments
- The list of the patches (each section starting with "Patch Id :") with the patch id, the action and action time, the description of the patch, its log file and status
- The PDB alerts (empty here)
An example of PDB alerts is (from another database):
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.
[Updated on: Mon, 18 January 2021 11:02] Report message to a moderator |
|
|
|
| Re: McOptions: a program which allows to know which options are used in a database [message #684406 is a reply to message #681229] |
Wed, 26 May 2021 12:02 |
|
Michel Cadot
Messages: 68049
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
New version (2021.05.27) with the following changes:
- Update with the latest licensing information:
Oracle® Database Licensing Information User Manual 12c Release 2 (12.2) E85683-20 (April 2021)
Oracle® Database Licensing Information User Manual 18c E87202-22 (April 2021)
Oracle® Database Licensing Information User Manual 19c E94254-26 (April 2021)
- Improvements in the ouput:
- All dates are now in the format YYYY/MM/DD, maybe followed by a time
- A title has been added before each chart
- ...
- Fix minor bugs and workaround Oracle ones
Note: The 12c Release 1 was in its final version in the previous update.
There are few modifications since the previous update however I want to point out a new free option introduced in 19c, version 19.8, Enterprise Edition: "Database In-Memory Base Level".
Quote:[This option] Allows you to experiment with Oracle Database In-Memory features without
purchasing the Oracle Database In-Memory option.
The following restrictions apply:
- The size of the In-Memory area (INMEMORY_SIZE) cannot exceed 16 GB for a CDB.
In an Oracle RAC environment, the size is limited to 16 GB for each instance.
- The compression level for all objects and columns is automatically and transparently set to QUERY LOW.
- The Automatic In-Memory feature is disabled.
- In-Memory Column Store feature tracking is tracked for "In-Memory Base Level" rather than "In-Memory Column Store."
- The CellMemory feature is disabled for Oracle Exadata.
This option is enabled by setting the parameter "inmemory_force" to "BASE_LEVEL" (default is... "DEFAuLT").
Note: Oracle code does not actually report the use of "In-Memory Base Level" option, McOptions does.
Note: The first post of this topic has been updated to clarify the scope and some restrictions of the program.
[Updated on: Mon, 27 September 2021 11:26] Report message to a moderator |
|
|
|
|
|
|
|
| Re: McOptions: a program which allows to know which options are used in a database [message #684933 is a reply to message #684919] |
Tue, 28 September 2021 11:55 |
|
Michel Cadot
Messages: 68049
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
New version (2021.09.28)
Starting with 19.12, Oracle ported "Memoptimized Rowstore" and "In-Memory Column Store for External Tables" Exadata features on "On-Premise" systems (McOptions does not check for Exadata specific features, see first post of the topic).
Currently, Oracle embedded code on these systems does not verify the usage of these features, I included them in this version of McOptions.
Note: "In-Memory Column Store for External Tables" implies "In-Memory Column Store", so if you use the former, the later is also reported; both require "Database In-Memory" option (and Entreprise Edition).
"Memoptimized Rowstore" feature is part of the Entreprise Edition.
[Updated on: Tue, 28 September 2021 11:57] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Mon Dec 06 08:28:08 CST 2021
|
] 
Search
Help
Members
Register
Login
Home
