Release 2 (9.2)
Part Number A96521-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Database Administrator's Guide Release 2 (9.2) Part Number A96521-01 |
|
This chapter describes how to control access to an Oracle database, and contains the following topics:
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:
| See Also:
Oracle9i SQL Reference for more information about SQL statements used for managing users |
You create a database user with the CREATE USER statement.To create a 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 (used 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.
Selecting and specifying the method of user authentication is discussed in "User Authentication Methods".
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 SYSTEM tablespace.
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 data_ts, and his quota on that tablespace is 500K.
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.
Advantage:
Disadvantages:
UNLIMITED TABLESPACE privilege. 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. 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 avyrros:
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;
The 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.avyrros is given a 100M quota for the data_ts tablespace.avyrros's quota on the test_ts is revoked.avyrros is assigned the clerk profile.Most non-DBA users can still change their own passwords with the ALTER USER statement, as follows:
ALTER USER andy IDENTIFIED BY swordfish;
No special privileges (other than those to connect to the database) are required for a user to change passwords. Users should be encouraged to change their passwords frequently.
Users must have the ALTER USER privilege to switch between methods of authentication. Usually, only an administrator has this privilege.
| See Also:
"User Authentication Methods" for information about the authentication methods that are available for Oracle users |
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.
| See Also:
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.
|
Note: If a user's schema and associated objects must remain but the user must be denied access to the database, revoke the |
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 jones.
DROP USER jones CASCADE;
| See Also:
"Terminating Sessions" for more information about terminating sessions |
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 including 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.
CREATE USER scott IDENTIFIED BY tiger;
| See Also:
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:
OS_AUTHENT_PREFIX=OPS$
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 OPS$tsmith.
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 scott IDENTIFIED EXTERNALLY;
Using 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.
| See Also:
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 FALSE) to TRUE in the database's initialization parameter file. Setting the initialization parameter REMOTE_OS_AUTHENT to 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.
|
Note: 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:
CREATE USER scott IDENTIFIED GLOBALLY AS 'CN=scott,OU=division1,O=oracle,C=US';
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 USER statement of any typeThe process of creating a schema-independent user is as follows:
CREATE USER appschema INDENTIFIED GLOBALLY AS '';
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 appschema:
OU=division,O=Oracle,C=US
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_USER database 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.
The PROXY_USERS data dictionary view can be queried to see which users are currently authorized to connect through a middle tier.
Use the REVOKE CONNECT THROUGH clause of ALTER USER to disallow a proxy connection.
See Also:
|
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 bill, except payroll.
ALTER USER bill GRANT CONNECT THROUGH appserve WITH ROLE ALL EXCEPT payroll;
To revoke the middle-tier server's (appserve) authorization to connect as user bill, the following statement is used:
ALTER USER bill REVOKE CONNECT THROUGH appserve;
Use the 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:
ALTER USER mary GRANT CONNECT THROUGH midtier AUTHENTICATED USING PASSWORD;
In the above statement, middle-tier server midtier is authorized to connect as mary, and 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 WebDB.
ALTER USER jeff GRANT CONNECT THROUGH WebDB AUTHENTICATED USING DISTINGUISHED NAME;
Optionally, the middle-tier server can be authorized to present an entire certificate (containing the distinguished name). This is illustrated in the following statement:
ALTER USER jeff GRANT CONNECT THROUGH WebDB AUTHENTICATED USING CERTIFICATE;
Passing the entire certificate costs time in authentication. However, some applications use other information contained in the certificate.
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:
| See Also:
Oracle9i SQL Reference.for more information about the SQL statements used for managing profiles |
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 SET RESOURCE_LIMIT = TRUE;
An 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 RESOURCE_LIMIT parameter.
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 clerk:
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 DEFAULT profile.
Each database has a DEFAULT profile, and its limits are used in two cases:
DEFAULT profile.DEFAULT profile.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:
ALTER PROFILE default LIMIT ...;
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 clerk profile:
ALTER PROFILE clerk LIMIT CPU_PER_CALL default LOGICAL_READS_PER_SESSION 20000;
In addition to being able to use the CREATE or 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 COMPOSITE_LIMIT clause:
CREATE PROFILE clerk LIMIT COMPOSITE_LIMIT 20000 SESSIONS_PER_USER 2 CPU_PER_CALL 1000;
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.
| See Also:
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 statement.You must have the ALTER RESOURCE system privilege.The following example assigns weights to the CPU_PER_SESSION and LOGICAL_READS_PER_SESSION resources.
ALTER RESOURCE COST CPU_PER_SESSION 1 LOGICAL_READS_PER_SESSION 50;
The weights establish this cost formula for a session:
cost = (1 * CPU_PER_SESSION) + (50 * LOGICAL_READS_PER_SESSION)
where the values of CPU_PER_SESSION and 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 CONNECT_TIME and PRIVATE_SGA, these resources do not appear in the formula.
See Also:
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 CASCADE option.
The following statement drops the profile clerk, even though it is assigned to a user:
DROP PROFILE clerk CASCADE;
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:
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;
| See Also:
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_PROFILES
ORDER 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'.
|
 Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|