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 - Export/Import Roles

Oracle Tips by Burleson Consulting

Export/Import Roles

Oracle provides a script entitled catexp.sql for roles. This script creates the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles. You can grant these to a user who will be executing the export and import utilities.

The EXP_FULL_DATABASE role has the SELECT ANY TABLE and BACKUP ANY TABLE system privileges. In addition, this role has INSERT, DELETE, and UPDATE privileges on the SYS.INCVID, SYS.INCFIL, and SYS.INCEXP tables.

The IMP_FULL_DATABASE role has the BECOME USER system privilege.

Using PROFILES

You can set up limits on the system resources used by setting up profiles with defined limits on resources.  Profiles are very useful in large, complex organizations with many users.  It allows you to regulate the amount of resources used by each database user by creating and assigning profiles to users. Using Oracle8 password attributes where added into profiles as well.

Creation of PROFILES

Profiles are a named set of resource limits.  By default, when you create a user, they are given the default profile.  The default profile provides unlimited use of all resources. 

The syntax to create a profile follows:

>---CREATE PROFILE profile LIMIT resource_parameters|password_parameters--;->
Resource_parameters (you can specify multiple paramters per command): 
 [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]

Restrictions on password parameters:

* Expr must resolve to either an integer value or an integer number of days

* If PASSWORD_REUSE_TIME is set to an integer value, PASSWORD_REUSE_MAX must be set to UNLIMITED.

* If PASSWORD_REUSE_MAX is set to an integer value, PASSWORD_REUSE_TIME must be set to UNLIMITED.

* If both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are set to UNLIMITED, then Oracle uses neither of these password resources.

* If PASSWORD_REUSE_MAX is set to DEFAULT and PASSWORD_REUSE_TIME is set to UNLIMITED, then Oracle uses the PASSWORD_REUSE_MAX value defined in the DEFAULT profile.

* If PASSWORD_REUSE_TIME is set to DEFAULT and PASSWORD_REUSE_MAX is set to UNLIMITED, then Oracle uses the PASSWORD_REUSE_TIME value defined in the DEFAULT profile.

* If both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are set to DEFAULT, then Oracle uses whichever value is defined in the DEFAULT profile.

For example:

CREATE PROFILE  enduser  LIMIT
CPU_PER_SESSION                 60000
LOGICAL_READS_PER_SESSION   1000
CONNECT_TIME              30
PRIVATE_SGA               102400
CPU_PER_CALL              UNLIMITED
COMPOSITE LIMIT           60000000
FAILED_LOGIN_ATTEMPTS       3
PASSWORD_LIFE_TIME              90
PASSWORD_REUSE_TIME             180
PASSWORD_LOCK_TIME              3
PASSWORD_GRACE_TIME             3
Verify_function_one ;

You can assign a profile to a user when you create the user or by altering the user.  The syntax to alter the profile for a user is:

ALTER USER PROFILE profile; 

For example: 

ALTER USER scott 
PROFILE appuser;

You must have the CREATE PROFILE system privilege to create a profile.  To alter a profile you must be the creator of the profile or have the ALTER PROFILE system privilege.  To assign a profile to a user, you must have the CREATE USER or ALTER USER system privilege.

Profiles and Resource Limits

The default cost assigned to a resource is unlimited.  By setting resource limits, you can prevent users from performing operations that will tie up the system and prevent other users from performing operations.  You can use resource limits for security to ensure that users log off the system and do not leave the session connected for long periods of time. You can also assign a composite cost to each profile . The system resource limits can be enforced at the session level, the call level or both.

The session level is from the time the user logs into the database until the user exits.  The call level is for each SQL command issued. Session level limits are enforced for each connection.  When a session level limit is exceeded, only the last SQL command issued is rolled back and no further work can be performed until a commit, rollback or exit is performed. Table 7.15 lists the system resources which can be regulated at the session level.

One thing to note if you use parallel query option (PQO) is that the resources are applied to each new session, not accumulated over all of the sessions that a parallel operation uses.

TABLE 7.15:  RESOURCES REGULATED AT THE SESSION LEVEL

SYSTEM RESOURCE

DEFINITION

CPU_PER_SESSION

total CPU time in hundreds of seconds

SESSIONS_PER_USER

number of concurrent sessions for a user

CONNECT_TIME

allowed connection time in minutes

IDLE_TIME

inactive time on the server in minutes

LOGICAL_READS_PER_SESSION

number of data blocks read including both physical and logical reads from memory and disk

PRIVATE_SGA

bytes of SGA used in a database with the multithreaded server (in K or M)

You can combine the CPU_PER_SESSION, LOGICAL_READS_PER_SESSION, CONNECT_TIME, and PRIVATE_SGA to create a COMPOSITE LIMIT.

Call-level limits are enforced during the execution of each SQL statement.  When a call-level limit is exceeded, the last SQL command issued is rolled back.  All the previous statements issued are still valid and the user can continue to execute other SQL statements.  The following system resources can be regulated at the call level:

* CPU_PER_CALL for the CPU time for the SQL statement
* LOGICAL_READS_PER_CALL for the number of data blocks read for the SQL statement

The assignment of a cost to a resource can be performed with the ALTER RESOURCE COST command. Resource limits that you set explicitly for a user take precedence over the resource costs in an assigned profile.  The command line syntax for this command is:

>-ALTER RESOURCE COST ---------------------------------------------------;-->
                      |-[CPU_PER_SESSION n|UNLIMITED|DEFAULT]-----------|
                      |-[CONNECT_TIME n|UNLIMITED|DEFAULT]--------------|
                      |-[LOGICAL_READS_PER_SESSION n|UNLIMITED|DEFAULT]-|
                      |-[PRIVATE_SGA  n [K|M]|UNLIMITED|DEFAULT]--------|

For example,

ALTER RESOURCE COST CONNECT_TIME 100;

Use of resource limits is set in the database initialization parameter RESOURCE_LIMIT=TRUE.  By default this parameter is set to false.  This parameter can be changed interactively with an ALTER SYSTEM command.

The DBA_PROFILES view provides information on all the profiles and the resource limits for each profile.  The RESOURCE_COST view shows the unit cost associated with each resource. Each user can find information on his resources and limits in the USER_RESOURCE_LIMITS view. 

Table 7.16 gives a description of these data dictionary views.

Table 7.16:  Data dictionary views for resources.

DBA_PROFILES

Column

Definition

Profile

the name given to the profile

Resource_name

the name of the resource assigned to the profile

Limit

the limit placed on the profile

RESOURCE_COST

Column

Definition

Resource_name

name of the resource

Unit_cost

cost assigned

USER_RESOURCE_LIMITS

Column

Definition

Resource_name

the name of the resource

Limit

the limit placed on the user

 


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

For complete Oracle utilities tips and tricks, see the book "Advanced Oracle Utilities" by Dr. Bert Scalzo.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

 


 

 
��  
 
 
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.