Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

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: 

 [SESSIONS_PER_USER n|UNLIMITED|DEFAULT]
 [CPU_PER_SESSION   n|UNLIMITED|DEFAULT]    
 [CPU_PER_CALL      n|UNLIMITED|DEFAULT]           
 [CONNECT_TIME      n|UNLIMITED|DEFAULT]
 [IDLE_TIME         n|UNLIMITED|DEFAULT]
 [LOGICAL_READS_PER_SESSION n|UNLIMITED|DEFAULT] 
 [LOGICAL_READS_PER_CALL    n|UNLIMITED|DEFAULT]
 [COMPOSITE_LIMIT           n|UNLIMITED|DEFAULT]
 [PRIVATE_SGA               n [K|M|UNLIMITED|DEFAULT]]

Password_parameters (Oracle8 and above):

 [FAILED_LOGIN_ATTEMPTS expr|UNLIMITED|DEFAULT]
 [PASSWORD_LIFE_TIME    expr|UNLIMITED|DEFAULT]
 [PASSWORD_REUSE_TIME   expr|UNLIMITED|DEFAULT]
 [PASSWORD_REUSE_MAX    expr|UNLIMITED|DEFAULT]
 [PASSWORD_LOCK_TIME    expr|UNLIMITED|DEFAULT]
 [PASSWORD_GRACE_TIME   expr|UNLIMITED|DEFAULT]
 [PASSWORD_VERIFY_FUNCTION function_name|NULL|DEFAULT]

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.

 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.