Oracle Tuning Pack Licensing
- Requires Licensing: Oracle Tuning Pack is a separately licensed Oracle Database Enterprise Edition option.
- Automatic Usage Risk: Features like SQL Tuning Advisor and Automatic Database Diagnostic Monitor (ADDM) trigger usage.
- License by Processor or Named User: Must match the database license type.
- Audit Risk: Accidental use can lead to non-compliance penalties.
Oracle Tuning Pack Licensing
Oracle’s Tuning Pack is an Oracle Database Enterprise Edition add-on that offers advanced SQL tuning and performance optimization tools. However, using this pack requires careful adherence to Oracle’s licensing rules.
This article provides IT asset managers and general audiences with a detailed overview of Oracle Tuning Pack licensing. We will cover the licensing models (Named User Plus vs. Processor), key rules and restrictions, the features included in the Tuning Pack, common compliance issues, and how to detect usage of Tuning Pack features.
The goal is to balance technical insight with practical compliance guidance across all Oracle Database versions, ensuring you can leverage the Tuning Pack’s benefits without violating license agreements.
Licensing Models for Oracle Tuning Pack
Oracle offers two primary licensing models for the Tuning Pack (as with most Oracle Database options): Named User Plus (NUP) licensing and Processor-based licensing. The appropriate model depends on your environment and how the database is accessed.
Regardless of the model, the Tuning Pack must be licensed in the same way and in the same quantity as the database it supplements. In other words, if your Oracle Database is licensed per processor, the Tuning Pack should also be licensed per processor (covering the same processors).
If your database is licensed by Named User Plus, the Tuning Pack must have at least the same number of NUP licenses (respecting Oracle’s minimums). Below is an explanation of each model and how to calculate licensing requirements:
Named User Plus (NUP) Licensing
User Plus licensing is based on the number of distinct users (including humans and non-human devices) accessing the Oracle Database and its features. Under this model, every person or device directly or indirectly using the Oracle Database must have a license.
This model is suitable for environments where you can reasonably count and identify all system users (for example, an internal application used by a known group of employees).
When licensing the Oracle Tuning Pack under the NUP model, consider the following:
- Minimum User Counts: Oracle requires a minimum number of NUP licenses per processor for Enterprise Edition databases. For Oracle Database Enterprise Edition, the minimum is 25 Named User Plus licenses per processor. Suppose you have a database server with four processors (as defined by Oracle’s licensing formula, explained below). In that case, you must have at least 100 Named User Plus licenses – or the number of users, whichever is higher. Oracle will not allow fewer than 25 NUP per processor even if you have, for example, only five users on a powerful server.
- Counting Users: Count all individuals and batch processes or devices that access the database. If an application connects to the database on behalf of users, you must license the end-users of that application. For example, if 50 employees use a reporting tool that queries the Oracle Database, those 50 employees would each need to be counted as Named Users. To ensure compliance, IT asset managers should work closely with database administrators to inventory all possible users (including service accounts or scripts).
- Alignment with Database License: The number of NUP licenses for the Tuning Pack must be at least equal to the number of NUP licenses for the database itself and meet the 25-per-processor minimum rule. If your database Enterprise Edition is licensed for 80 Named Users, you cannot license the Tuning Pack for only 40 users – it would need 80 users licensed (or more if a 25-per-processor minimum dictates a higher count). Essentially, you license the Tuning Pack NUP for every user of the database features, just as you do the database.
- Example Calculation: Suppose an Oracle database runs on a server with two processor cores (with Enterprise Edition), and you have 30 distinct users who access it. Oracle’s core factor for modern x86 processors is often 0.5 (we will cover processor calculation next), so two cores * 0.5 = 1 processor license (rounded up). The minimum NUP for one processor is 25 Named Users. Since you have 30 actual users over 25, you need 30 Named User Plus licenses for the database and 30 Named User Plus licenses for the Tuning Pack to cover those users. If the number of users grows, you must increase NUP licenses accordingly.
Processor-Based Licensing
Processor-based licensing is generally used for environments where counting users is impractical, such as public-facing applications or large enterprise systems with hundreds or thousands of internal and external users.
Under this model, you license the Oracle software per processor on the machines where the database is installed/running. In Oracle’s terms, a “processor” doesn’t always equal a physical CPU socket; it accounts for cores and hardware architecture via a core factor.
Oracle defines the required processor licenses by counting the cores and applying a core factor specific to the processor type.
Key points for Processor licensing of the Tuning Pack include:
- Counting Processors (Core Factor): For Enterprise Edition databases, the formula is: Number of Processor licenses = (Number of cores) × (Core Factor), rounded up to the next whole number. Oracle provides a Core Factor Table (available on Oracle’s website) that assigns a multiplier for different CPU types. For example, Intel and AMD x86 chips typically have a 0.5 core factor, meaning two cores count as one license; IBM POWER might have a factor of 1.0 (one core is one license), etc. All cores in the server are summed, multiplied by the factor, and any fraction is rounded up to a whole processor license. This result is the number of Processor licenses you need for Oracle Database and equally for the Tuning Pack if you use it.
- Example Calculation: Imagine your Oracle Database runs on a server with 8 Intel cores. According to Oracle’s core factor table, Intel x86 cores have a 0.5 factor. So, eight cores × 0.5 = 4.0, which means 4 Processor licenses are required for the database. If you intend to use the Tuning Pack on that database, you also need 4 Processor licenses of the Oracle Tuning Pack. Even if only some databases on that server use the Tuning Pack, Oracle’s policy is that if the software is installed and/or used on a processor, that processor must be fully licensed. You cannot partially license some cores for the option – it’s all or nothing on that server (unless you use hard partitioning or other Oracle-approved isolation, which is beyond the scope of this discussion).
- Alignment with Database License: Just as with NUP, the Tuning Pack’s processor licenses must match the count of the database’s processor licenses. You cannot mix licensing metrics in the same environment; for example, having the database on a Processor license while licensing the Tuning Pack by NUP is not allowed. Oracle expects all options and packs to follow the same licensing metric as a server or cluster database. This simplifies compliance: if a server’s database is licensed for X processors, any add-on like Tuning Pack used on that server should also be licensed for X processors.
- Multi-Server or RAC Environments: If your database uses Real Application Clusters (RAC) across multiple servers, you must count cores on all nodes where the database runs. For instance, a RAC database across two servers, each with eight cores, would have 16 cores. Using the earlier example core factor (0.5), that’s 8 Processor licenses required. You would need eight processor licenses for Enterprise Edition and 8 for the tuning pack to cover the RAC cluster. The rule of a minimum of 25 NUP per processor applies if you chose NUP on RAC (though large environments typically go with Processor licensing).
Choosing a Model: In summary, Processor licenses make sense for environments with a large or unpredictable user base (to avoid counting every user), whereas Named User Plus licenses are cost-effective for smaller, controlled user groups.
Many organizations license the base database by the processor for server-wide coverage and then license the Tuning Pack with the same processor count. If you opt for NUP licensing, be vigilant in tracking user counts and growth.
Remember, any user accessing any feature of the database counts toward NUP, and this extends to the usage of the Tuning Pack features.
Rules and Restrictions for Tuning Pack Usage
Licensing Oracle Tuning Pack involves more than just picking a metric and purchasing licenses. Oracle has specific rules and restrictions on how and when the Tuning Pack can be used.
Compliance requires meeting these conditions:
- Enterprise Edition Only: The Oracle Tuning Pack can only be licensed with Oracle Database Enterprise Edition (EE). It is not available for Standard Edition (SE) or other editions. You cannot use Tuning Pack features if your database is Standard Edition. Oracle’s license terms state that certain packs (including Tuning Pack and others like Advanced Compression) are only allowed on Enterprise Edition databases. Attempting to use the Tuning Pack on a Standard Edition database is a compliance violation (Oracle would require you to license the database as EE plus the pack or cease usage). Asset managers should ensure that any environment using Tuning Pack runs Enterprise Edition of Oracle Database.
- Prerequisite: Diagnostics Pack License: A critical restriction often overlooked is that Oracle Tuning Pack requires Oracle Diagnostic Pack to be licensed as welldocs.oracle.com. The Tuning Pack’s functionality builds on the performance monitoring capabilities provided by the Diagnostics Pack (which includes AWR, ADDM, etc.). Oracle’s documentation states that the Diagnostics Pack is a prerequisite to Tuning Pack; you must have Diagnostic Pack licenses to use Tuning Pack features. In practical terms, most customers who use the Tuning Pack will have purchased the “Diagnostics and Tuning Pack” together, but if there’s any doubt, ensure both are licensed for the target databases. Using the Tuning Pack alone without the Diagnostics Pack is not allowed, even if technically some functions might work – it would break licensing terms.
- License Must Match Database Deployment: The Tuning Pack must be licensed for the same databases (and their full capacity) on which it’s used. This sounds obvious, but it has important implications. For example, suppose you have an Oracle Enterprise Edition database running on a 16-core server, and you only license the database for eight cores (perhaps using hard partitioning to limit to 8 cores). In that case, you cannot use the Tuning Pack on more cores than the database is licensed for. The scope of your Tuning Pack license cannot exceed the scope of your database license, and vice versa; they should be aligned one-to-one. Additionally, if you have multiple Oracle databases on a server, a license for Tuning Pack generally covers usage on that server for the licensed database. You can’t “mix and match” licenses (e.g., using a Tuning Pack license from one server’s allocation on a different database without proper licensing).
- No Partial Licensing per Feature or Partial Usage: Oracle’s licensing is not modular to the level of individual features within the Tuning Pack. If you enable or use any Tuning Pack feature on a database, that entire database instance must be licensed for the Tuning Pack. You cannot, for instance, decide that only a certain schema or user will use the SQL Tuning Advisor and try to license just for that subset. The licensing is tied to the server/instance level usage. From Oracle’s perspective, if the option is installed and accessible, it must be licensed for that environment once used. Oracle provides a database initialization parameter
CONTROL_MANAGEMENT_PACK_ACCESSthat can be set toNONEto disable Diagnostic and Tuning Pack features at the database level. This is a helpful control: if you have databases not licensed for these packs, it’s wise to set this parameter to prevent accidental usage. - Use in Development/Test Environments: Oracle’s licensing applies to all environments – production, testing, development, etc. There is no free usage of Tuning Pack in non-production environments unless you have a special arrangement (like a purchased package that includes non-prod licenses or if you’re using Oracle’s cloud service that bundles it). Every environment where the Tuning Pack is used must be licensed appropriately. IT asset managers should not overlook development or QA databases: if DBAs use Tuning Pack tools on them (say, to tune a query in test), that technically requires a license for that environment. Oracle does offer a limited free license for personal learning or trial under OTN agreements, but those cannot be used for enterprise testing or any data derived from production. Always assume that if it’s an installation inside the company (even for testing), you must license the pack if it’s being used.
- License Term and Renewals: The Oracle Tuning Pack can be licensed perpetually (with an optional support contract for updates) or as term licenses (like one year). Most organizations have it as part of their perpetual Oracle Database licenses with annual support. Ensure support is maintained if you rely on the pack because using features past your support end date could be problematic. While technically, the license is perpetual if bought as such; Oracle support contracts ensure you have rights to updates, which are typically required for compliance in large enterprises.
In summary, adhere to the edition requirement (Enterprise Edition only), ensure the Diagnostic Pack is licensed, and match the licensing metrics and quantities to your database deployment. By following these rules, you set the foundation for staying compliant.
Included Components of the Oracle Tuning Pack
The Oracle Tuning Pack is focused on SQL tuning and performance optimization features. It provides database administrators and developers with expert recommendations for improving SQL query performance and optimizing storage structures.
Understanding the features of the Tuning Pack is important because using any of these features obligates you to have the license.
Below is a list of the major components and tools included under the Tuning Pack license, along with brief explanations of each:
- SQL Tuning Advisor: This tool analyzes individual SQL statements and suggests ways to improve their execution. It may recommend creating indexes, restructuring the SQL, or accepting an SQL Profile (a set of optimizer hints) to improve performance. Running the SQL Tuning Advisor (for example, via Oracle Enterprise Manager or by calling the
DBMS_SQLTUNEPL/SQL package) is a Tuning Pack feature. The advisor can dramatically speed up troublesome queries by guiding DBAs on adjusting them for better efficiency. - SQL Access Advisor: Whereas the SQL Tuning Advisor looks at specific statements, the SQL Access Advisor takes a broader view, analyzing workload patterns to recommend changes in schema design for performance gains. It might suggest adding or dropping indexes, partitioning tables, or creating materialized views to optimize data access paths. This is invaluable for tuning the database schema based on how an application queries data. Using the SQL Access Advisor (via Enterprise Manager or
DBMS_ADVISORwith the “SQL Access Advisor” task) requires a Tuning Pack license. - Automatic SQL Tuning: Oracle can automatically run SQL Tuning Advisor in the background during maintenance windows (for example, nightly) to identify and tune high-impact SQL statements. This feature, Automatic SQL Tuning, will often accept certain recommendations like SQL Profiles if configured. It’s essentially a “set and forget” way to continuously tune SQL over time. Because it invokes the SQL Tuning Advisor under the hood, Automatic SQL Tuning is part of the Tuning Pack. DBAs can review the auto-tuning reports to see what suggestions were made or implemented.
- SQL Tuning Sets: A SQL Tuning Set (STS) is a feature that allows DBAs to collect and group a set of SQL statements (for example, the top and slow queries or a specific workload) along with their execution context and performance statistics. This set can then be fed into advisors or moved between systems. Managing and using SQL Tuning Sets is included in the Tuning Pack. One exception noted by Oracle is that SQL Tuning Sets can also be used if you have licensed the Real Application Testing option instead of the Tuning Pack. But generally, if you are using STS for tuning purposes, it falls under Tuning Pack usage.
- Automatic Plan Evolution (SQL Plan Management): Oracle’s SQL Plan Management feature allows the database to maintain a history of execution plans for queries and evolve them over time to ensure stability and performance. Automatic plan evolution means the database will automatically accept better execution plans for SQL statements if it verifies they improve performance. This automated evolution of plans is a feature of the Tuning Pack. It builds on the SQL Plan Baseline mechanism (a base EE feature to lock down accepted plans). With the pack licensed, Oracle can do the heavy lifting of testing new plans and updating the baselines. DBAs would have to manually load and evolve plan baselines without the pack.
- Real-Time SQL Monitoring: Often referred to simply as SQL Monitoring, this feature provides a live view of the execution of long-running SQL queries or PL/SQL. It shows detailed information about each step of the execution plan, how far along it is, rows processed, I/O done, etc., and updates in real-time or near real-time. This is extremely useful for performance troubleshooting. Real-time SQL Monitoring is enabled when the query runs in parallel or consumes more than a certain amount of resources, and it can be accessed via Enterprise Manager or by querying V$SQL_MONITOR and V$SQL_PLAN_MONITOR views. SQL Monitoring is part of the Tuning Pack – using the V$SQL_MONITOR views or OEM’s Live SQL Monitoring screen requires the Tuning Pack license.
- Reorganize Objects: Oracle Enterprise Manager includes a feature to reorganize database objects such as tables and indexes (for example, to reclaim space, improve data organization, or reduce fragmentation). This might involve online redefinition or rebuilding operations guided by OEM. This functionality is considered part of the Tuning Pack. You leverage the Tuning Pack if you use Oracle’s tools to advise or carry out segment reorganization for performance/storage optimization. (Note that basic ALTER INDEX REBUILD or moving tables is part of Oracle DB, but OEM’s advisor-driven or automated reorganization features fall under the pack license).
These components are all covered under the Oracle Tuning Pack license. It’s important to recognize them because using these features – whether through Oracle Enterprise Manager (which has GUI pages for advisors, SQL tuning, etc.) or via direct PL/SQL APIs and scripts – constitutes using the Tuning Pack. Oracle’s documentation notes that Tuning Pack functionality can be accessed through Enterprise Manager and command-line APIs, and using either interface requires licensing.
In other words, it doesn’t matter if a DBA runs a manual script to invoke DBMS_SQLTUNE vs. clicking a button in OEM’s Performance Hub – both are equivalent and require you to pay for the pack. For asset managers, having this list of features helps educate their technical teams about what is allowed only if licensed.
Many of these features (especially the advisors and performance screens) are extremely helpful, so DBAs often enable them by habit; ensuring everyone knows they are licensable extras is key to compliance.
Read how Oracle Diagnostic Pack is licensed.
Common Compliance Issues and Pitfalls
Oracle licensing is notoriously complex, and the Tuning Pack is no exception. Organizations often fall into certain traps that lead to non-compliance.
Below are some common compliance issues related to Tuning Pack licensing, along with explanations:
- Unauthorized Usage of Tuning Pack Features: The most frequent compliance problem is when DBAs or users activate Tuning Pack features without a license. Oracle Database installations don’t physically prevent you from using these features by default. For example, a developer might inadvertently run a SQL Tuning Advisor task or generate an AWR report (AWR is part of Diagnostics Pack, which often goes hand-in-hand with Tuning Pack) on an unlicensed database. Such usage is considered “unlicensed” and can be flagged in an audit. Oracle’s policy is that if a feature is used, it must be licensed. There is no “trial period” or forgiveness because it was accidental. A typical scenario is that the DBAs use Enterprise Manager’s Performance and Tuning screens or run scripts that invoke DBSQLTUNE on which database the company didn’t buy the Tuning Pack. This unauthorized usage would put the company out of compliance. Often, these incidents are not malicious but due to a lack of awareness. To avoid this, restrict access to pack features by using the parameter eTROL_MANAGEMENT_PACK_ACCESS parlier or by disabling links in OEM for unlicensed packs. Even one-time or sporadic use of a pack feature is enough to require a license, according to Oracle’s rules.
- Exceeding Licensed Capacity (Overdeployment): Another common issue is when the usage of the Tuning Pack extends beyond what was purchased. This could mean more processors or users are utilizing the pack than you have licenses. For example, you licensed 50 Named Users for the Tuning Pack. Still, as the company grew, 80 employees are now using the application that leverages those tuning features – you are 30 users over your license. Similarly, perhaps you licensed four processors for the pack (matching a previous hardware configuration), but later upgraded your server to 8 cores without trueing-up the licenses. Oracle believes any usage beyond your licensed quantities is non-compliant and must be rectified (usually by purchasing additional licenses and back-support). Unlike some software that might shut down or prevent overuse, Oracle software will continue functioning, so the licensee must stay on top of this. IT asset managers should regularly review hardware changes and user counts related to Oracle systems. Align with your DBA and infrastructure teams whenever there’s an upgrade or scaling of Oracle databases to ensure the licenses (database and packs) are adjusted accordingly. Oracle’s contracts often state that if you deploy beyond your licensed rights, you must license that excess separately – so it’s best not to let it happen in the first place.
- Improper Licensing Alignment & Prerequisites: As noted in the rules, a frequent pitfall is not aligning the Tuning Pack licensing with the database licenses or ignoring prerequisites. For instance, a company might license the Tuning Pack for a database but forget to license the Diagnostics Pack, which is required to use Tuning Pack features legally. Oracle would likely penalize this omission in an audit by requiring back licensing of the Diagnostics Pack. Another example is when a company has an Unlimited License Agreement (ULA) or some licenses for the database but mistakenly assumes that it covers the packs – not true; packs are always separate unless explicitly included. Mixing up metrics is another alignment issue: you cannot have the database on a processor license while the Tuning Pack on Named User Plus for the same deployment; such a configuration would violate the requirement that the metric and counts match. Furthermore, deploying the Tuning Pack on a Standard Edition database (perhaps via a misguided attempt to use AWR or advisors on SE) is improper and non-compliant. Oracle could interpret that as requiring you to upgrade to Enterprise Edition and purchase the packs, which is an extremely costly mistake if unintentional. Always double-check that any use of the Tuning Pack is happening only on properly licensed Enterprise Edition databases that also have the Diagnostics Pack licensed.
- Lack of Knowledge and Accidental Feature Use: We separate this from outright unauthorized usage to emphasize the accidental nature. Oracle’s feature set is rich, and sometimes, DBAs or developers might not realize an action they take is part of a licensed pack. For example, running a query against certain database views might imply the usage of a pack. Oracle documentation notes that even querying the V$SQL_MONITOR view (to see real-time stats) is considered part of the Tuning Pack. A developer troubleshooting performance might Google an Oracle script to see long-running queries and unknowingly invoke a Tuning Pack feature. It’s quite easy to do – e.g., running @$ORACLE_HOME/rdbms/admin/sqltrpt.sql generates a SQL Tuning Advisor report, a licensable feature. In an audit, Oracle’s feature usage statistics would show that usage. The compliance issue arises from lack of training: organizations should educate their technical staff on which features are free and which require licensing. Oracle provides a Licensing Information manual for each version that delineates what is part of the Tuning Pack vs base database; those documents should be required reading for DBAs in licensed environments. Many companies also proactively disable or hide unlicensed features (for example, by removing certain Oracle scripts or using profiles to restrict access) to prevent accidents.
- Audit Findings of Historical Usage: Oracle’s auditing is sophisticated. They often ask customers to run Oracle’s feature usage tracking scripts, which can reveal if Tuning Pack features were used historically, even if not used recently. A common scenario is that you used a feature in the past (maybe before you knew it was licensable) and then stopped. The Automatic Workload Repository (AWR), part of the Diagnostics Pack, continuously collects data by default (unless disabled). If those AWR data or advisor repositories show evidence that, for example, a SQL Tuning Advisor task was executed or a SQL Profile exists (something only created by the Tuning Pack), Oracle will know that the Tuning Pack was used without a license. This can lead to compliance issues even if you’re not currently using the feature because unlicensed past usage is technically a violation. One real-world pitfall is the default automatic SQL tuning task that runs nightly in Oracle 11g and above – if that was not disabled and you had no Tuning Pack license, you’ve been unknowingly using the pack’s functionality. Companies should conduct internal audits to check for such usage and address it (either by licensing or disabling the features) before Oracle’s official auditors do. Oracle’s audit script will surface these details, so it’s better to first catch them.
Strict governance should be implemented over Oracle Database feature usage to avoid these pitfalls. Clear documentation of what packs are licensed for each database should be maintained. DBAs should be regularly communicated about the boundaries of usage. If a new tool or feature is needed for performance reasons, the impact of licensing should be properly evaluated.
It’s also wise to involve Oracle license specialists or resellers who can clarify gray areas. Compliance is not just about avoiding fees; it also ensures that your organization is fully entitled to get support for the features you use.
Detection of Tuning Pack Usage
Knowing whether the Tuning Pack features are being used in your databases is crucial for compliance management.
Oracle provides means to detect and audit the usage of optional packs like the Tuning Pack.
IT asset managers and DBAs can leverage these methods to ensure no unlicensed usage is occurring or to gather evidence for license true-ups.
Here are the primary ways to detect Tuning Pack usage:
- Oracle Feature Usage Statistics (Database View): Oracle databases track feature usage in an internal view called
DBA_FEATURE_USAGE_STATISTICS. This view records various features and options, whether they have been used, how many times, and the last usage date. You can query this view to see if features like “SQL Tuning Advisor”, “SQL Access Advisor”, “SQL Performance Analyzer”, “AWR Reports”, etc., have recorded usage. For example, sqlCopyEditSELECT name, detected_usages, last_usage_date FROM DBA_FEATURE_USAGE_STATISTICS WHERE name LIKE ‘%Tuning%’; This might show entries such as “SQL Tuning Advisor” with the number of times it was used. Oracle designed this view specifically to help customers and auditors track feature usage. Any non-zero usage count for a feature that falls under the Tuning Pack is a red flag if you haven’t licensed it. It’s good practice to periodically review this view. Remember that some features like ADDM or AWR (Diagnostics Pack) are closely linked; if you see those used, you likely have Tuning Pack usage since DBAs often use both in tandem. The view can sometimes have version-specific entries (so check the exact name Oracle uses for each feature in your DB version’s documentation or simply query to see what’s there). - Oracle’s License Audit Scripts: Oracle Support provides a utility script often referenced as
options_packs_usage_statistics.sql(available via My Oracle Support, Doc ID 1317265.1) that generates a comprehensive report of usage of all database options and management packs. This script queries various internal tables and views (including the one above and others like DBA_ADVISOR_USAGE) to produce a formatted report listing which features have been used and on what dates. It’s essentially the same script Oracle LMS (License Management Services) might ask you to run during an official audit. By running this script yourself proactively, you can see exactly what an auditor would see regarding Tuning Pack usage. The script output will flag things like usage of SQL Tuning Advisor, SQL Access Advisor, and so on. If you find any usage and know you have no corresponding license, that’s a situation to address immediately. Oracle has also introduced some features in newer versions to report option usage via Oracle Enterprise Manager or its Cloud Control repository queries. Still, as mentioned earlier, the script is the gold standard for on-premise audits. - Enterprise Manager (Management Pack Access Settings): If you use Oracle Enterprise Manager (OEM) to manage your databases, OEM has a feature to control and monitor pack usage. Under the Setup -> Management Pack Access page, you can see which packs are enabled or disabled for each monitored target database. Disabling access here doesn’t just hide the links; it actively prevents OEM from displaying data that would invoke those packs. This can prevent accidental usage through the GUI. Moreover, OEM’s repository can track which features have been used. For example, if a DBA tried to use the SQL Tuning Advisor in OEM, that action might be logged. While OEM is more about controlling access, it’s part of the detection arsenal that ensures unlicensed packs are turned off at the UI level. Suppose you discover that the Tuning Pack was left enabled in OEM for a database that wasn’t licensed. In that case, it’s wise to check if any of those pages were accessed (OEM has usage reports) and also double-check the database feature usage views as mentioned.
- Database Auditing of Package Calls: Sometimes, organizations set up audit triggers or logs on specific PL/SQL package usage. For instance, you could enable auditing to execute DBMS_SQLTUNE or DBMS_ADVISOR procedures. If someone invokes them, an audit record will be generated. This is a more technical and granular approach, but it can catch any direct use of the tuning APIs. Oracle doesn’t do this by default for you, but a security-conscious DBA team might implement it to catch unauthorized use of high-privilege packages. However, using the built-in feature usage views is usually sufficient and much simpler.
- Review of AWR and Advisor Repositories: The presence of certain data can indicate pack usage. For example, if you query the table
DBA_ADVISOR_TASKSand see tasks with names like “AUTOTASK_SQL_TUNING…,” the automatic SQL tuning task has run. Or if you see tasks for “SQL Access Advisor,” someone invoked it. Similarly,DBA_SQL_PROFILESit will list SQL profiles created by the Tuning Advisor – if that table has entries and you never licensed Tuning Pack, those profiles were generated illicitly from Oracle’s standpoint. Oracle’s audit script typically checks these for you, but you can manually inspect them.
In practice, the simplest method is to run the official Oracle options usage script or look at DBA_FEATURE_USAGE_STATISTICS. IT asset managers might not run these queries personally, but they should coordinate with Oracle DBAs to get regular reports (for example, quarterly) on feature usage for all Oracle instances. It’s a good compliance hygiene measure.
We recommend that companies regularly perform internal audits using Oracle’s tools to detect unlicensed usage. By catching it early, you can stop the usage or acquire the necessary licenses before it becomes a bigger issue (especially before a vendor audit).
This also helps in true-up budgeting—if you notice that developers need the Tuning Pack to do their jobs effectively, it might be worth purchasing it outright rather than forbidding its use.
Finally, note that Oracle’s auditing and detection are version-agnostic: whether you are running Oracle Database 11g, 12c, 19c, or newer, the same mechanisms (feature usage views and scripts) apply, though features tracked will correspond to what’s available in that version. Thus, managing Tuning Pack usage is ongoing throughout the database lifecycle.
