Session License Limits (vlicense.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides Oracle license and session limit information by:

• Displaying maximum session limits configured
• Showing current session usage
• Indicating warning thresholds
• Tracking high-water marks for capacity planning
• Reporting user license limits

Script

rem  vlicense.sql
rem
ttitle 'Sessions Summary'
rem
col sessions_max heading 'MAX|SESSIONS'
col sessions_warning heading 'WARNING|SESSIONS'
col sessions_current heading 'CURRENT|SESSIONS'
col sessions_highwater heading 'HIGHWATER|SESSIONS'
col users_max heading 'MAX|USERS'
rem
select sessions_max,
       sessions_warning,
       sessions_current,
       sessions_highwater,
       users_max
  from v$license;

Usage

SQL> @vlicense.sql

Required Privileges

• SELECT on V$LICENSE

Sample Output

Sessions Summary

      MAX   WARNING   CURRENT HIGHWATER       MAX
 SESSIONS  SESSIONS  SESSIONS  SESSIONS     USERS
--------- --------- --------- --------- ---------
      472       378       156       245         0

Key Output Columns

• MAX SESSIONS: Maximum number of concurrent sessions allowed
• WARNING SESSIONS: Warning threshold for session count
• CURRENT SESSIONS: Current number of active sessions
• HIGHWATER SESSIONS: Highest number of sessions reached since startup
• MAX USERS: Maximum named users (0 = unlimited)

Understanding Session Limits

Session Parameters

• SESSIONS: Derived from PROCESSES parameter
• PROCESSES: Maximum number of OS processes
• Calculation: SESSIONS = (1.1 * PROCESSES) + 5

Warning Levels

• Warning Threshold: Usually 80% of maximum sessions
• Critical Level: Approaching maximum sessions
• Capacity Planning: Use highwater mark for planning

Common Use Cases

1. Capacity Monitoring

   • Monitor current session usage
   • Track peak session counts
   • Plan for growth

2. License Compliance

   • Verify license limits
   • Monitor user connections
   • Ensure compliance with Oracle licensing

3. Performance Analysis

   • Correlate session counts with performance
   • Identify peak usage periods
   • Plan connection pooling

Session Management

Check Current Sessions

-- Count active sessions by type
SELECT status, type, COUNT(*) session_count
FROM v$session
GROUP BY status, type
ORDER BY status, type;

-- Find user sessions
SELECT username, COUNT(*) session_count
FROM v$session
WHERE username IS NOT NULL
GROUP BY username
ORDER BY session_count DESC;

Session Parameters

-- Check related parameters
SELECT name, value, description
FROM v$parameter
WHERE name IN ('processes','sessions','license_max_sessions',
               'license_sessions_warning','license_max_users')
ORDER BY name;

Warning Scenarios

Approaching Limits

• Current > 80% of Max: Monitor closely
• Current > 90% of Max: Consider action
• Current = Max: New connections will fail

High Water Analysis

• Growing Trend: May need parameter increase
• Stable Pattern: Normal operational range
• Spikes: Investigate application behavior

Tuning Recommendations

Increase Session Limits

-- Increase PROCESSES (requires restart)
ALTER SYSTEM SET processes=600 SCOPE=SPFILE;
-- SESSIONS will be calculated automatically

-- Check calculated SESSIONS value
SELECT name, value
FROM v$parameter
WHERE name IN ('processes','sessions');

Connection Management

1. Connection Pooling

   • Implement application connection pools
   • Reuse connections efficiently
   • Configure appropriate pool sizes

2. Session Cleanup

   • Kill idle sessions if necessary
   • Implement connection timeouts
   • Monitor long-running sessions

License Considerations

Named User Licensing

• MAX USERS: Set based on license agreement
• Concurrent Sessions: May exceed named users
• Compliance: Ensure proper licensing

Processor Licensing

• Unlimited Users: MAX USERS = 0
• Session Limits: Based on hardware capacity
• No User Restrictions: Focus on performance

Monitoring Alerts

Session Threshold Alerts

-- Check if approaching limits
SELECT
  CASE
    WHEN sessions_current > sessions_max * 0.9 THEN 'CRITICAL'
    WHEN sessions_current > sessions_warning THEN 'WARNING'
    ELSE 'OK'
  END AS status,
  sessions_current,
  sessions_max,
  ROUND(sessions_current/sessions_max*100,1) AS pct_used
FROM v$license;

Historical Tracking

-- Create monitoring table
CREATE TABLE session_usage_history (
  check_time DATE,
  current_sessions NUMBER,
  max_sessions NUMBER,
  highwater_sessions NUMBER
);

-- Insert current values
INSERT INTO session_usage_history
SELECT SYSDATE, sessions_current, sessions_max, sessions_highwater
FROM v$license;

Troubleshooting

ORA-00020: Maximum Number of Processes Exceeded

• Check current process usage
• Increase PROCESSES parameter
• Restart database instance

High Session Usage

1. Identify Heavy Users

   • Find users with many sessions
   • Check for connection leaks
   • Review application design

2. Session Analysis

   • Check for idle sessions
   • Monitor session duration
   • Implement session timeouts

Related Scripts

• Active Session Analysis
• User Session Analysis
• Process Information