Home arrow Oracle arrow Page 2 - Profiles, Password and Resource Control for Oracle Database XE

Profiles and Resource Control - Oracle

In this fifth part of a ten-part series on securing Oracle Database XE, you'll learn about profiles as they pertain to password and resource control. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

  1. Profiles, Password and Resource Control for Oracle Database XE
  2. Profiles and Resource Control
By: Apress Publishing
Rating: starstarstarstarstar / 2
January 06, 2011

print this article



The list of resource-control profile options that can appear afterCREATE PROFILE profilename LIMITare explained in Table 31-4. Each of these parameters can either be an integer,UNLIMITED, orDEFAULT.

Table 31-4. Resource-Related Profile Parameters


Resource Parameter



Maximum number of sessions a user can simultaneously have


Maximum CPU time allowed per session, in hundredths of a second


Maximum CPU time for a statement parse, execute, or fetch operation, in hundredths of a second


Maximum total elapsed time, in minutes


Maximum continuous inactive time in a session, in minutes, while a query or other operation is not in progress


Total number of data blocks read per session, either from memory or disk


Maximum number of data blocks read for a statement parse, execute, or fetch operation


Total resource cost, in service units, computed as a composite weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_ READS_PER_SESSION, and PRIVATE_SGA


Maximum amount of memory a session can allocate in the shared pool, in bytes, kilobytes, or megabytes


As with the password-related parameters,UNLIMITEDmeans that there is no bound on how much of the given resource can be used.DEFAULTmeans that this parameter takes its values from theDEFAULTprofile.

TheCOMPOSITE_LIMITparameter allows you to control a group of resource limits when the types of resources typically used varies widely by type; it allows a user to use a lot of CPU time but not much disk I/O during one session, and vice versa, during another session, without being disconnected by the policy.

By default, all resource costs are zero:

SQL> select * from resource_cost;

RESOURCE_NAME                  UNIT_COST
----------------------------- ----------
CPU_PER_SESSION                        0 LOGICAL_READS_PER_SESSION              0 CONNECT_TIME                           0 PRIVATE_SGA                            0
4 rows selected.

To adjust the resource cost weights, use theALTER RESOURCE COSTcommand. In this example, we change the weightings so thatCPU_PER_SESSIONfavors CPU usage over connect time by a factor of 25 to 1; in other words, a user will be disconnected more likely because of CPU usage than because of connect time:

SQL> alter resource cost
  2      cpu_per_session 50
  3      connect_time 2;
Resource cost altered.
SQL> select * from resource_cost;

---------------------------- ---------- 
CPU_PER_SESSION                      50 LOGICAL_READS_PER_SESSION             0 CONNECT_TIME                          2 PRIVATE_SGA                           0
4 rows selected.

The next step is to create a new profile or modify an existing profile to use a composite limit:

SQL> create profile lim_comp_cpu_conn limit
  2      composite_limit 250;
Profile created.

As a result, users assigned to the profileLIM_COMP_CPU_CONNwill have their session resources limited using the following formula to calculate cost:

composite_cost = (50 * CPU_PER_SESSION) + (2 * CONNECT_TIME);

In Table 31-5, we provide some examples of resource usage to see if the composite limit of 250 is exceeded.

Table 31-5. Resource Usage Scenarios  


CPU (Seconds)

Connect (Seconds)

Composite Cost




(50 * 5) + (2 * 100) = 450




(50 * 2) + (2 * 30) = 160




(50 * 1) + (2 * 150) = 350




(50 * 2) + (2 * 5) = 110



The parametersPRIVATE_SGAandLOGICAL_READS_PER_SESSIONare not used in this particular example, so unless they are specified otherwise in the profile definition, they default to whatever their value is in theDEFAULTprofile. The goal of using composite limits is to give users some leeway in the types of queries or DML they run. On some days, they may run a lot of queries that perform numerous calculations but donít access a lot of table rows; on other days, they may do a lot of full table scans but donít stay connected very long. In these situations, you donít want to limit a user by a single parameter, but instead by total resource usage weighted by the availability of each resource on the server.

Please check back next week for the next part of this series.

>>> More Oracle Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: