Create a profile to limit and manage the user resources in Oracle

You can create a database profile to manage database resource usage. In Oracle, if you create a user without specifying a profile, the default profile is assigned. You can also create your own profile and assign it to a user.

Managing User Resources with Oracle Profiles

Check the profiles present in the database:

SELECT * FROM DBA_PROFILES;

Check the users and profiles assigned to them.

SELECT USERNAME, USER_ID, PROFILE FROM DBA_USERS;

Create a new profile with changing IDLE_TIME to 60 seconds.

CREATE PROFILE PROFILE_TEST LIMIT IDLE_TIME 60;

ALTER profile like make IDLE_TIME from 60 to 90

ALTER PROFILE PROFILE_TEST LIMIT IDLE_TIME 90;

Assign the new profile to specific user:

ALTER USER HR PROFILE PROFILE_TEST;

For make the profile to work, Need to set the following parameter:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE SCOPE=BOTH;

Otherwise, it will not work.

Verify the Profile is assigned to user:

SELECT USERNAME, USER_ID, PROFILE FROM DBA_USERS where username='HR';