A database user can accumulate open sessions even when no SQL is running. A profile gives the account a named set of limits. One local account can be followed from the profile definition through a fresh session and idle expiry. These examples use Oracle Database 19c and a local profile in one application PDB. The numbers illustrate what each limit means.
Oracle DBA Lesson 75 Part 1 — Limit Sessions with a User Profile
Define the policy
The account APP_INTERACTIVE needs one interactive connection at a time. Its profile is:
CREATE PROFILE interactive_limits LIMIT
SESSIONS_PER_USER 1
IDLE_TIME 10
CONNECT_TIME 60;| Parameter | Example | Interpretation |
|---|---|---|
SESSIONS_PER_USER | 1 | Maximum concurrent sessions for the user |
IDLE_TIME | 10 | Continuous inactivity between calls, in minutes |
CONNECT_TIME | 60 | Total elapsed connection time, in minutes, including busy time |
A long-running operation is active work and does not count as idle time for this limit. CONNECT_TIME measures elapsed time, rather than processor consumption. Resource limits also exist for calls, CPU consumption, and logical reads. Those limits use their own units. See CREATE PROFILE.
A user has one profile. Assigning a replacement changes the user's reference. A profile can be shared by several users. It contains both resource and password parameters. Keep the account's password requirements in view when designing a session policy. See user profiles.
Assign the profile
Creating the profile and assigning it requires CREATE PROFILE and ALTER USER. The dictionary checks below use DBA_USERS and DBA_PROFILES.
ALTER USER app_interactive PROFILE interactive_limits;
SELECT username, profile
FROM dba_users
WHERE username = 'APP_INTERACTIVE';| USERNAME | PROFILE |
|---|---|
| APP_INTERACTIVE | INTERACTIVE_LIMITS |
The row establishes the account-to-profile relationship. Assignment changes and ALTER PROFILE resource changes apply to subsequent sessions. An existing session still runs with the settings it had when it connected. Reconnect before you rely on the new profile. See ALTER USER, DBA_USERS, and ALTER PROFILE.
Read the three resource values:
SELECT resource_name, limit
FROM dba_profiles
WHERE profile = 'INTERACTIVE_LIMITS'
AND resource_type = 'KERNEL'
AND resource_name IN ('SESSIONS_PER_USER',
'IDLE_TIME','CONNECT_TIME');Row order is unspecified:
| RESOURCE_NAME | LIMIT |
|---|---|
| SESSIONS_PER_USER | 1 |
| IDLE_TIME | 10 |
| CONNECT_TIME | 60 |
RESOURCE_TYPE = 'KERNEL' selects resource parameters. PASSWORD selects password parameters. LIMIT is the dictionary's character representation of the setting. These rows show configuration. They do not prove enforcement. See DBA_PROFILES.
Read all settings, including inherited values:
SELECT profile, resource_type, resource_name, limit
FROM dba_profiles
WHERE profile IN ('INTERACTIVE_LIMITS','DEFAULT')
ORDER BY resource_type, resource_name, profile;DEFAULT means that parameter is inherited from the DEFAULT profile. An omitted parameter also inherits. The short CREATE PROFILE statement therefore inherits every password setting and every unspecified resource setting. Review the effective policy before you replace an existing profile. A later change to DEFAULT can affect profiles that depend on that value. Explicit values in another profile keep their own settings. See DEFAULT profile semantics.
Confirm enforcement
In SQL*Plus, read the setting in the same container:
SHOW PARAMETER resource_limit| Setting | Profile resource behavior |
|---|---|
TRUE | Resource-limit enforcement enabled |
FALSE | Limits remain defined; resource enforcement disabled |
Oracle 19c documents a default of TRUE. The parameter is modifiable in a PDB. Read the actual setting in that PDB. A change applies to resource enforcement for accounts in that scope. See RESOURCE_LIMIT.
Password controls are enforced independently of this parameter. Local and common accounts and profiles also follow container-specific rules. This lesson uses a local account and a local profile in one PDB. See resource enforcement and profiles.
Session and time-limit behavior
With enforcement enabled, fresh sessions for the account show the concurrent cap:
- Open session 1 and keep it connected.
- Attempt session 2 with the same account and container. When the cap of one is already in use, the additional connection is rejected.
- Close session 1 normally. A later connection can use the released capacity.
An inactive session still occupies a session slot while it is retained. Idle cleanup can delay the release of that capacity. A different error can mean authentication, account status, or service selection stopped the connection before the session limit applied. See concurrent sessions and cleanup.
For idle expiry, separate the configured threshold, server enforcement, and what the client observes. Oracle checks idle time and connection time periodically. Expiry can therefore occur after the stated threshold rather than at an exact second. When the limit is enforced, the current transaction is rolled back and the session ends. The client receives the error on its next database call. A prompt that remains on screen does not show that the session is still usable. See time-limit behavior.
Session state for the account:
SELECT sid, serial#, username, status
FROM v$session
WHERE username = 'APP_INTERACTIVE';SNIPED identifies an inactive session that exceeded an applicable limit and cannot become active again. The row can remain visible during cleanup. INACTIVE describes inactivity. It is not proof of expiry. Watch the status over time and issue the next client call. See V$SESSION.
A connection pool can hold or reuse a session beyond one application request, and it may issue keepalive calls. A held session still occupies a session slot. A profile does not assign a percentage of CPU to a workload. Oracle recommends Database Resource Manager when the requirement is more flexible resource control. See resource-control guidance.
Remove an unused profile
A profile that is still assigned to a user cannot be dropped unless CASCADE is specified. CASCADE reassigns those users to DEFAULT, and that reassignment can change their resource and password policy. Reassign the account to its previous profile, confirm that no user remains assigned, and then drop the unused profile. In the statement below, original_profile stands for the profile the account had before this change. It is not a literal profile named ORIGINAL_PROFILE.
-- Replace ORIGINAL_PROFILE with the value recorded before testing.
ALTER USER app_interactive PROFILE original_profile;
DROP PROFILE interactive_limits;See DROP PROFILE.
No comments:
Post a Comment