Oracle Concepts - Alerting Profiles

Oracle Tips by Burleson Consulting

Altering Profiles

Provided you have the CREATE PROFILE or ALTER PROFILE system privilege, you can alter any profile, including the Oracle created DEFAULT profile. You can alter a profile to change the cost assigned to each resource. The syntax to alter a profile follows:

>--ALTER PROFILE profile LIMIT resource_parameters|password_parameters-;->

Resource_parameters:

Password_parameters (Oracle8 and above):

For example:

ALTER PROFILE enduser LIMIT
CPU_PER_SESSION 60000
LOGICAL_READS_PER_SESSION 1000
CONNECT_TIME 60
PRIVATE_SGA 102400
CPU_PER_CALL UNLIMITED
COMPOSITE LIMIT 60000000;

To disable a profile during a session, you must have the ALTER SYSTEM privilege. A limit that you set for the session overrides the previous limit set by the profile. To reset the profile to the limit originally set by the database, set the limit to DEFAULT.

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE|FALSE;

For example:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE ;

Profiles and Passwords

In Oracle8 the ability to control password expiry and password complexity and validity was added to Oracle. The capability to control passwords is contained within the purview of the profile. Table 7.17 lists the password control attributes in a profile and their definitions.

Table 7.17: Password Control Attributes in a Profile

Attribute	Description
FAILED_LOGIN_ATTEMPTS	specifies the number of failed attempts to log in to the user account before the account is locked.
PASSWORD_LIFE_TIME	limits the number of days the same password can be used for authentication. The password expires if it is not changed within this period, and further connections are rejected.
PASSWORD_REUSE_TIME	specifies the number of days before which a password cannot be reused. If you set PASSWORD_REUSE_TIME to an integer value, then you must set PASSWORD_REUSE_MAX to UNLIMITED.
PASSWORD_REUSE_MAX	specifies the number of password changes required before the current password can be reused. If you set PASSWORD_REUSE_MAX to an integer value, then you must set PASSWORD_REUSE_TIME to UNLIMITED.
PASSWORD_LOCK_TIME	specifies the number of days an account will be locked after the specified number of consecutive failed login attempts.
PASSWORD_GRACE_TIME	specifies the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.
PASSWORD_VERIFY_FUNCTION	allows a PL/SQL password complexity verification script to be passed as an argument to the CREATE PROFILE statement. Oracle provides a default script, but you can create your own routine or use third-party software instead. function is the name of the password complexity verification routine. NULL indicates that no password verification is performed.

Oracle also provides a template PL/SQL procedure for use in creating your own password complexity and verification function. The example PL/SQL procedure is located in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql on UNIX and in x:\orant\rdbms\admin\utlpwdmg.sql on NT. Other than the required input and return variables the password verification function can be as simple, or as complex, as you desire.

Managing CPU Utilization for in Oracle8i

In Oracle diverse groups of users may look at the database to find information important to their group. While we like to believe everyone is equal, face it, if the CEO wants a report his needs come over and above Joe Clerk's needs for a different report. In earlier releases of Oracle you could use profiles to restrict specific types of resources but this was unwieldy and produced unpredictable results. New in Oracle8i is the concept of Oracle resource groups. A resource group specification allows you to specify that a specific group of database users can only use a certain percentage of the CPU resources on the system. A resource plan must be developed that defines the various levels within the application and their percentage of CPU resources in a waterfall type structure where each subsequent levels percentages are based on the previous levels.

This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam. It?s only $19.95 when you buy it directly from the publisher here.

��