Oracle All about profile

Oracle All about profile 1. Purpose: The profile in the Oracle system can be used to restrict the database resources that users can use. Use the Create Profile command to Create a Profile, it is used to restrict the use of database resources. If the profile is assigned to the user, all the database resources that the user can use are within the limit of the profile. 2. condition: You must have the system permission to CREATE a profile. Specify resource limits for users. Required: 1. dynamically use alter system or use the initialization parameter resource_limit to make the resource limits take effect. This change is invalid for password resources and password resources are always available. SQL> show parameter resource_limitNAME type value -------------------------------------- resource_limit boolean false SQL> alter system set resource_limit = true. SQL> show parameter resource_limit; NAME TYPE VALUE -------------------------------------------------------------------- resource_limit boolean TRUE 2. create a profile using create profile to define a profile that limits database resources. 3. Use the create user or alter user command to allocate the profile to the user. Iii. Syntax: create profile profileLIMIT {resource_parameters | password_parameters} [resource_parameters | password_parameters]...; <resource_parameters> {SESSIONS_PER_USER | CPU_PER_SESSION | CPU_PER_CALL | CONNECT_TIME | IDLE_TIME | average | percent | integer} {integer | UNLIMITED | DEFAULT} | PRIVATE_SGA {integer [K | M] | limituned DEFAULT }}< Password_parameters >{{ parameters | PASSWORD_LIFE_TIME | PASSWORD_REUSE_TIME | expiration | PASSWORD_LOCK_TIME | PASSWORD_GRACE_TIME} {expr | UNLIMITED | DEFAULT} | functions {function | NULL | DEFAULT} IV. Syntax explanation: profile: the name of the configuration file. Oracle Database forces resource restrictions in the following ways: 1. If the user exceeds the session resource limit of connect_time or idle_time, the database rolls back the current transaction and ends the session. When you run the command again, the database returns an error. 2. If you attempt to perform operations that exceed other session resource limits, the database will discard the operation, roll back the current transaction, and immediately return an error. After that, you can submit or roll back the current transaction. You must end the session. Tip: You can divide a row into multiple segments, such as one hour (1/24 days) to limit the time. You can specify resource restrictions for users, but the database will only execute the restrictions after the parameters take effect. Unlimited: the user who assigns the profile has no limit on the resource usage. When the password parameter is used, unlimited means that there is no limit on the parameter. Default: if it is set to default, some resource restrictions on the profile are ignored. The initial definition of the Default profile is not limited to resources and can be changed through the alter profile command. Resource_parameter part: Session_per_user: specifies the number of concurrent sessions of a user. Cpu_per_session: Specifies the CPU time limit of the session, in the unit of 1% seconds. Cpu_per_call: Specifies the CPU time limit for one call (resolution, execution, and extraction), in 1% seconds. Connect_time: Specifies the total connection time of a session, in minutes. Idle_time: Specifies the total time for a session to allow continuous inactivity, in minutes. If the duration is exceeded, the session will be disconnected. However, long-running queries and other operations are not restricted. SYS @ orcl> alter profile default limit IDLE_TIME 1; Profile altered. SYS @ orcl> alter system set resource_limit = true; System altered. at 00:24:57 SYS @ orcl> select sid, serial #, status from v $ session; sid serial # STATUS ------- ---------- 1 5 ACTIVE 2 1 ACTIVE 3 1 ACTIVE 4 1 ACTIVE 5 1 ACTIVE 6 1 ACTIVE 7 1 ACTIVE 8 1 ACTIVE 9 1 ACTIVE 10 1 ACTIVE 11 1 ACTIVE 12 1 ACTIVE 13 1 ACTIVE 14 1 ACTI VE 15 1 ACTIVE 16 1 ACTIVE 17 7 ACTIVE 18 5 ACTIVE 20 2 ACTIVE 21 1 ACTIVE 24 1 ACTIVE 25 5 ACTIVE 28 4 ACTIVE 31 1 ACTIVE 35 167 <span style = "background- color: rgb (255,255, 0 ); "> INACTIVE </span> 37 31 ACTIVE 43 104 ACTIVE 44 11 ACTIVE 47 94 INACTIVE 00:24:58 SYS @ orcl>/sid serial # STATUS ------- ---------- -------- 1 5 ACTIVE 2 1 ACTIVE 3 1 ACTIVE 4 1 ACTIVE 5 1 ACTIVE 6 1 ACTIVE 7 1 ACTIVE 8 1 ACTIVE 9 1 ACTIVE 10 1 ACTIVE 11 1 ACTIVE 12 1 ACTIVE 13 1 ACTIVE 14 1 ACTIVE 15 1 ACTIVE 16 1 ACTIVE 17 7 ACTIVE 18 5 ACTIVE 20 2 ACTIVE 21 1 ACTIVE 24 1 ACTIVE 25 5 ACTIVE 28 4 ACTIVE 31 1 ACTIVE 35 167 <span style = "background-color: rgb (255,255, 0); "> SNIPED </span> 37 31 ACTIVE 43 104 ACTIVE 44 11 ACTIVE 47 94 INACTIVE [oracle @ rhel5 ~] $ Sqlplus scott/tiger SCOTT @ orcl> select distinct sid from v $ mystat; SID ---------- 35 SCOTT @ orcl>/select distinct sid from v $ mystat * ERROR at line 1: ORA-02396: exceeded maximum idle time, please connect again Logical_reads_per_session: specify the number of data blocks that a session can read, including all data blocks read from memory and disk. Logical_read_per_call: specify the maximum number of data blocks that can be read when an SQL statement (parsing, execution, and extraction) call is executed. Private_sga: specify the maximum space that a session can allocate in the Shared Pool (SGA), in bytes. (This restriction is only valid when the Shared Server structure is used. The private space of a session in SGA includes private SQL and PL/SQL, but not shared SQL and PL/SQL ). Composite_limit: Specifies the total resource consumption of a session, in the unit of service units. The Oracle Database calculates the maximum number of attempts allowed to log on to cpu_per_session, connect_time, logical_reads_per_session, and private-sga service units Password_parameter in a favorable way. SQL> select name, lcount from user $ where name = 'system'; name lcount -------------------------------- ---------- SYSTEM 0 SQL> conn SYSTEM/1; ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn system/2; ERROR: ORA-01017: invalid username/password; logon denied SQL> conn system/3; ERROR: ORA-01017: invalid username/password; logon denied SQL> conn system/4; ERROR: ORA-01017: invalid username/password; logon denied SQL> conn/as sysdba; Connected. SQL> select name, lcount from user $ where name = 'system'; name lcount -------------------------------- ---------- SYSTEM 4 SQL> conn SYSTEM/oracle; Connected. SQL> conn/as sysdba; Connected. SQL> select name, lcount from user $ where name = 'system'; NAME LCOUNT ------------------------------ ---------- SYSTEM 0 Password_life_time: specifies the number of days allowed by the same password. If the password_grace_time parameter is specified at the same time, if the password is not changed within grace period, the password will be invalid and the connection to the database will be rejected. If the password_grace_time parameter is not set, the default value of unlimited will throw a database warning, but allow the user to continue the connection. Password_reuse_time and password_reuse_max: these two parameters must be associated. password_reuse_time specifies the number of days before the password cannot be reused, while password_reuse_max specifies the number of times the password has changed before the current password is reused. Both parameters must be set as integers. 1. If an integer is specified for these two parameters, the user cannot reuse the password until the password is changed to the number of times specified by password_reuse_max and later within the time specified by password_reuse_time. For example, if password_reuse_time = 30 and password_reuse_max = 10, you can reuse the password after 30 days. The password must be changed more than 10 times. 2. If one of them is specified as an integer and the other is unlimited, the user can never reuse a password. 3. if one of them is specified as default, Oracle database uses the default value defined in profile. By default, all parameters are set as unlimited in profile. If the default value of profile is not changed, the database always defaults to unlimited. 4. If both parameters are set to unlimited, the database ignores them. Password_lock_time: Specify the account contraction time after the number of failed login attempts reaches, in days. Password_grace_time: specifies the number of days before the database returns a warning. If the database password is not modified in the middle, it will expire. Password_verify_function: this field allows you to pass complex PL/SQL password verification scripts as parameters to the create profile statement. Oracle Database provides a default script, but you can create your own verification rules or use third-party software for verification. For the Function name, specify the name of the password verification rule. If it is Null, the password verification Function is not used. If an expression is specified for the password parameter, the expression can be in any format, except for database scalar queries. V. Example: 1. create a profile: create profile new_profile limit password_reuse_max 10password_reuse_time 30; 2. set profile resource limit: create profile app_user limitsessions_per_user unlimitedcpu_per_session unlimitedcpu_per_call 3000connect_time limit 1000private_sga 15kcomposite_limit 5000000; total resource cost cannot exceed 5 million service units. The formula for calculating the total resource cost is specified by the alter resource cost statement. 3. set Password restrictions profile: create profile app_users2 limitfailed_login_attempts 5password_life_time 60password_reuse_time 60password_reuse_max 5password_verify_function limit 1/24password_grace_time 10; 4. assign the configuration file to the user: SQL> alter user dinya profile app_user; the user has changed. SQL> alter user dinya profile default; the user has changed. Error message and solution ORA-02390 exceeded COMPOSITE_LIMIT, you are being logged offCause: The COMPOSITE_LIMIT for the profile is exceeded. that is, the weighted sum of the connection time, logical reads per session, CPU usage per session, and private SGA space used during the session exceeded the limit set by the COMPOSITE_LIMIT clause set in the user profile. action: If this happens often, ask the database administrator to raise the COMPOSITE_LIMIT of the user profile, or determine which resource is used the most and raise the limit on that resource. ORA-02391 exceeded simultaneous SESSIONS_PER_USER limitCause: An attempt was made to exceed the maximum number of concurrent sessions allowed by the SESSIONS_PER_USER clause of the user profile. action: End one or more concurrent sessions or ask the database administrator to increase the SESSIONS_PER_USER limit of the user profile. for more information about SESSIONS_PER_USER and the database administrator's specific tasks of adjusting concurrent sessions, see the Oracle9i SQL Reference and the Oracle9i Database Administrator's Guide. ORA-02392 exceeded session limit on CPU usage, you are being logged offCause: An attempt was made to exceed the maximum CPU usage allowed by the CPU_PER_SESSION clause of the user profile. action: If this happens often, ask the database administrator to increase the CPU_PER_SESSION limit of the user profile. ORA-02393 exceeded call limit on CPU usageCause: An attempt was made to exceed the maximum CPU time for a call, a parse, execute, or fetch, allowed by the CPU_PER_CALL clause of the user profile. action: If this happens often, ask the database administrator to increase the CPU_PER_CALL limit of the user profile. ORA-02394 exceeded session limit on IO usage, you are being logged offCause: An attempt was made to exceed the maximum I/O allowed by the LOGICAL_READS_PER_SESSION clause of the user profile. action: If this happens often, ask the database administrator to increase the LOGICAL_READS_PER_SESSION limit of the user profile. ORA-02395 exceeded call limit on IO usageCause: An attempt was made to exceed the maximum I/O for a call, a parse, execute, or fetch, allowed by the LOGICAL_READS_PER_CALL clause of the user profile. action: If this happens often, ask the database administrator to increase the LOGICAL_READS_PER_CALL limit of the user profile. ORA-02396 exceeded maximum idle time, please connect againCause: A user has exceeded the maximum time allowed to remain idle. action: The user must reconnect to the database. ORA-02397 exceeded PRIVATE_SGA limit, you are being logged offCause: This error occurs only when using a multi-threaded server. action: Contact the database administrator to expand the PRIVATE_SGA limit.