Manage Password and Resources in Oracle using Profile

In this article, you will learn how to manage password and resources in oracle using profile.

Why we should use Profiles in Oracle?

Profiles in Oracle helps us to manage password and restrict resource consumption.

In this tutorial of ‘profiles in Oracle’, we are going to learn session management in Oracle, resource management in Oracle and password management in Oracle with the help of parameters available in profile.  Herein we have tried to cover almost all parameters in profiles in Oracle. Hope this will help you.

Purpose of Profile in Oracle

To restrict resource consumption and allows to manage password.

  • One profile can be assigned to multiple users.
  • One user can have one profile at a time
  • Only DBA can create a profile.
  • When we alter users profile, it will be applicable from next logged in session, not from the current  or previous  session
  • View to check details about the profile in Oracle:- dba_profiles  and  dba_users

How to create Profile in Oracle/Command to create a profile in Oracle?

Below command will create a profile with the name ‘profile_test1’  which defines that user can open only 2 concurrent sessions at a time also it specifies ideal_time as 15 minutes, it means if a session is ideal for more than 15 minutes then that session will automatically log off.   It also specifies connect time as 120 minutes which means, the session can remain connected to the database for maximum 120 minutes after that session will automatically log off.

How to check profile allocated to the user in Oracle

How to assign created profile to a user in Oracle

How to drop a profile in Oracle

Resource management in Oracle using profile parameters


It specifies the total time in minutes a session may remain connected to the database. After specified minutes a session logs off.


It limits the CPU time used by each call within a transaction. It includes parsing, executes and fetch. This much of time one statement can use at max. If any statement uses more CPU it will be failed.


It limits CPU total time used during a session.


It specifies the number of concurrent sessions that can be opened by users.

If the value for sessions_per_user = 2 then that user can open 2 sessions simultaneously.


It limits the amount of time the session is idle. If idle_tile is 15 minutes and the session is idle for 15 minutes, the session automatically logs off.


It limits a total number of data blocks read(from SGA+DIsk read) per session.


It limits the total number of data blocks reads per statement.


Its an overall limit of all the above parameters in terms of the service unit.

User password management in Oracle using profile parameters


It specifies the number of time the user can enter the wrong password.


It specifies the number of days the password should be valid. If we don’t change the password within these days then the password will expire.


When the password expires within the next password_grace_time days you can change the password.


If failed_login_attempts is set to 3, 4th-time account is locked. Then account should be locked for password_lock_time days.  After password_lock_time days account will be unlocked.


The definition for password_reuse_time says that same password cannot be used again till password_reuse_time days.


It specifies the number of times we can use the same password again.


This parameter is inbuild provided by Oracle to impose restriction while setting a password. Value is verify_function_11g.

Please check below example to understand the –

If the above function is not available then we need to run below password manager function
We need to below for resource parameter to take in effect.

Related Articles

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.


Adblock Detected

Please consider supporting us by disabling your ad blocker
%d bloggers like this: