Create Profile in Oracle

Posted by Sapna Malik Articles | DB Administration March 11, 2011
We will create profile in Oracle enterprise manager and through the command.
Reader Level:

The profile is a database object and a named set of resource limit. The profile manage the account status, place limitations on users and password set. The profile impose a named set of resource limits on database usage and instance resources. We can set some hardware and software status. The profile are used to manage the resource limits of related users.


Create profile profile_name
limit ....................


SQL> Create profile TestProf
2 limit
3 sessions_per_user 2
4 cpu_per_session 10000
5 cpu_per_call 1
6 connect_time unlimited
7 idle_time 30
8 logical_reads_per_session default
9 logical_reads_per_call default
10 private_sga 20M
11 failed_login_attempts 3
12 password_life_time 30
13 password_reuse_time 12
14 password_reuse_max unlimited
15 password_lock_time default
16 password_grace_time 2
17 password_verify_function null;


We will explain one by one hardware and software limitation. These limitation by default set but you can set according your requirements.  

SESSIONS_PER_USER: We can set the maximum number of concurrent sessions for user.

CPU_PER_SESSION: We can set the CPU time limit for a session, expressed in hundredth of seconds.

CPU_PER_CALL: We can set the CPU time limit for a call, expressed in hundredths of seconds.

CONNECT_TIME: We can set the total connect time limit for a session, expressed in minutes.

IDLE_TIME: We can set maximum idle time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.

LOGICAL_READS_PER_SESSION: We can set the permitted number of data blocks read in a session, including blocks read from memory and disk.

LOGICAL_READS_PER_CALL: We can set the permitted the number of data blocks read for a call to process a SQL statement.

PRIVATE_SGA: We can set the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.

COMPOSITE_LIMIT:We can set the total resource cost for a session, expressed in service units.

FAILED_LOGIN_ATTEMPTS: We can set maximum failed login attempts.

PASSWORD_LIFE_TIME: We can set valid password time.

PASSWORD_REUSE_MAX: We can set minimum of different passwords before password can be reused.

PASSWORD_REUSE_TIME: We can set minimum of days before a password can be reused.

PASSWORD_LOCK_TIME: We can set number of days an account is locked after failing to login.

PASSWORD_VERIFY_FUNCTION: We can set verify function for passwords.

We will create profile in Oracle enterprise manager.

First of all, we will go to Start=>All Programs=> Oracle-OraDb10g_home1=> Database Control-OMFDATA and specifying the system username and password after that we will click Login button.

The home page is displaying. After that we will click Administrationtab.



The Administration page is displaying. We will click Profiles option under the Users & Privilege.



The Profiles page is displaying.  We will click Create button.



The Create Profile page is displaying. We will enter the Name of the profile, CPU details and Database services details. We will click Password tab.



The Password page is displaying. This is dived into four part password, history, complexity and failed login. We can set all option otherwise by default set. You want to see SQL query then click Show SQL button.



The Show SQL page is displaying. We will click Return button for go to create profile page and click the OK button.



The Update Message page is displaying. This page give the message "The object has been created successfully".



I hope this article help you.

Login to add your contents and source code to this article
This Feature is Sponsored By
post comment