Understanding Infrastructure Resource Profiles in SQL*Plus

Infrastructure Resource Profiles control how CPU, I/O, and session resources are allocated in an Oracle database. They define usage limits, enforce policies, and ensure that critical workloads run without being starved by less important processes. Managing these profiles through sqlplus is direct, fast, and scriptable.

Listing Existing Profiles

To see what exists:

SELECT * FROM DBA_PROFILES;

This query reveals every configured profile, the resource names, and their limits. Common resources include SESSIONS_PER_USER, CPU_PER_SESSION, and CONNECT_TIME.

Creating a New Profile

Example creation:

CREATE PROFILE high_priority LIMIT
SESSIONS_PER_USER 3
CPU_PER_SESSION 1000
CONNECT_TIME 60;

Here, high_priority users can run up to three sessions, have 1000 CPU units per session, and a maximum of 60 minutes connection time.

Assigning a Profile to a User

ALTER USER analytics_team PROFILE high_priority;

This ensures analytics work gets defined CPU and time allocation, preventing uncontrolled growth in resource consumption.

Modifying an Existing Profile

Direct changes avoid downtime:

ALTER PROFILE high_priority LIMIT CPU_PER_SESSION 2000;

The new limit takes effect immediately.

Dropping a Profile

Only do this after confirming no critical accounts depend on it:

DROP PROFILE high_priority CASCADE;

The CASCADE keyword reassigns users of this profile to the default.

Best Practices

• Audit DBA_PROFILES regularly.
• Keep default profiles strict and specialized profiles tailored.
• Use SQL scripts for repeatable configuration via sqlplus.
• Document every change.

Infrastructure Resource Profiles in SQL*Plus give you control over the database’s allocation engine. They are the fine-tuning layer between hardware capability and application demand. Once you master this control, stability improves, bottlenecks fade, costs drop.

See this in action with real database resource control at hoop.dev — try it and watch it live in minutes.