Oracle Database Session Control: RESOURCE_LIMIT, CONNECT_TIME, and IDLE

Oracle Database Session Control: RESOURCE_LIMIT, CONNECT_TIME, and IDLE_TIME

Overview

Oracle provides powerful mechanisms to control user sessions and enforce resource limits through profiles and initialization parameters. Key among these are:

RESOURCE_LIMIT: A database-level setting to enforce profile resource limits.
CONNECT_TIME: Maximum time (in minutes) a user session can remain connected.
IDLE_TIME: Maximum time (in minutes) a user session can remain idle without activity.

`RESOURCE_LIMIT` Parameter

Purpose:

RESOURCE_LIMIT enables or disables the enforcement of profile limits defined for database users.

Characteristics:

Attribute	Description
Parameter Type	Initialization parameter
Default Value	`FALSE`
Modifiable	`ALTER SYSTEM` (dynamic)
Scope	Instance
Affected Feature	All resource limits in profiles

When `TRUE`:

Oracle will enforce all limits such as CONNECT_TIME, IDLE_TIME, SESSIONS_PER_USER, etc.
Sessions that violate the defined thresholds will be terminated automatically.

How to Check and Set:


-- Check current setting
SHOW PARAMETER resource_limit;

-- Enable resource limits
ALTER SYSTEM SET resource_limit = TRUE;

`CONNECT_TIME` in Profiles

Purpose:

Defines the maximum allowed connection time (in minutes) for a user session.

Behavior:

If a session exceeds CONNECT_TIME, Oracle disconnects the session.
Useful to limit session duration for temporary or shared user accounts.

Example:


-- Set connect time limit to 60 minutes
ALTER PROFILE DEFAULT LIMIT CONNECT_TIME 60;

`IDLE_TIME` in Profiles

Purpose:

Specifies the maximum allowed idle time (in minutes) — time without any user interaction.

Behavior:

Oracle terminates sessions that remain idle beyond the specified duration.
Helps in automatically cleaning up inactive sessions and preserving resources.

Example:


-- Set idle time limit to 30 minutes
ALTER PROFILE DEFAULT LIMIT IDLE_TIME 30;

🔔 Sessions running queries or background jobs may not be truly idle. Use caution when applying IDLE_TIME to such users.

5. Enabling Limits - Combined Usage

To activate these limits, both the profile limits must be set and RESOURCE_LIMIT must be enabled:


ALTER PROFILE DEFAULT LIMIT
  CONNECT_TIME 60
  IDLE_TIME 30;

ALTER SYSTEM SET resource_limit = TRUE;

6. Monitoring and Managing Profiles

View Profile Assignment:


SELECT username, profile FROM dba_users;

View All Limits for a Profile:


SELECT resource_name, limit
FROM dba_profiles
WHERE profile = 'DEFAULT';  -- or a custom profile name

Best Practices

Recommendation	Reason
Use `RESOURCE_LIMIT = TRUE`	To enforce profile-based restrictions
Set appropriate `CONNECT_TIME`	For environments with shared access or compliance requirements
Use `IDLE_TIME` with caution	May disrupt long-running queries or scheduled jobs
Create custom profiles	Assign tailored limits to different user groups
Monitor logs and alerts	To track session kills due to resource constraints

Real-world Use Case

Scenario: A training environment where users often forget to log out.

Solution:

Set CONNECT_TIME = 90 to limit session duration.
Set IDLE_TIME = 30 to disconnect inactive sessions.
Set RESOURCE_LIMIT = TRUE to enforce these settings.

Summary Table

Parameter	Type	Enforced When `RESOURCE_LIMIT=TRUE`	Action
`RESOURCE_LIMIT`	System Init	N/A	Enables enforcement of limits
`CONNECT_TIME`	Profile	Yes	Ends session after X minutes
`IDLE_TIME`	Profile	Yes	Ends idle session after X minutes

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Oracle Database Session Control: RESOURCE_LIMIT, CONNECT_TIME, and IDLE_TIME