ALTER PROFILE

Edit the resource limits associated with a user profile

Syntax:

   ALTER PROFILE profile_name LIMIT limit(s) range

KEY
   limit  =  SESSIONS_PER_USER
             CPU_PER_SESSION
             CPU_PER_CALL
             CONNECT_TIME
             IDLE_TIME
             LOGICAL_READS_PER_SESSION
             LOGICAL_READS_PER_CALL
             COMPOSITE_LIMIT
             PRIVATE_SGA

   range  =  UNLIMITED | DEFAULT | integer

Where PRIVATE_SGA is set to an integer - specify K or M
e.g.
ALTER PROFILE profile_name LIMIT PRIVATE_SGA 50 K

New with Oracle 8 are password related profile limits...

Syntax:

   ALTER PROFILE profile_name LIMIT pw_limit(s) range

KEY
  pw_limit = PASSWORD_LIFE_TIME
             PASSWORD_GRACE_TIME
             PASSWORD_REUSE_TIME
             PASSWORD_REUSE_MAX
             FAILED_LOGIN_ATTEMPS
             PASSWORD_LOCK_TIME

   range  =  UNLIMITED | DEFAULT | expression

Syntax to customise password verification:

   ALTER PROFILE profile_name LIMIT PASSWORD_VERIFY_FUNCTION {plsql_function | NULL | DEFAULT}

Definitions
   CONNECT_TIME - Max. time user may stay connected 
   IDLE_TIME    - Max. time user may stay connected & idle
   PRIVATE_SGA  - Session space in the shared pool - K or M (bytes)
   COMPOSITE_LIMIT - A weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA. 
   PASSWORD_LIFE_TIME  - Expire password after X no of days
   PASSWORD_GRACE_TIME - Lock account X days after LIFE_TIME expires.
   PASSWORD_REUSE_TIME - Min. no. days before the same pw may be reused
   PASSWORD_REUSE_MAX  - Min. no. of number of pw changes before the current password can be reused
   FAILED_LOGIN_ATTEMPS - Max no. of incorrect logins before account is locked
   PASSWORD_LOCK_TIME  - Max. no. of days an account will be locked 

DEFAULT refers to values set in the DEFAULT user profile.

"Good people do not need laws to tell them to act responsibly, while bad people will find a way around the laws." - Plato (427-347 B.C.)

Related Commands:

PROFILE - CREATE PROFILE
PROFILE - DROP PROFILE
ROLE - SET ROLE
ROLE - ALTER ROLE

Related Views:

 DBA_PROFILES
 DBA_SYS_PRIVS                             USER_SYS_PRIVS       ROLE_SYS_PRIVS 

 V$SESSION
 V$SESSION_CONNECT_INFO
 V$SESSTAT
 V$SESS_IO



Back to the Top

Simon Sheppard
SS64.com