So here it is
Oracle announces a license audit, some urgency kicks in and this familiar but also really serious question comes down from management: “Are we using any unlicensed database features“. The seriousness is quite understandable, because if so, the company can look forward to some negotiations with Oracle over license fees, possibly resulting in considerable and unforeseen extra costs.
Tracking… why
To be able to provide a swift and correct answer to this question, I track the usage of database options and management packs. As you might expect, tracking also enables detection of any deliberate or accidental unlicensed feature usage, so I can stop it sooner than later. And stopping it sooner is better because usage during months or years isn’t as easily excused by Oracle as usage during a day or week.
Tracking… how
Tracking is done by way of 2 views, both derived from “options_packs_usage_statistics.sql“, provided by Oracle Support –> MOS Note 1317265. Recently this script has been updated to handle version 12.2, so I had to update my views too. The Oracle script can be used on database version 11gR2 and higher, and on 12c container as well as non-container 12c databases. My views can also be used on 11gR2 databases and higher ( EE, SE and SE2 ), but assume a non-container database.
Bugs
Some bugs (Doc ID 1309070.1) are associated with DBA_FEATURE_USAGE_STATISTICS, the main data source for “options_packs_usage_statistics.sql“. At this time they mention false positives over the use of compression or encryption with Secure Files and RMAN, and with the reporting of Oracle Spatial usage where only Oracle Locator is used.
Disclaimer
The following code provide usage statistics for Database Options, Management Packs and their corresponding features.
This information is to be used for informational purposes only and does not represent any license entitlement or requirement.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 | SET DEFINE OFF; CREATE OR REPLACE FORCE VIEW FEATURE_USAGE AS select product , decode(usage, 'NO_USAGE','NO', usage ) "Used" , last_sample_date , first_usage_date , last_usage_date ------- following sql is based on options_packs_usage_statistics.sql --> MOS Note 1317265.1 from ( with MAP as ( -- mapping between features tracked by DBA_FUS and their corresponding database products (options or packs) select '' PRODUCT, '' feature, '' MVERSION, '' CONDITION from dual union all SELECT 'Active Data Guard' , 'Active Data Guard - Real-Time Query on Physical Standby' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Active Data Guard' , 'Global Data Services' , '^12\.' , ' ' from dual union all SELECT 'Advanced Analytics' , 'Data Mining' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'ADVANCED Index Compression' , '^12\.' , 'BUG' from dual union all SELECT 'Advanced Compression' , 'Advanced Index Compression' , '^12\.' , 'BUG' from dual union all SELECT 'Advanced Compression' , 'Backup HIGH Compression' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Backup LOW Compression' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Backup MEDIUM Compression' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Backup ZLIB Compression' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Data Guard' , '^11\.2|^12\.' , 'C001' from dual union all SELECT 'Advanced Compression' , 'Flashback Data Archive' , '^11\.2\.0\.[1-3]\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Flashback Data Archive' , '^(11\.2\.0\.[4-9]\.|12\.)' , 'INVALID' from dual union all -- licensing required by Optimization for Flashback Data Archive SELECT 'Advanced Compression' , 'HeapCompression' , '^11\.2|^12\.1' , 'BUG' from dual union all SELECT 'Advanced Compression' , 'HeapCompression' , '^12\.[2-9]' , ' ' from dual union all SELECT 'Advanced Compression' , 'Heat Map' , '^12\.1' , 'BUG' from dual union all SELECT 'Advanced Compression' , 'Heat Map' , '^12\.[2-9]' , ' ' from dual union all SELECT 'Advanced Compression' , 'Hybrid Columnar Compression Row Level Locking' , '^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Information Lifecycle Management' , '^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Oracle Advanced Network Compression Service' , '^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Oracle Utility Datapump (Export)' , '^11\.2|^12\.' , 'C001' from dual union all SELECT 'Advanced Compression' , 'Oracle Utility Datapump (Import)' , '^11\.2|^12\.' , 'C001' from dual union all SELECT 'Advanced Compression' , 'SecureFile Compression (user)' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'SecureFile Deduplication (user)' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Security' , 'ASO native encryption and checksumming' , '^11\.2|^12\.' , 'INVALID' from dual union all -- no longer part of Advanced Security SELECT 'Advanced Security' , 'Backup Encryption' , '^11\.2' , ' ' from dual union all SELECT 'Advanced Security' , 'Backup Encryption' , '^12\.' , 'INVALID' from dual union all -- licensing required only by encryption to disk SELECT 'Advanced Security' , 'Data Redaction' , '^12\.' , ' ' from dual union all SELECT 'Advanced Security' , 'Encrypted Tablespaces' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Security' , 'Oracle Utility Datapump (Export)' , '^11\.2|^12\.' , 'C002' from dual union all SELECT 'Advanced Security' , 'Oracle Utility Datapump (Import)' , '^11\.2|^12\.' , 'C002' from dual union all SELECT 'Advanced Security' , 'SecureFile Encryption (user)' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Security' , 'Transparent Data Encryption' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Change Management Pack' , 'Change Management Pack' , '^11\.2' , ' ' from dual union all SELECT 'Configuration Management Pack for Oracle Database' , 'EM Config Management Pack' , '^11\.2' , ' ' from dual union all SELECT 'Data Masking Pack' , 'Data Masking Pack' , '^11\.2' , ' ' from dual union all SELECT '.Database Gateway' , 'Gateways' , '^12\.' , ' ' from dual union all SELECT '.Database Gateway' , 'Transparent Gateway' , '^12\.' , ' ' from dual union all SELECT 'Database In-Memory' , 'In-Memory Aggregation' , '^12\.' , ' ' from dual union all SELECT 'Database In-Memory' , 'In-Memory Column Store' , '^12\.1\.0\.2\.0' , 'BUG' from dual union all SELECT 'Database In-Memory' , 'In-Memory Column Store' , '^12\.1\.0\.2\.[^0]|^12\.2' , ' ' from dual union all SELECT 'Database Vault' , 'Oracle Database Vault' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Database Vault' , 'Privilege Capture' , '^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'ADDM' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'AWR Baseline' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'AWR Baseline Template' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'AWR Report' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'Automatic Workload Repository' , '^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'Baseline Adaptive Thresholds' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'Baseline Static Computations' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'Diagnostic Pack' , '^11\.2' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'EM Performance Page' , '^12\.' , ' ' from dual union all SELECT '.Exadata' , 'Exadata' , '^11\.2|^12\.' , ' ' from dual union all SELECT '.GoldenGate' , 'GoldenGate' , '^12\.' , ' ' from dual union all SELECT '.HW' , 'Hybrid Columnar Compression' , '^12\.1' , 'BUG' from dual union all SELECT '.HW' , 'Hybrid Columnar Compression' , '^12\.[2-9]' , ' ' from dual union all SELECT '.HW' , 'Hybrid Columnar Compression Row Level Locking' , '^12\.' , ' ' from dual union all SELECT '.HW' , 'Sun ZFS with EHCC' , '^12\.' , ' ' from dual union all SELECT '.HW' , 'ZFS Storage' , '^12\.' , ' ' from dual union all SELECT '.HW' , 'Zone maps' , '^12\.' , ' ' from dual union all SELECT 'Label Security' , 'Label Security' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Multitenant' , 'Oracle Multitenant' , '^12\.' , 'C003' from dual union all -- licensing required only when more than one PDB containers are created SELECT 'Multitenant' , 'Oracle Pluggable Databases' , '^12\.' , 'C003' from dual union all -- licensing required only when more than one PDB containers are created SELECT 'OLAP' , 'OLAP - Analytic Workspaces' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'OLAP' , 'OLAP - Cubes' , '^12\.' , ' ' from dual union all SELECT 'Partitioning' , 'Partitioning (user)' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Partitioning' , 'Zone maps' , '^12\.' , ' ' from dual union all SELECT '.Pillar Storage' , 'Pillar Storage' , '^12\.' , ' ' from dual union all SELECT '.Pillar Storage' , 'Pillar Storage with EHCC' , '^12\.' , ' ' from dual union all SELECT '.Provisioning and Patch Automation Pack' , 'EM Standalone Provisioning and Patch Automation Pack' , '^11\.2' , ' ' from dual union all SELECT 'Provisioning and Patch Automation Pack for Database' , 'EM Database Provisioning and Patch Automation Pack' , '^11\.2' , ' ' from dual union all SELECT 'RAC or RAC One Node' , 'Quality of Service Management' , '^12\.' , ' ' from dual union all SELECT 'Real Application Clusters' , 'Real Application Clusters (RAC)' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Real Application Clusters One Node' , 'Real Application Cluster One Node' , '^12\.' , ' ' from dual union all SELECT 'Real Application Testing' , 'Database Replay: Workload Capture' , '^11\.2|^12\.' , 'C004' from dual union all SELECT 'Real Application Testing' , 'Database Replay: Workload Replay' , '^11\.2|^12\.' , 'C004' from dual union all SELECT 'Real Application Testing' , 'SQL Performance Analyzer' , '^11\.2|^12\.' , 'C004' from dual union all SELECT '.Secure Backup' , 'Oracle Secure Backup' , '^12\.' , 'INVALID' from dual union all -- does not differentiate usage of Oracle Secure Backup Express, which is free SELECT 'Spatial and Graph' , 'Spatial' , '^11\.2' , 'INVALID' from dual union all -- does not differentiate usage of Locator, which is free SELECT 'Spatial and Graph' , 'Spatial' , '^12\.' , ' ' from dual union all SELECT 'Tuning Pack' , 'Automatic Maintenance - SQL Tuning Advisor' , '^12\.' , 'INVALID' from dual union all -- system usage in the maintenance window SELECT 'Tuning Pack' , 'Automatic SQL Tuning Advisor' , '^11\.2|^12\.' , 'INVALID' from dual union all -- system usage in the maintenance window SELECT 'Tuning Pack' , 'Real-Time SQL Monitoring' , '^11\.2' , ' ' from dual union all SELECT 'Tuning Pack' , 'Real-Time SQL Monitoring' , '^12\.' , 'INVALID' from dual union all -- default SELECT 'Tuning Pack' , 'SQL Access Advisor' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Tuning Pack' , 'SQL Monitoring and Tuning pages' , '^12\.' , ' ' from dual union all SELECT 'Tuning Pack' , 'SQL Profile' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Tuning Pack' , 'SQL Tuning Advisor' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Tuning Pack' , 'SQL Tuning Set (user)' , '^12\.' , 'INVALID' from dual union all -- no longer part of Tuning Pack SELECT 'Tuning Pack' , 'Tuning Pack' , '^11\.2' , ' ' from dual union all SELECT '.WebLogic Server Management Pack Enterprise Edition' , 'EM AS Provisioning and Patch Automation Pack' , '^11\.2' , ' ' from dual union all select '' PRODUCT, '' FEATURE, '' MVERSION, '' CONDITION from dual ), FUS as ( -- the current data set to be used: DBA_FEATURE_USAGE_STATISTICS or CDB_FEATURE_USAGE_STATISTICS for Container Databases(CDBs) select 0 as CON_ID, NULL as CON_NAME, -- Detect and mark with Y the current DBA_FUS data set = Most Recent Sample based on LAST_SAMPLE_DATE case when DBID || '#' || VERSION || '#' || to_char(LAST_SAMPLE_DATE, 'YYYYMMDDHH24MISS') = first_value (DBID ) over (partition by 0 order by LAST_SAMPLE_DATE desc nulls last, DBID desc) || '#' || first_value (VERSION ) over (partition by 0 order by LAST_SAMPLE_DATE desc nulls last, DBID desc) || '#' || first_value (to_char(LAST_SAMPLE_DATE, 'YYYYMMDDHH24MISS')) over (partition by 0 order by LAST_SAMPLE_DATE desc nulls last, DBID desc) then 'Y' else 'N' end as CURRENT_ENTRY, NAME , LAST_SAMPLE_DATE, DBID , VERSION , DETECTED_USAGES , TOTAL_SAMPLES , CURRENTLY_USED , FIRST_USAGE_DATE, LAST_USAGE_DATE , AUX_COUNT , FEATURE_INFO from DBA_FEATURE_USAGE_STATISTICS xy ), PFUS as ( -- Product-Feature Usage Statitsics = DBA_FUS entries mapped to their corresponding database products select CON_ID, CON_NAME, PRODUCT, NAME as FEATURE_BEING_USED, case when CONDITION = 'BUG' --suppressed due to exceptions/defects then '3.SUPPRESSED_DUE_TO_BUG' when detected_usages > 0 -- some usage detection - current or past and CURRENTLY_USED = 'TRUE' -- usage at LAST_SAMPLE_DATE and CURRENT_ENTRY = 'Y' -- current record set and ( trim(CONDITION) is null -- no extra conditions or CONDITION_MET = 'TRUE' -- extra condition is met and CONDITION_COUNTER = 'FALSE' ) -- extra condition is not based on counter then '6.CURRENT_USAGE' when detected_usages > 0 -- some usage detection - current or past and CURRENTLY_USED = 'TRUE' -- usage at LAST_SAMPLE_DATE and CURRENT_ENTRY = 'Y' -- current record set and ( CONDITION_MET = 'TRUE' -- extra condition is met and CONDITION_COUNTER = 'TRUE' ) -- extra condition is based on counter then '5.PAST_OR_CURRENT_USAGE' -- FEATURE_INFO counters indicate current or past usage when detected_usages > 0 -- some usage detection - current or past and ( trim(CONDITION) is null -- no extra conditions or CONDITION_MET = 'TRUE' ) -- extra condition is met then '4.PAST_USAGE' when CURRENT_ENTRY = 'Y' then '2.NO_CURRENT_USAGE' -- detectable feature shows no current usage else '1.NO_PAST_USAGE' end as USAGE, LAST_SAMPLE_DATE, DBID , VERSION , DETECTED_USAGES , TOTAL_SAMPLES , CURRENTLY_USED , case when CONDITION like 'C___' and CONDITION_MET = 'FALSE' then to_date('') else FIRST_USAGE_DATE end as FIRST_USAGE_DATE, case when CONDITION like 'C___' and CONDITION_MET = 'FALSE' then to_date('') else LAST_USAGE_DATE end as LAST_USAGE_DATE, EXTRA_FEATURE_INFO from ( select m.PRODUCT, m.CONDITION, m.MVERSION, -- if extra conditions (coded on the MAP.CONDITION column) are required, check if entries satisfy the condition case when CONDITION = 'C001' and ( regexp_like(to_char(FEATURE_INFO), 'compression used:[ 0-9]*[1-9][ 0-9]*time', 'i') or regexp_like(to_char(FEATURE_INFO), 'compression used: *TRUE', 'i') ) then 'TRUE' -- compression has been used when CONDITION = 'C002' and ( regexp_like(to_char(FEATURE_INFO), 'encryption used:[ 0-9]*[1-9][ 0-9]*time', 'i') or regexp_like(to_char(FEATURE_INFO), 'encryption used: *TRUE', 'i') ) then 'TRUE' -- encryption has been used when CONDITION = 'C003' and CON_ID=1 and AUX_COUNT > 1 then 'TRUE' -- more than one PDB are created when CONDITION = 'C004' and 'N'= 'N' then 'TRUE' -- not in oracle cloud else 'FALSE' end as CONDITION_MET, -- check if the extra conditions are based on FEATURE_INFO counters. They indicate current or past usage. case when CONDITION = 'C001' and regexp_like(to_char(FEATURE_INFO), 'compression used:[ 0-9]*[1-9][ 0-9]*time', 'i') then 'TRUE' -- compression counter > 0 when CONDITION = 'C002' and regexp_like(to_char(FEATURE_INFO), 'encryption used:[ 0-9]*[1-9][ 0-9]*time', 'i') then 'TRUE' -- encryption counter > 0 else 'FALSE' end as CONDITION_COUNTER, case when CONDITION = 'C001' then regexp_substr(to_char(FEATURE_INFO), 'compression used:(.*?)(times|TRUE|FALSE)', 1, 1, 'i') when CONDITION = 'C002' then regexp_substr(to_char(FEATURE_INFO), 'encryption used:(.*?)(times|TRUE|FALSE)', 1, 1, 'i') when CONDITION = 'C003' then 'AUX_COUNT=' || AUX_COUNT when CONDITION = 'C004' and 'N'= 'Y' then 'feature included in Oracle Cloud Services Package' else '' end as EXTRA_FEATURE_INFO, f.CON_ID , f.CON_NAME , f.CURRENT_ENTRY , f.NAME , f.LAST_SAMPLE_DATE, f.DBID , f.VERSION , f.DETECTED_USAGES , f.TOTAL_SAMPLES , f.CURRENTLY_USED , f.FIRST_USAGE_DATE, f.LAST_USAGE_DATE , f.AUX_COUNT , f.FEATURE_INFO from MAP m join FUS f on m.FEATURE = f.NAME and regexp_like(f.VERSION, m.MVERSION) where nvl(f.TOTAL_SAMPLES, 0) > 0 -- ignore features that have never been sampled ) where nvl(CONDITION, '-') != 'INVALID' -- ignore features for which licensing is not required without further conditions and not (CONDITION = 'C003' and CON_ID not in (0, 1)) -- multiple PDBs are visible only in CDB$ROOT; PDB level view is not relevant ) select grouping_id(CON_ID) as gid, CON_ID , decode(grouping_id(CON_ID), 1, '--ALL--', max(CON_NAME)) as CON_NAME, PRODUCT , decode(max(USAGE), '1.NO_PAST_USAGE' , 'NO_USAGE' , '2.NO_CURRENT_USAGE' , 'NO_USAGE' , '3.SUPPRESSED_DUE_TO_BUG', 'SUPPRESSED_DUE_TO_BUG', '4.PAST_USAGE' , 'PAST_USAGE' , '5.PAST_OR_CURRENT_USAGE', 'PAST_OR_CURRENT_USAGE', '6.CURRENT_USAGE' , 'CURRENT_USAGE' , 'UNKNOWN') as USAGE, max(LAST_SAMPLE_DATE) as LAST_SAMPLE_DATE, min(FIRST_USAGE_DATE) as FIRST_USAGE_DATE, max(LAST_USAGE_DATE) as LAST_USAGE_DATE from PFUS where USAGE in ('2.NO_CURRENT_USAGE', '4.PAST_USAGE', '5.PAST_OR_CURRENT_USAGE', '6.CURRENT_USAGE') -- ignore '1.NO_PAST_USAGE', '3.SUPPRESSED_DUE_TO_BUG' group by rollup(CON_ID), PRODUCT having not (max(CON_ID) in (-1, 0) and grouping_id(CON_ID) = 1) -- aggregation not needed for non-container databases order by GID desc, CON_ID, decode(substr(PRODUCT, 1, 1), '.', 2, 1), PRODUCT ); CREATE OR REPLACE FORCE VIEW FEATURE_USAGE_DETAILS AS select product , feature_being_used , usage , last_sample_date , dbid , ( select name from v$database ) dbname , version , detected_usages , total_samples , currently_used , first_usage_date , last_usage_date , extra_feature_info ------- following sql is based on options_packs_usage_statistics.sql --> MOS Note 1317265.1 from ( with MAP as ( -- mapping between features tracked by DBA_FUS and their corresponding database products (options or packs) select '' PRODUCT, '' feature, '' MVERSION, '' CONDITION from dual union all SELECT 'Active Data Guard' , 'Active Data Guard - Real-Time Query on Physical Standby' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Active Data Guard' , 'Global Data Services' , '^12\.' , ' ' from dual union all SELECT 'Advanced Analytics' , 'Data Mining' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'ADVANCED Index Compression' , '^12\.' , 'BUG' from dual union all SELECT 'Advanced Compression' , 'Advanced Index Compression' , '^12\.' , 'BUG' from dual union all SELECT 'Advanced Compression' , 'Backup HIGH Compression' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Backup LOW Compression' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Backup MEDIUM Compression' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Backup ZLIB Compression' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Data Guard' , '^11\.2|^12\.' , 'C001' from dual union all SELECT 'Advanced Compression' , 'Flashback Data Archive' , '^11\.2\.0\.[1-3]\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Flashback Data Archive' , '^(11\.2\.0\.[4-9]\.|12\.)' , 'INVALID' from dual union all -- licensing required by Optimization for Flashback Data Archive SELECT 'Advanced Compression' , 'HeapCompression' , '^11\.2|^12\.1' , 'BUG' from dual union all SELECT 'Advanced Compression' , 'HeapCompression' , '^12\.[2-9]' , ' ' from dual union all SELECT 'Advanced Compression' , 'Heat Map' , '^12\.1' , 'BUG' from dual union all SELECT 'Advanced Compression' , 'Heat Map' , '^12\.[2-9]' , ' ' from dual union all SELECT 'Advanced Compression' , 'Hybrid Columnar Compression Row Level Locking' , '^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Information Lifecycle Management' , '^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Oracle Advanced Network Compression Service' , '^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'Oracle Utility Datapump (Export)' , '^11\.2|^12\.' , 'C001' from dual union all SELECT 'Advanced Compression' , 'Oracle Utility Datapump (Import)' , '^11\.2|^12\.' , 'C001' from dual union all SELECT 'Advanced Compression' , 'SecureFile Compression (user)' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Compression' , 'SecureFile Deduplication (user)' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Security' , 'ASO native encryption and checksumming' , '^11\.2|^12\.' , 'INVALID' from dual union all -- no longer part of Advanced Security SELECT 'Advanced Security' , 'Backup Encryption' , '^11\.2' , ' ' from dual union all SELECT 'Advanced Security' , 'Backup Encryption' , '^12\.' , 'INVALID' from dual union all -- licensing required only by encryption to disk SELECT 'Advanced Security' , 'Data Redaction' , '^12\.' , ' ' from dual union all SELECT 'Advanced Security' , 'Encrypted Tablespaces' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Security' , 'Oracle Utility Datapump (Export)' , '^11\.2|^12\.' , 'C002' from dual union all SELECT 'Advanced Security' , 'Oracle Utility Datapump (Import)' , '^11\.2|^12\.' , 'C002' from dual union all SELECT 'Advanced Security' , 'SecureFile Encryption (user)' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Advanced Security' , 'Transparent Data Encryption' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Change Management Pack' , 'Change Management Pack' , '^11\.2' , ' ' from dual union all SELECT 'Configuration Management Pack for Oracle Database' , 'EM Config Management Pack' , '^11\.2' , ' ' from dual union all SELECT 'Data Masking Pack' , 'Data Masking Pack' , '^11\.2' , ' ' from dual union all SELECT '.Database Gateway' , 'Gateways' , '^12\.' , ' ' from dual union all SELECT '.Database Gateway' , 'Transparent Gateway' , '^12\.' , ' ' from dual union all SELECT 'Database In-Memory' , 'In-Memory Aggregation' , '^12\.' , ' ' from dual union all SELECT 'Database In-Memory' , 'In-Memory Column Store' , '^12\.1\.0\.2\.0' , 'BUG' from dual union all SELECT 'Database In-Memory' , 'In-Memory Column Store' , '^12\.1\.0\.2\.[^0]|^12\.2' , ' ' from dual union all SELECT 'Database Vault' , 'Oracle Database Vault' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Database Vault' , 'Privilege Capture' , '^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'ADDM' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'AWR Baseline' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'AWR Baseline Template' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'AWR Report' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'Automatic Workload Repository' , '^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'Baseline Adaptive Thresholds' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'Baseline Static Computations' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'Diagnostic Pack' , '^11\.2' , ' ' from dual union all SELECT 'Diagnostics Pack' , 'EM Performance Page' , '^12\.' , ' ' from dual union all SELECT '.Exadata' , 'Exadata' , '^11\.2|^12\.' , ' ' from dual union all SELECT '.GoldenGate' , 'GoldenGate' , '^12\.' , ' ' from dual union all SELECT '.HW' , 'Hybrid Columnar Compression' , '^12\.1' , 'BUG' from dual union all SELECT '.HW' , 'Hybrid Columnar Compression' , '^12\.[2-9]' , ' ' from dual union all SELECT '.HW' , 'Hybrid Columnar Compression Row Level Locking' , '^12\.' , ' ' from dual union all SELECT '.HW' , 'Sun ZFS with EHCC' , '^12\.' , ' ' from dual union all SELECT '.HW' , 'ZFS Storage' , '^12\.' , ' ' from dual union all SELECT '.HW' , 'Zone maps' , '^12\.' , ' ' from dual union all SELECT 'Label Security' , 'Label Security' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Multitenant' , 'Oracle Multitenant' , '^12\.' , 'C003' from dual union all -- licensing required only when more than one PDB containers are created SELECT 'Multitenant' , 'Oracle Pluggable Databases' , '^12\.' , 'C003' from dual union all -- licensing required only when more than one PDB containers are created SELECT 'OLAP' , 'OLAP - Analytic Workspaces' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'OLAP' , 'OLAP - Cubes' , '^12\.' , ' ' from dual union all SELECT 'Partitioning' , 'Partitioning (user)' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Partitioning' , 'Zone maps' , '^12\.' , ' ' from dual union all SELECT '.Pillar Storage' , 'Pillar Storage' , '^12\.' , ' ' from dual union all SELECT '.Pillar Storage' , 'Pillar Storage with EHCC' , '^12\.' , ' ' from dual union all SELECT '.Provisioning and Patch Automation Pack' , 'EM Standalone Provisioning and Patch Automation Pack' , '^11\.2' , ' ' from dual union all SELECT 'Provisioning and Patch Automation Pack for Database' , 'EM Database Provisioning and Patch Automation Pack' , '^11\.2' , ' ' from dual union all SELECT 'RAC or RAC One Node' , 'Quality of Service Management' , '^12\.' , ' ' from dual union all SELECT 'Real Application Clusters' , 'Real Application Clusters (RAC)' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Real Application Clusters One Node' , 'Real Application Cluster One Node' , '^12\.' , ' ' from dual union all SELECT 'Real Application Testing' , 'Database Replay: Workload Capture' , '^11\.2|^12\.' , 'C004' from dual union all SELECT 'Real Application Testing' , 'Database Replay: Workload Replay' , '^11\.2|^12\.' , 'C004' from dual union all SELECT 'Real Application Testing' , 'SQL Performance Analyzer' , '^11\.2|^12\.' , 'C004' from dual union all SELECT '.Secure Backup' , 'Oracle Secure Backup' , '^12\.' , 'INVALID' from dual union all -- does not differentiate usage of Oracle Secure Backup Express, which is free SELECT 'Spatial and Graph' , 'Spatial' , '^11\.2' , 'INVALID' from dual union all -- does not differentiate usage of Locator, which is free SELECT 'Spatial and Graph' , 'Spatial' , '^12\.' , ' ' from dual union all SELECT 'Tuning Pack' , 'Automatic Maintenance - SQL Tuning Advisor' , '^12\.' , 'INVALID' from dual union all -- system usage in the maintenance window SELECT 'Tuning Pack' , 'Automatic SQL Tuning Advisor' , '^11\.2|^12\.' , 'INVALID' from dual union all -- system usage in the maintenance window SELECT 'Tuning Pack' , 'Real-Time SQL Monitoring' , '^11\.2' , ' ' from dual union all SELECT 'Tuning Pack' , 'Real-Time SQL Monitoring' , '^12\.' , 'INVALID' from dual union all -- default SELECT 'Tuning Pack' , 'SQL Access Advisor' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Tuning Pack' , 'SQL Monitoring and Tuning pages' , '^12\.' , ' ' from dual union all SELECT 'Tuning Pack' , 'SQL Profile' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Tuning Pack' , 'SQL Tuning Advisor' , '^11\.2|^12\.' , ' ' from dual union all SELECT 'Tuning Pack' , 'SQL Tuning Set (user)' , '^12\.' , 'INVALID' from dual union all -- no longer part of Tuning Pack SELECT 'Tuning Pack' , 'Tuning Pack' , '^11\.2' , ' ' from dual union all SELECT '.WebLogic Server Management Pack Enterprise Edition' , 'EM AS Provisioning and Patch Automation Pack' , '^11\.2' , ' ' from dual union all select '' PRODUCT, '' FEATURE, '' MVERSION, '' CONDITION from dual ), FUS as ( -- the current data set to be used: DBA_FEATURE_USAGE_STATISTICS or CDB_FEATURE_USAGE_STATISTICS for Container Databases(CDBs) select 0 as CON_ID, NULL as CON_NAME, -- Detect and mark with Y the current DBA_FUS data set = Most Recent Sample based on LAST_SAMPLE_DATE case when DBID || '#' || VERSION || '#' || to_char(LAST_SAMPLE_DATE, 'YYYYMMDDHH24MISS') = first_value (DBID ) over (partition by 0 order by LAST_SAMPLE_DATE desc nulls last, DBID desc) || '#' || first_value (VERSION ) over (partition by 0 order by LAST_SAMPLE_DATE desc nulls last, DBID desc) || '#' || first_value (to_char(LAST_SAMPLE_DATE, 'YYYYMMDDHH24MISS')) over (partition by 0 order by LAST_SAMPLE_DATE desc nulls last, DBID desc) then 'Y' else 'N' end as CURRENT_ENTRY, NAME , LAST_SAMPLE_DATE, DBID , VERSION , DETECTED_USAGES , TOTAL_SAMPLES , CURRENTLY_USED , FIRST_USAGE_DATE, LAST_USAGE_DATE , AUX_COUNT , FEATURE_INFO from DBA_FEATURE_USAGE_STATISTICS xy ), PFUS as ( -- Product-Feature Usage Statitsics = DBA_FUS entries mapped to their corresponding database products select CON_ID, CON_NAME, PRODUCT, NAME as FEATURE_BEING_USED, case when CONDITION = 'BUG' --suppressed due to exceptions/defects then '3.SUPPRESSED_DUE_TO_BUG' when detected_usages > 0 -- some usage detection - current or past and CURRENTLY_USED = 'TRUE' -- usage at LAST_SAMPLE_DATE and CURRENT_ENTRY = 'Y' -- current record set and ( trim(CONDITION) is null -- no extra conditions or CONDITION_MET = 'TRUE' -- extra condition is met and CONDITION_COUNTER = 'FALSE' ) -- extra condition is not based on counter then '6.CURRENT_USAGE' when detected_usages > 0 -- some usage detection - current or past and CURRENTLY_USED = 'TRUE' -- usage at LAST_SAMPLE_DATE and CURRENT_ENTRY = 'Y' -- current record set and ( CONDITION_MET = 'TRUE' -- extra condition is met and CONDITION_COUNTER = 'TRUE' ) -- extra condition is based on counter then '5.PAST_OR_CURRENT_USAGE' -- FEATURE_INFO counters indicate current or past usage when detected_usages > 0 -- some usage detection - current or past and ( trim(CONDITION) is null -- no extra conditions or CONDITION_MET = 'TRUE' ) -- extra condition is met then '4.PAST_USAGE' when CURRENT_ENTRY = 'Y' then '2.NO_CURRENT_USAGE' -- detectable feature shows no current usage else '1.NO_PAST_USAGE' end as USAGE, LAST_SAMPLE_DATE, DBID , VERSION , DETECTED_USAGES , TOTAL_SAMPLES , CURRENTLY_USED , FIRST_USAGE_DATE, LAST_USAGE_DATE, EXTRA_FEATURE_INFO from ( select m.PRODUCT, m.CONDITION, m.MVERSION, -- if extra conditions (coded on the MAP.CONDITION column) are required, check if entries satisfy the condition case when CONDITION = 'C001' and ( regexp_like(to_char(FEATURE_INFO), 'compression used:[ 0-9]*[1-9][ 0-9]*time', 'i') or regexp_like(to_char(FEATURE_INFO), 'compression used: *TRUE', 'i') ) then 'TRUE' -- compression has been used when CONDITION = 'C002' and ( regexp_like(to_char(FEATURE_INFO), 'encryption used:[ 0-9]*[1-9][ 0-9]*time', 'i') or regexp_like(to_char(FEATURE_INFO), 'encryption used: *TRUE', 'i') ) then 'TRUE' -- encryption has been used when CONDITION = 'C003' and CON_ID=1 and AUX_COUNT > 1 then 'TRUE' -- more than one PDB are created when CONDITION = 'C004' and 'N'= 'N' then 'TRUE' -- not in oracle cloud else 'FALSE' end as CONDITION_MET, -- check if the extra conditions are based on FEATURE_INFO counters. They indicate current or past usage. case when CONDITION = 'C001' and regexp_like(to_char(FEATURE_INFO), 'compression used:[ 0-9]*[1-9][ 0-9]*time', 'i') then 'TRUE' -- compression counter > 0 when CONDITION = 'C002' and regexp_like(to_char(FEATURE_INFO), 'encryption used:[ 0-9]*[1-9][ 0-9]*time', 'i') then 'TRUE' -- encryption counter > 0 else 'FALSE' end as CONDITION_COUNTER, case when CONDITION = 'C001' then regexp_substr(to_char(FEATURE_INFO), 'compression used:(.*?)(times|TRUE|FALSE)', 1, 1, 'i') when CONDITION = 'C002' then regexp_substr(to_char(FEATURE_INFO), 'encryption used:(.*?)(times|TRUE|FALSE)', 1, 1, 'i') when CONDITION = 'C003' then 'AUX_COUNT=' || AUX_COUNT when CONDITION = 'C004' and 'N'= 'Y' then 'feature included in Oracle Cloud Services Package' else '' end as EXTRA_FEATURE_INFO, f.CON_ID , f.CON_NAME , f.CURRENT_ENTRY , f.NAME , f.LAST_SAMPLE_DATE, f.DBID , f.VERSION , f.DETECTED_USAGES , f.TOTAL_SAMPLES , f.CURRENTLY_USED , f.FIRST_USAGE_DATE, f.LAST_USAGE_DATE , f.AUX_COUNT , f.FEATURE_INFO from MAP m join FUS f on m.FEATURE = f.NAME and regexp_like(f.VERSION, m.MVERSION) where nvl(f.TOTAL_SAMPLES, 0) > 0 -- ignore features that have never been sampled ) where nvl(CONDITION, '-') != 'INVALID' -- ignore features for which licensing is not required without further conditions and not (CONDITION = 'C003' and CON_ID not in (0, 1)) -- multiple PDBs are visible only in CDB$ROOT; PDB level view is not relevant ) select CON_ID , CON_NAME , PRODUCT , FEATURE_BEING_USED, decode(USAGE, '1.NO_PAST_USAGE' , 'NO_PAST_USAGE' , '2.NO_CURRENT_USAGE' , 'NO_CURRENT_USAGE' , '3.SUPPRESSED_DUE_TO_BUG', 'SUPPRESSED_DUE_TO_BUG', '4.PAST_USAGE' , 'PAST_USAGE' , '5.PAST_OR_CURRENT_USAGE', 'PAST_OR_CURRENT_USAGE', '6.CURRENT_USAGE' , 'CURRENT_USAGE' , 'UNKNOWN') as USAGE, LAST_SAMPLE_DATE , DBID , VERSION , DETECTED_USAGES , TOTAL_SAMPLES , CURRENTLY_USED , FIRST_USAGE_DATE , LAST_USAGE_DATE , EXTRA_FEATURE_INFO from PFUS where USAGE in ('2.NO_CURRENT_USAGE', '3.SUPPRESSED_DUE_TO_BUG', '4.PAST_USAGE', '5.PAST_OR_CURRENT_USAGE', '6.CURRENT_USAGE') -- ignore '1.NO_PAST_USAGE' order by CON_ID, decode(substr(PRODUCT, 1, 1), '.', 2, 1), PRODUCT, FEATURE_BEING_USED, LAST_SAMPLE_DATE desc, PFUS.USAGE ); |
many thank for this. how do you know about advanced index compression this is a bug ? do you have a doc ID ?
That is a good tip especially to those new to the blogs here but very accurate information. Very nice blog post!! Thanks for the posting that is a really neatly written blog.