Check session "high water mark" for license compliance in Oracle 23c

In Oracle 23c, checking the session "high water mark" is still relevant, but less critical for license compliance than it used to be, especially with the introduction of Oracle's new licensing models and Autonomous Database services.
✅ Traditional On-Premises Oracle (Non-Autonomous)
If you are running Oracle 23c on-premises (non-cloud), particularly under a processor-based or Named User Plus (NUP) license model, it’s still important to:
🔍 Monitor:

• Concurrent sessions
• Maximum sessions reached (High Water Mark)

🔧 View it using:

SELECT resource_name, current_utilization, max_utilization
FROM v$resource_limit
WHERE resource_name = 'sessions';

• current_utilization: Number of sessions right now
• max_utilization: High water mark since instance start
• initial_allocation: Configured limit from sessions parameter

🔒 Why It Matters for License Compliance

• Oracle NUP licensing requires that you count the number of users who can connect, and monitor concurrent usage.
• If max_utilization of sessions is consistently high, you may be exceeding your licensed limits or need more resources.

☁️ In Oracle Autonomous/Cloud Editions

• Oracle Autonomous Database (on Oracle Cloud) handles licensing based on OCPUs, not sessions.
• You don’t need to monitor session high water marks for compliance, but it can still help with performance diagnostics and scaling decisions.

💡 Summary

Environment	Is Session HWM Relevant for Licensing?	Notes
Oracle 23c On-Premises	✅ Yes	Important for NUP/Processor compliance
Oracle Cloud (Autonomous)	❌ No	Sessions are managed internally; focus is on OCPUs

Here is a complete solution to track Oracle session high water marks historically in Oracle 23c (or earlier versions), using:

1. A custom tracking table
2. A PL/SQL procedure to insert records
3. An optional scheduler job to run it periodically

✅ Step 1: Create the Historical Tracking Table

CREATE TABLE session_hwm_history (
    record_time      TIMESTAMP DEFAULT SYSTIMESTAMP,
    current_sessions NUMBER,
    max_sessions     NUMBER,
    session_limit    NUMBER
);

✅ Step 2: Create a Procedure to Capture High Water Mark

CREATE OR REPLACE PROCEDURE capture_session_hwm IS
    v_current   NUMBER;
    v_max       NUMBER;
    v_limit     NUMBER;
BEGIN
    SELECT 
        MAX(CASE WHEN resource_name = 'sessions' THEN current_utilization END),
        MAX(CASE WHEN resource_name = 'sessions' THEN max_utilization END),
        MAX(CASE WHEN resource_name = 'sessions' THEN limit_value END)
    INTO v_current, v_max, v_limit
    FROM v$resource_limit;

    INSERT INTO session_hwm_history (current_sessions, max_sessions, session_limit)
    VALUES (v_current, v_max, TO_NUMBER(v_limit));
    
    COMMIT;
END;
/

✅ Step 3 (Optional): Schedule the Procedure Periodically

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'track_session_hwm_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN capture_session_hwm; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=HOURLY',  -- You can adjust this
    enabled         => TRUE,
    comments        => 'Track session high water mark every hour'
  );
END;
/

SELECT * FROM session_hwm_history
ORDER BY record_time DESC;

🔐 Security Note:
Make sure the user executing this script has access to `v$resource_limit`. If not, grant the privilege:

GRANT SELECT ON v_$resource_limit TO your_user;

As of Oracle Database 12c, "Oracle no longer uses the session "high water mark" to check for license compliance". The session "high water mark," which represents the maximum number of concurrent sessions observed at any point in time, was traditionally a key metric for enforcing licensing limits in earlier Oracle versions, particularly for older license models tied to session usage.
Key Changes in Oracle Licensing:

1. Licensing Enforcement Shift:
   • Starting with Oracle 12c, Oracle moved away from relying on the session "high water mark" as a direct licensing compliance mechanism.
   • The licensing model became more aligned with processor-based and named-user metrics, rather than monitoring the peak number of concurrent sessions.
2. Focus on Audits and Contracts:
   • Licensing compliance is now primarily determined by Oracle's license audits and contractual agreements. Oracle expects customers to monitor their own usage to remain compliant with the agreed terms.
3. Concurrent Session Tracking for Monitoring Only:
   • While Oracle still tracks the session "high water mark" in views like V$LICENSE, it is for informational purposes rather than enforcing license restrictions.
   
   You can view the session "high water mark" in Oracle 12c using:
   

         SELECT sessions_highwater
         FROM v$license;
       

4. License Models in Oracle 12c:
   • Processor-Based Licensing: Based on the number of CPUs or cores.
   • Named User Plus Licensing: Based on the number of unique users accessing the database.
   • Cloud Licensing Models (BYOL): These are usage-based models applicable to Oracle Cloud services.

Why the Change?

1. Flexibility for Cloud and Multi-Tenant Architectures: Oracle 12c introduced multi-tenant architecture and cloud capabilities, which made session-based licensing impractical. Customers often have fluctuating workloads with unpredictable session peaks, especially in shared environments.
2. Encouraging Scalability: Session-based enforcement discouraged customers from scaling their systems freely. By removing this restriction, Oracle allows more flexibility in workload management.

Best Practices for Compliance:

• Monitor your named users and processor usage instead of focusing on session counts.
• Keep track of your database usage metrics, as Oracle can request this information during an audit.
• Use tools like Oracle Enterprise Manager (OEM) or third-party tools to help with usage tracking.

Many, but not all, Oracle licenses specify a maximum number for concurrent users accessing a database. For example, I have a client with a 220 concurrent user license. That means that client can have up to 220 user sessions open at any one time. This limit was negotiated when the client purchased the license and was recorded as part of its contract with Oracle. However, it's up to the DBA to configure the software to enforce that limit. It's also up to the DBA to monitor compliance. You can restrict the number of concurrent sessions allowed for a database by setting the license_max_sessions initialization parameter. To allow 220 sessions, for example, you would place the following line in your database initialization file:

license_max_sessions = 220

If you are enforcing a limit on concurrent sessions, you may want to check to see how many you are using periodically. Oracle helps you do that by recording a license high water mark in the alert log each time the database is shut down. Here's what that looks like:

Sat Jun 19 22:22:41 1999
Shutting down instance (normal)

License high water mark = 180

• License High Water Mark:
  The license high water mark represents the maximum number of concurrent sessions that have ever been active all at once since the database was last started. The example shown here indicates that there was a maximum of 180 users connected at one time. If you notice that the license high water mark is consistently near the limit of your license, you may want to consider increasing the number of concurrent users on your license.
  In the next lesson, you will list free space in a tablespace.

Click the Quiz link below to answer a few questions about the alert log.
Checking License Compliance - Quiz