|Oracle9i Database Administrator's Guide
Release 1 (9.0.1)
Part Number A90117-01
This chapter describes how to control access to an Oracle database, and contains the following topics:
Oracle helps you ensure that your site complies with its Oracle database server license agreement. If your site is licensed by concurrent usage, you can track and limit the number of sessions concurrently connected to a database. If your site is licensed by named users, you can limit the number of named users created in a database. In either case, you control the licensing facilities, and must enable the facilities and set the appropriate limits. You do this by setting the following initialization parameters in your initialization parameter file:
To use the licensing facility, you must know which type of licensing agreement your site has, and what the maximum number of sessions or named users is. Your site can use either type of licensing (concurrent usage or named user), but not both.
In a few cases, a site might have an unlimited license, rather than concurrent usage or named user licensing. In these cases only, leave the licensing mechanism disabled, and omit
This section describes aspects of session and user licensing, and contains the following topics:
Concurrent usage licensing limits the number of sessions that can be connected simultaneously to the database on the specified computer. You can set a limit on the number of concurrent sessions before you start an instance. You can also change the maximum number of concurrent sessions while the database is running.
Chapter 2, "Creating an Oracle Database" describes setting a limit on the number of concurrent sessions as part of the initial database creation process
After your instance's session limit is reached, only users with
RESTRICTED SESSION privilege (usually DBAs) can connect to the database. When a user with
RESTRICTED SESSION privileges connects, Oracle sends the user a message indicating that the maximum limit has been reached, and writes a message to the alert file. When the maximum is reached, you should connect only to terminate unneeded processes.
Do not raise the licensing limits unless you have upgraded your Oracle license agreement.
In addition to setting a maximum concurrent session limit, you can set a warning limit on the number of concurrent sessions. After this limit is reached, additional users can continue to connect (up to the maximum limit). However, Oracle writes an appropriate message to the alert file with each connection, and sends each connecting user who has the
RESTRICTED SESSION privilege a warning indicating that the maximum is about to be reached.
If a user is connecting with administrator privileges, the limits still apply. However, Oracle enforces the limit after the first statement the user executes.
In addition to enforcing the concurrent usage limits, Oracle tracks the highest number of concurrent sessions for each instance. You can use this "high water mark" to help you determine if your Oracle license needs to be reviewed.
For instances running Oracle Real Application Clusters, each instance can have its own concurrent usage limit and warning limit. However, the sum of the instances' limits must not exceed the site's concurrent usage license.
Sessions that connect to Oracle through multiplexing software or hardware (such as a TP monitor) each contribute individually to the concurrent usage limit. However, the Oracle licensing mechanism cannot distinguish the number of sessions connected this way. If your site uses multiplexing software or hardware, you must consider that and set the maximum concurrent usage limit lower to account for the multiplexed sessions.
"Viewing Licensing Limits and Current Values" for information about Oracle licensing limit upgrades
To set the maximum number of concurrent sessions for an instance, set the
LICENSE_MAX_SESSIONS initialization parameter. This example sets the maximum number of concurrent sessions to 80.
If you set this limit, you are not required to set a warning limit (
LICENSE_SESSIONS_WARNING). However, using the warning limit makes the maximum limit easier to manage, because it gives you advance notice that your site is nearing maximum use.
To set the warning limit for an instance, set the
LICENSE_SESSIONS_WARNING initialization parameter in the initialization parameter file used to start the instance. In the following example a warning message is written to the alert file for each new connection starting with the 60th.
Set the session warning to a value lower than the concurrent usage maximum limit (
To change either the maximum concurrent usage limit or the warning limit while the database is running, use the
ALTER SYSTEM statement with the appropriate option. The following statement changes the maximum limit to 100 concurrent sessions:
The following statement changes both the warning limit and the maximum limit:
If you change either limit to a value lower than the current number of sessions, the current sessions remain. However, the new limit is enforced for all future connections until the instance is shut down. To change the limit permanently, change the value of the appropriate parameter in the initialization parameter file.
To change the concurrent usage limits while the database is running, you must have the
ALTER SYSTEM privilege. Also, to connect to an instance after the instance's maximum limit has been reached, you must have the
RESTRICTED SESSION privilege.
Oracle9i SQL Reference for more information about using the
Named user licensing limits the number of individuals authorized to use Oracle on the specified computer. To enforce this license, you can set a limit on the number of users created in the database before you start an instance. You can also change the maximum number of users while the instance is running, or disable the limit altogether. You cannot create more users after reaching this limit. If you try to do so, Oracle returns an error indicating that the maximum number of users have been created, and writes a message to the alert file.
This mechanism operates on the assumption that each person accessing the database has a unique user name, and that there are no shared user names. If you use named user licensing, do not allow multiple users to connect using the same user name.
To limit the number of users created in a database, set the
LICENSE_MAX_USERS initialization parameter in the database's initialization parameter file. The following example sets the maximum number of users to 200:
If the database contains more than
LICENSE_MAX_USERS when you start it, Oracle returns a warning and writes an appropriate message in the alert file. You cannot create additional users until the number of users drops below the limit or until you delete users or upgrade your Oracle license.
For instances running Oracle Real Application Clusters, all instances connected to the same database should have the same named user limit.
To change the maximum named users limit, use the
ALTER SYSTEM statement with the
LICENSE_MAX_USERS option. The following statement changes the maximum number of defined users to 300:
If you try to change the limit to a value lower than the current number of users, Oracle returns an error and continues to use the old limit. If you successfully change the limit, the new limit remains in effect until you shut down the instance. To change the limit permanently, change the value of
LICENSE_MAX_USERS in the initialization parameter file.
To change the maximum named users limit, you must have the
ALTER SYSTEM privilege.
V$LICENSE data dictionary view allows you to see the current limits of all of the license settings, the current number of user sessions, and the highest number of concurrent user sessions since the instance started. An example is shown below. You can use this information to determine if you need to upgrade your Oracle license to allow more concurrent sessions or named users.
SELECT SESSIONS_MAX S_MAX, SESSIONS_WARNING S_WARNING, SESSIONS_CURRENT S_CURRENT, SESSIONS_HIGHWATER S_HIGH, USERS_MAX FROM V$LICENSE; S_MAX S_WARNING S_CURRENT S_HIGH USERS_MAX ------- ---------- ----------- -------- ---------- 100 80 65 82 50
In addition, Oracle writes the session high water mark to the database's alert file when the database shuts down, so you can check for it there.
To see the current number of named users defined in the database, use the following query:
Oracle9i Database Reference for a complete description of the
Oracle provides several means for users to be authenticated before they are allowed to create a database session:
These means of authentication are discussed in the following sections:
If you choose database authentication for a user, administration of the user account, password, and authentication of that user is performed entirely by Oracle. To have Oracle authenticate a user, specify a password for the user when you create or alter the user. Users can change their password at any time. Passwords are stored in an encrypted format. Each password must be made up of single-byte characters, even if your database uses a multibyte character set.
To enhance security when using database authentication, Oracle recommends the use of password management, including account locking, password aging and expiration, password history, and password complexity verification.
The following statement creates a user who is identified and authenticated by Oracle. User
scott must specify the password
tiger whenever connecting to Oracle.
Oracle9i SQL Reference for more information about valid passwords, and how to specify the
Following are advantages of database authentication:
When you choose external authentication for a user, the user account is maintained by Oracle, but password administration and user authentication is performed by an external service. This external service can be the operating system or a network service, such as Oracle Net.
With external authentication, your database relies on the underlying operating system or network authentication service to restrict access to database accounts. A database password is not used for this type of login. If your operating system or network service permits, you can have it authenticate users. If you do so, set the initialization parameter
OS_AUTHENT_PREFIX, and use this prefix in Oracle user names. The
OS_AUTHENT_PREFIX parameter defines a prefix that Oracle adds to the beginning of every user's operating system account name. Oracle compares the prefixed user name with the Oracle user names in the database when a user attempts to connect.
For example, assume that
OS_AUTHENT_PREFIX is set as follows:
If a user with an operating system account named
tsmith is to connect to an Oracle database and be authenticated by the operating system, Oracle checks that there is a corresponding database user
OPS$tsmith and, if so, allows the user to connect. All references to a user authenticated by the operating system must include the prefix, as seen in
The default value of this parameter is
OPS$ for backward compatibility with previous versions of Oracle. However, you might prefer to set the prefix value to some other string or a null string (an empty set of double quotes: ""). Using a null string eliminates the addition of any prefix to operating system account names, so that Oracle user names exactly match operating system user names.
After you set
OS_AUTHENT_PREFIX, it should remain the same for the life of a database. If you change the prefix, any database user name that includes the old prefix cannot be used to establish a connection, unless you alter the user name to have it use password authentication.
The following statement creates a user who is identified by Oracle and authenticated by the operating system or a network service. This example assumes that
OS_AUTHENT_PREFIX = "".
CREATE USER ... IDENTIFIED EXTERNALLY, you create database accounts that must be authenticated by the operating system or network service. Oracle relies on this external login authentication to ensure that a specific operating system user has access to a specific database user.
Oracle Advanced Security Administrator's Guide for more information about external authentication
By default, Oracle only allows operating system authenticated logins over secure connections. Therefore, if you want the operating system to authenticate a user, by default that user cannot connect to the database over Oracle Net. This means the user cannot connect using a shared server configuration, since this connection uses Oracle Net. This default restriction prevents a remote user from impersonating another operating system user over a network connection.
If you are not concerned about remote users impersonating another operating system user over a network connection, and you want to use operating system user authentication with network clients, set the initialization parameter
REMOTE_OS_AUTHENT (default is
TRUE in the database's initialization parameter file. Setting the initialization parameter
TRUE allows the RDBMS to accept the client operating system user name received over a nonsecure connection and use it for account access. The change take effect the next time you start the instance and mount the database.
Generally, user authentication through the host operating system offers the following benefits:
Network authentication is performed using Oracle Advanced Security, which can be configured to use a third party service such as Kerberos. If you are using Oracle Advanced Security as your only external authentication service, the setting of the parameter
REMOTE_OS_AUTHENT is irrelevant, since Oracle Advanced Security only allows secure connections.
Following are advantages of external authentication:
Oracle Advanced Security enables you to centralize management of user-related information, including authorizations, in an LDAP-based directory service. Users can be identified in the database as global users, meaning that they are authenticated by SSL and that the management of these users is done outside of the database by the centralized directory service. Global roles are defined in a database and are known only to that database, but authorizations for such roles is done by the directory service.
You can also have users authenticated by SSL, whose authorizations are not managed in a directory; that is, they have local database roles only. See the Oracle Advanced Security Administrator's Guide for details.
This centralized management enables the creation of enterprise users and enterprise roles. Enterprise users are defined and managed in the directory. They have unique identities across the enterprise, and can be assigned enterprise roles that determine their access privileges across multiple databases. An enterprise role consists of one or more global roles, and might be thought of as a container for global roles.
You have a couple of options as to how you specify users who are authorized by a directory service.
The following statement illustrates the creation of a global user, who is authenticated by SSL and authorized by the enterprise directory service:
The string provided in the
AS clause provides an identifier (distinguished name, or DN) meaningful to the enterprise directory.
In this case,
scott is truly a global user. But, the disadvantage here is that user
scott must then be created in every database that he must access, plus the directory.
Creating schema-independent users allows multiple enterprise users to access a shared schema in the database. A schema-independent user is:
CREATE USERstatement of any type
The process of creating a schema-independent user is as follows:
The mapping object tells the database how you want to map users' DNs to the shared schema. You can either do a full DN mapping (one directory entry for each unique DN), or you can map, for example, every user containing the following DN components to the
See the Oracle Internet Directory Administrator's Guide for an explanation of these mappings.
Most users do not need their own schemas, and implementing schema-independent users divorces users from databases. You create multiple users who share the same schema in a database, and as enterprise users, they can access shared schemas in other databases as well.
Some of the advantages of global user authentication and authorization are the following:
CURRENT_USERdatabase links connect as a global user. A local user can connect as a global user in the context of a stored procedure--without storing the global user's password in a link definition.
It is possible to design a middle-tier server to proxy clients in a secure fashion.
Oracle provides three forms of proxy authentication:
In all cases, the middle-tier server must be authorized to act on behalf of the client by the administrator.
To authorize a middle-tier server to proxy a client use the
GRANT CONNECT THROUGH clause of the
ALTER USER statement. You can also specify roles that the middle tier is permitted to activate when connecting as the client.
Operations done on behalf of a client by a middle-tier server can be audited.
PROXY_USERS data dictionary view can be queried to see which users are currently authorized to connect through a middle tier.
REVOKE CONNECT THROUGH clause of
ALTER USER to disallow a proxy connection.
The following statement authorizes the middle-tier server
appserve to connect as user
bill. It uses the
WITH ROLE clause to specify that
appserve activate all roles associated with
To revoke the middle-tier server's (
appserve) authorization to connect as user
bill, the following statement is used:
AUTHENTICATED USING clause of the
ALTER USER ... GRANT CONNECT THROUGH statement to authorize a user to be proxied, but not authenticated, by a middle tier. Currently,
PASSWORD is the only means supported.
The following statement illustrates this form of authentication:
In the above statement, middle-tier server
midtier is authorized to connect as
midtier must also pass
mary's password to the database server for authorization.
In this case, the following statement authorizes the middle-tier server
WebDB to present the distinguished name for global user
jeff to the database server. The distinguished name is used to retrieve the user name. User
jeff has been authenticated by the middle-tier server
Optionally, the middle-tier server can be authorized to present an entire certificate (containing the distinguished name). This is illustrated in the following statement:
Passing the entire certificate costs time in authentication. However, some applications use other information contained in the certificate.
Each Oracle database has a list of valid database users. To access a database, a user must run a database application and connect to the database instance using a valid user name defined in the database. This section explains how to manage users for a database, and contains the following topics:
You create a user with the
CREATE USER statement.To create a database user, you must have the
CREATE USER system privilege. Because it is a powerful privilege, a DBA or security administrator is normally the only user who has the
CREATE USER system privilege.
The following example creates a user and specifies that user's password, default tablespace, temporary tablespace where temporary segments are created, tablespace quotas, and profile.
CREATE USER jward IDENTIFIED BY aZ7bC2 DEFAULT TABLESPACE data_ts QUOTA 100M ON test_ts QUOTA 500K ON data_ts TEMPORARY TABLESPACE temp_ts PROFILE clerk; GRANT connect TO jward;
A newly created user cannot connect to the database until granted the
CREATE SESSION system privilege. Usually, a newly created user is granted a role, similar to the predefined roll
CONNECT, as shown in this example, that specifies the
CREATE SESSION and other basic privileges required to access a database.
This section refers to the above example as it discusses the following aspects of creating a user:
Within each database a user name must be unique with respect to other user names and roles. A user and role cannot have the same name. Furthermore, each user has an associated schema. Within a schema, each schema object must have a unique name.
In the previous
CREATE USER statement, the new user is to be authenticated using the database. In this case, the connecting user must supply the correct password to the database to connect successfully.
Each user should have a default tablespace. When a user creates a schema object and specifies no tablespace to contain it, Oracle stores the object in the user's default tablespace.
The default setting for every user's default tablespace is the
SYSTEM tablespace. If a user does not create objects, and has no privileges to do so, this default setting is fine. However, if a user creates any type of object, you should specifically assign the user a default tablespace. Using a tablespace other than
SYSTEM reduces contention between data dictionary objects and user objects for the same datafiles. In general, it is not advisable for user data to be stored in the
You can set a user's default tablespace during user creation, and change it later with the
ALTER USER statement. Changing the user's default tablespace affects only objects created after the setting is changed.
When you specify the user's default tablespace, also specify a quota on that tablespace.
In the previous
CREATE USER statement,
jward's default tablespace is
You can assign each user a tablespace quota for any tablespace (except a temporary tablespace). Assigning a quota does two things:
By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, you must assign a quota to allow the user to create objects. Minimally, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they can create objects.
You can assign a user either individual quotas for a specific amount of disk space in each tablespace or an unlimited amount of disk space in all tablespaces. Specific quotas prevent a user's objects from consuming too much space in the database.
You can assign a user's tablespace quotas when you create the user, or add or change quotas later. If a new quota is less than the old one, then the following conditions hold true:
You can revoke a user's ability to create objects in a tablespace by changing the user's current quota to zero. After a quota of zero is assigned, the user's objects in the tablespace remain, but new objects cannot be created and existing objects cannot be allocated any new space.
To permit a user to use an unlimited amount of any tablespace in the database, grant the user the
UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the user. If you later revoke the privilege, explicit quotas again take effect. You can grant this privilege only to users, not to roles.
Before granting the
UNLIMITED TABLESPACE system privilege, consider the consequences of doing so:
UNLIMITED TABLESPACEprivilege. You can grant access selectively only after revoking the privilege.
Each user also should be assigned a temporary tablespace. When a user executes a SQL statement that requires a temporary segment, Oracle stores the segment in the user's temporary tablespace. These temporary segments are created by the system when doing sorts or joins and are owned by
SYS, which has resource privileges in all tablespaces.
In the previous
CREATE USER statement,
jward's temporary tablespace is
temp_ts, a tablespace created explicitly to contain only temporary segments. Such a tablespace is created using the
CREATE TEMPORARY TABLESPACE statement.
If a user's temporary tablespace is not explicitly set, the user is assigned the default temporary tablespace that was specified at database creation, or by an
ALTER DATABASE statement at a later time. If there is no default temporary tablespace, the default is the
SYSTEM tablespace. Just as for a user's default tablespace discussed earlier, it is not advisable for user data to be stored in the
SYSTEM tablespace. Also, assigning a tablespace to be used specifically as a temporary tablespace eliminates file contention among temporary segments and other types of segments.
You can set a user's temporary tablespace at user creation, and change it later using the
ALTER USER statement. Do not set a quota for temporary tablespaces.
You also specify a profile when you create a user. A profile is a set of limits on database resources and password access to the database. If no profile is specified, the user is assigned a default profile.
You cannot set a user's default roles in the
CREATE USER statement. When you first create a user, the user's default role setting is
ALL, which causes all roles subsequently granted to the user to be default roles. Use the
ALTER USER statement to change the user's default roles.
Users can change their own passwords. However, to change any other option of a user's security domain, you must have the
ALTER USER system privilege. Security administrators are normally the only users that have this system privilege, as it allows a modification of any user's security domain. This privilege includes the ability to set tablespace quotas for a user on any tablespace in the database, even if the user performing the modification does not have a quota for a specified tablespace.
You can alter a user's security settings with the
ALTER USER statement. Changing a user's security settings affects the user's future sessions, not current sessions.
The following statement alters the security settings for user
ALTER USER avyrros IDENTIFIED EXTERNALLY DEFAULT TABLESPACE data_ts TEMPORARY TABLESPACE temp_ts QUOTA 100M ON data_ts QUOTA 0 ON test_ts PROFILE clerk;
ALTER USER statement here changes
avyrros's security settings as follows:
avyrros's operating system account.
avyrros's default and temporary tablespaces are explicitly set.
avyrrosis given a
100Mquota for the
avyrros's quota on the
avyrrosis assigned the
Most non-DBA users can still change their own passwords with the
ALTER USER statement, as follows:
Users can change their own passwords this way, without any special privileges (other than those to connect to the database). Users should be encouraged to change their passwords frequently.
Users must have the
ALTER USER privilege to switch between Oracle database authentication and a form of external authentication. Usually, only an administrator has this privilege.
A default role is one that is automatically enabled for a user when the user creates a session. You can assign a user zero or more default roles.
Chapter 25, "Managing User Privileges and Roles" for information about changing users' default roles
When a user is dropped, the user and associated schema are removed from the data dictionary and all schema objects contained in the user's schema, if any, are immediately dropped.
A user that is currently connected to a database cannot be dropped. To drop a connected user, you must first terminate the user's sessions using the SQL statement
ALTER SYSTEM with the
KILL SESSION clause.
You can drop a user from a database using the
DROP USER statement. To drop a user and all the user's schema objects (if any), you must have the
DROP USER system privilege. Because the
DROP USER system privilege is so powerful, a security administrator is typically the only type of user that has this privilege.
If the user's schema contains any schema objects, use the
CASCADE option to drop the user and all associated objects and foreign keys that depend on the tables of the user successfully. If you do not specify
CASCADE and the user's schema contains objects, an error message is returned and the user is not dropped. Before dropping a user whose schema contains objects, thoroughly investigate which objects the user's schema contains and the implications of dropping them. Pay attention to any unknown cascading effects. For example, if you intend to drop a user who owns a table, check whether any views or procedures depend on that particular table.
The following statement drops user
jones and all associated objects and foreign keys that depend on the tables owned by
"Terminating Sessions" for more information about terminating sessions
A profile is a named set of resource limits. A user's profile limits database usage and instance resources as defined in the profile. You can assign a profile to each user, and a default profile to all users who do not have specific profiles. For profiles to take effect, resource limits must be turned on for the database as a whole.
This section describes aspects of profile management, and contains the following topics:
A profile can be created, assigned to users, altered, and dropped at any time by any authorized database user, but the resource limits set for a profile are enforced only when you enable resource limitation for the associated database. Resource limitation enforcement can be enabled or disabled by two different methods, as described in the next two sections.
To alter the enforcement of resource limitation while the database remains open, you must have the
ALTER SYSTEM system privilege.
If a database can be temporarily shut down, resource limitation can be enabled or disabled by the
RESOURCE_LIMIT initialization parameter in the database's initialization parameter file. Valid values for the parameter are
TRUE (enables enforcement) and
FALSE. By default, this parameter's value is set to
FALSE. Once the initialization parameter file has been edited, the database instance must be restarted to take effect. Every time an instance is started, the new parameter value enables or disables the enforcement of resource limitation.
If a database cannot be temporarily shut down or the resource limitation feature must be altered temporarily, you can enable or disable the enforcement of resource limitation using the SQL statement
ALTER SYSTEM. After an instance is started, an
ALTER SYSTEM statement overrides the value set by the
RESOURCE_LIMIT initialization parameter. For example, the following statement enables the enforcement of resource limitation for a database:
ALTER SYSTEM statement does not permanently determine the enforcement of resource limitation. If the database is shut down and restarted, the enforcement of resource limits is determined by the value set for the
To create a profile, you must have the
CREATE PROFILE system privilege. You can create profiles using the SQL statement
CREATE PROFILE. At the same time, you can explicitly set particular resource limits.
The following statement creates the profile
CREATE PROFILE clerk LIMIT SESSIONS_PER_USER 2 CPU_PER_SESSION unlimited CPU_PER_CALL 6000 LOGICAL_READS_PER_SESSION unlimited LOGICAL_READS_PER_CALL 100 IDLE_TIME 30 CONNECT_TIME 480;
All unspecified resource limits for a new profile take the limit set by a
Each database has a
DEFAULT profile, and its limits are used in two cases:
Initially, all limits of the
DEFAULT profile are set to
UNLIMITED. However, to prevent unlimited resource consumption by users of the
DEFAULT profile, the security administrator should change the default limits using the
ALTER PROFILE statement:
Any user with the
ALTER PROFILE system privilege can adjust the limits in the
DEFAULT profile. The
DEFAULT profile cannot be dropped.
After a profile has been created, you can assign it to database users. Each user can be assigned only one profile at any given time. If a profile is assigned to a user who already has a profile, the new profile assignment overrides the previously assigned profile. Profile assignments do not affect current sessions. Profiles can be assigned only to users and not to roles or other profiles.
Profiles can be assigned to users with the
CREATE USER and
ALTER USER statements.
You can alter the resource limit settings of any profile using the SQL statement
ALTER PROFILE. To alter a profile, you must have the
ALTER PROFILE system privilege.
Any adjusted profile limit overrides the previous setting for that profile limit. By adjusting a limit with a value of
DEFAULT, the resource limit reverts to the default limit set for the database. All profiles not adjusted when altering a profile retain the previous settings. Any changes to a profile do not affect current sessions. New profile settings are used only for sessions created after a profile is modified.
The following statement alters the
In addition to being able to use the
ALTER PROFILE statements to assign resource limits to specific resources, you can limit the total resource cost for a session by using composite limits. A composite limit is expressed as a weighted sum, measured in service units, of certain resources.
You can set a profile's composite limit using the
COMPOSITE_LIMIT clause of a
CREATE PROFILE or
ALTER PROFILE statement. The following
CREATE PROFILE statement specifies the
Notice that both explicit resource limits and a composite limit can exist concurrently for a profile. The limit that is reached first stops the activity in a session. Composite limits allow additional flexibility when limiting the use of system resources.
The correct composite limit depends on the total amount of resource used by an average profile user. As with each specific resource limit, historical information should be gathered to determine the normal range of composite resource usage for a typical profile user.
Oracle9i SQL Reference for information on how to calculate the composite limit
Each Oracle database server environment has its own characteristics. Some system resources can be more valuable in one environment than another. Oracle enables you to assign the following resources a weight, which then affects their contribution to a total resource cost:
If you do not assign a weight to a resource, its weight defaults to 0, and the use of the resource does not contribute to the total resource cost.
Oracle calculates the total resource cost by first multiplying the amount of each resource used in the session by the resource's weight, and then summing the products for all four resources. For any session, this cost is limited by the value of the
COMPOSITE_LIMIT parameter in the user's profile. Both the products and the total cost are expressed in units called service units.
To set weights for resources, use the
ALTER RESOURCE COST statemen
t.You must have the
ALTER RESOURCE system privilege.The following example assigns weights to the
The weights establish this cost formula for a session:
where the values of
LOGICAL_READS_PER_SESSION are either values in the
DEFAULT profile or in the profile of the user of the session.
Because the above statement assigns no weight to the resources
PRIVATE_SGA, these resources do not appear in the formula.
The above sources provide additional information and recommendations on setting resource costs
To drop a profile, you must have the
DROP PROFILE system privilege. You can drop a profile using the SQL statement
DROP PROFILE. To successfully drop a profile currently assigned to a user, use the
The following statement drops the profile
clerk, even though it is assigned to a user:
Any user currently assigned to a profile that is dropped is automatically assigned to the
DEFAULT profile. The
DEFAULT profile cannot be dropped. When a profile is dropped, the drop does not affect currently active sessions. Only sessions created after a profile is dropped abide by any modified profile assignments.
The following data dictionary views contain information about database users and profiles:
Describes tablespace quotas for users.
Describes the password profile parameters that are assigned to the user.
Displays the resource limits for the current user.
Displays all profiles and their limits.
Lists the cost for each resource.
Lists session information for each current session. Includes user name.
Lists user session statistics.
Displays decoded statistic names for the statistics shown in the
Describes users who can assume the identity of other users.
The following sections present some example of using these views, and assume a database in which the following statements have been executed:
CREATE PROFILE clerk LIMIT SESSIONS_PER_USER 1 IDLE_TIME 30 CONNECT_TIME 600; CREATE USER jfee IDENTIFIED BY wildcat DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp_ts QUOTA 500K ON users PROFILE clerk; CREATE USER dcranney IDENTIFIED BY bedrock DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp_ts QUOTA unlimited ON users; CREATE USER userscott IDENTIFIED BY scott1;
Oracle9i SQL Reference for complete descriptions of the above data dictionary and dynamic performance views
The following query lists users and their associated information as defined in the database:
SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS; USERNAME PROFILE ACCOUNT_STATUS --------------- --------------- --------------- SYS DEFAULT OPEN SYSTEM DEFAULT OPEN USERSCOTT DEFAULT OPEN JFEE CLERK OPEN DCRANNEY DEFAULT OPEN
All passwords are encrypted to preserve security. If a user queries the
PASSWORD column, that user is not be able to determine another user's password.
The following query lists all tablespace quotas specifically assigned to each user:
SELECT * FROM DBA_TS_QUOTAS; TABLESPACE USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS ---------- --------- -------- ---------- ------- ---------- USERS JFEE 0 512000 0 250 USERS DCRANNEY 0 -1 0 -1
When specific quotas are assigned, the exact number is indicated in the
MAX_BYTES column. Note that this number is always a multiple of the database block size, so if you specify a tablespace quota that is not a multiple of the database block size, it is rounded up accordingly. Unlimited quotas are indicated by "-1".
The following query lists all profiles in the database and associated settings for each limit in each profile:
SELECT * FROM
DBA_PROFILESORDER BY PROFILE; PROFILE RESOURCE_NAME RESOURCE LIMIT ----------------- --------------- ---------- -------------- CLERK COMPOSITE_LIMIT KERNEL DEFAULT CLERK FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT CLERK PASSWORD_LIFE_TIME PASSWORD DEFAULT CLERK PASSWORD_REUSE_TIME PASSWORD DEFAULT CLERK PASSWORD_REUSE_MAX PASSWORD DEFAULT CLERK PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT CLERK PASSWORD_LOCK_TIME PASSWORD DEFAULT CLERK PASSWORD_GRACE_TIME PASSWORD DEFAULT CLERK PRIVATE_SGA KERNEL DEFAULT CLERK CONNECT_TIME KERNEL 600 CLERK IDLE_TIME KERNEL 30 CLERK LOGICAL_READS_PER_CALL KERNEL DEFAULT CLERK LOGICAL_READS_PER_SESSION KERNEL DEFAULT CLERK CPU_PER_CALL KERNEL DEFAULT CLERK CPU_PER_SESSION KERNEL DEFAULT CLERK SESSIONS_PER_USER KERNEL 1 DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED DEFAULT PRIVATE_SGA KERNEL UNLIMITED DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED DEFAULT CPU_PER_CALL KERNEL UNLIMITED DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED DEFAULT CONNECT_TIME KERNEL UNLIMITED DEFAULT IDLE_TIME KERNEL UNLIMITED DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED DEFAULT CPU_PER_SESSION KERNEL UNLIMITED DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED 32 rows selected.
The following query lists all current sessions, showing the Oracle user and current UGA (user global area) memory use for each session:
SELECT USERNAME, VALUE || 'bytes' "Current UGA memory" FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name WHERE sess.SID = stat.SID AND stat.STATISTIC# = name.STATISTIC# AND name.NAME = 'session uga memory'; USERNAME Current UGA memory ------------------------------ --------------------------------------------- 18636bytes 17464bytes 19180bytes 18364bytes 39384bytes 35292bytes 17696bytes 15868bytes USERSCOTT 42244bytes SYS 98196bytes SYSTEM 30648bytes 11 rows selected.
To see the maximum UGA memory ever allocated to each session since the instance started, replace
'session uga memory' in the query above with
'session uga memory max'.