update : 01-05-2017

DBA Script : example_create_profile_resource_parameters.sql

-- | FILE : example_create_profile_resource_parameters.sql |
-- +----------------------------------------------------------------------------+
-- | |
-- | |
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : example_create_profile_resource_parameters.sql |
-- | CLASS : Examples |
-- | PURPOSE : The following script provides syntax on how to create an Oracle |
-- | profile to be used in limiting resources. The profile in this |
-- | example does not provide parameters related to password |
-- | security. |
-- | |
-- | Oracle Database enforces resource limits in the following ways: |
-- | |
-- | (*) If a user exceeds the CONNECT_TIME or IDLE_TIME session |
-- | resource limit, then the database rolls back the current |
-- | transaction and ends the session. When the user process |
-- | next issues a call, the database returns an error. |
-- | |
-- | (*) If a user attempts to perform an operation that exceeds |
-- | the limit for other session resources, then the database |
-- | aborts the operation, rolls back the current statement, |
-- | and immediately returns an error. The user can then |
-- | commit or roll back the current transaction, and must |
-- | then end the session. |
-- | |
-- | (*) If a user attempts to perform an operation that exceeds |
-- | the limit for a single call, then the database aborts the |
-- | operation, rolls back the current statement, and returns |
-- | an error, leaving the current transaction intact. |
-- | |
-- | NOTES: |
-- | |
-- | (*) You can use fractions of days for all parameters that |
-- | limit time, with days as units. For example, 1 hour is |
-- | 1/24 and 1 minute is 1/1440. None of the resource |
-- | parameters are specified in days! |
-- | |
-- | (*) You can specify resource limits for users regardless of |
-- | whether the resource limits are enabled. However, Oracle |
-- | Database does not enforce the limits until you enable |
-- | them. |
-- | |
-- | (*) After you set up the new profile, you must edit your |
-- | INIT.ORA file and set: |
-- | |
-- | RESOURCE_LIMIT = TRUE |
-- | |
-- | if you attempt to limit any of the profile resources |
-- | below: |
-- | |
-- | SESSIONS_PER_USER |
-- | CPU_PER_SESSION |
-- | CPU_PER_CALL |
-- | CONNECT_TIME |
-- | IDLE_TIME |
-- | LOGICAL_READS_PER_SESSION |
-- | COMPOSITE_LIMIT |
-- | PRIVATE_SGA |
-- | |
-- | You can also modify the "RESOURCE_LIMIT" parameter on the |
-- | SYSTEM level via: |
-- | |
-- | ALTER SYSTEM SET RESOURCE_LIMIT = TRUE; |
-- | |
-- | Note though; this will only apply for new users login |
-- | onto the database - not the existing users currently |
-- | logged on. |
-- | |
-- | It is possible to check the current information on this |
-- | parameter by performing the below SELECT: |
-- | |
-- | SELECT * FROM V$PARAMETER |
-- | WHERE NAME = 'resource_limit'; |
-- | |
-- | (*) When specified with a resource parameter (like those |
-- | parameters defined in this example script), UNLIMITED |
-- | indicates that a user assigned this profile can use an |
-- | unlimited amount of this resource. When specified with a |
-- | password parameter, UNLIMITED indicates that no limit has |
-- | been set for the parameter. |
-- | |
-- | (*) Specify DEFAULT if you want to omit a limit for this |
-- | resource in this profile. A user assigned this profile is |
-- | subject to the limit for this resource specified in the |
-- | DEFAULT profile. The DEFAULT profile initially defines |
-- | unlimited resources. You can change those limits with the |
-- | ALTER PROFILE statement. |
-- | |
-- | Any user who is not explicitly assigned a profile is |
-- | subject to the limits defined in the DEFAULT profile. |
-- | Also, if the profile that is explicitly assigned to a |
-- | user omits limits for some resources or specifies DEFAULT |
-- | for some limits, then the user is subject to the limits |
-- | on those resources defined by the DEFAULT profile. |
-- | |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
CONNECT / as sysdba
/*
** +-----------------------------------------------------------------------------------+
** | CREATE PASSWORD PROFILE: developer_profile |
** | --------------------------------------------------------------------------------- |
** | |
** | => SESSIONS_PER_USER : Specify the number of concurrent sessions to |
** | which you want to limit the user. When a user |
** | attempts to create a new session that exceeds |
** | the given threshold, they will not be allowed |
** | to login with that new session and presented |
** | with an ORA-02391 error. |
** | |
** | => CPU_PER_SESSION : Specify the CPU time limit for a session, |
** | expressed in hundredth of seconds. If the user |
** | exceeds this time limit, they are logged off |
** | with an ORA-02392 error. |
** | |
** | => CPU_PER_CALL : Specify the CPU time limit for a call (a parse, |
** | execute, or fetch), expressed in hundredths of |
** | seconds. If the user exceeds this time limit, |
** | they are logged off with an ORA-02393 error. |
** | |
** | => CONNECT_TIME : Specify the total elapsed time limit for a |
** | session, expressed in minutes. When a user |
** | session exceeds the given threshold, they are |
** | logged off and presented with an ORA-02399 |
** | error. |
** | |
** | => IDLE_TIME : Specify the permitted periods of continuous |
** | inactive time during a session, expressed in |
** | minutes. Long-running queries and other |
** | operations are not subject to this limit. When |
** | a user session exceeds the given threshold, |
** | they are logged off and presented with an |
** | ORA-02396 error. |
** | |
** | => LOGICAL_READS_PER_SESSION : Specify the permitted number of data blocks |
** | read in a session, including blocks read from |
** | memory and disk. When a user session exceeds |
** | the given threshold, they are logged off and |
** | presented with an ORA-02394 error. |
** | |
** | => LOGICAL_READS_PER_CALL : Specify the permitted number of data blocks |
** | read for a call to process a SQL statement |
** | (a parse, execute, or fetch). When a user |
** | session exceeds the given threshold, they are |
** | logged off and presented with an ORA-02395 |
** | error. |
** | |
** | => PRIVATE_SGA : Specify the amount of private space a session |
** | can allocate in the shared pool of the system |
** | global area (SGA) specified using the |
** | "size_clause". The size_clause lets you specify |
** | a number of bytes, kilobytes (K), megabytes (M),|
** | gigabytes (G), terabytes (T), petabytes (P), or |
** | exabytes (E) in any statement that lets you |
** | establish amounts of disk or memory space. Use |
** | the size_clause to specify a number or multiple |
** | of bytes. If you do not specify any of the |
** | multiple abbreviations, the integer is |
** | interpreted as bytes. |
** | |
** | NOTE: |
** | This limit applies only if you are using shared |
** | server architecture. The private space for a |
** | session in the SGA includes private SQL and |
** | PL/SQL areas, but not shared SQL and PL/SQL |
** | areas. |
** | |
** | => COMPOSITE_LIMIT : Specify the total resource cost for a session, |
** | expressed in service units. Oracle Database |
** | calculates the total service units as a |
** | weighted sum of CPU_PER_SESSION, CONNECT_TIME, |
** | LOGICAL_READS_PER_SESSION, and PRIVATE_SGA. |
** | |
** +-----------------------------------------------------------------------------------+
*/
/*
** +-----------------------------------------------------------------------------------+
** | If you assign the developer_profile profile to a user (as defined below), the |
** | user is subject to the following limits in subsequent sessions: |
** | |
** | => The user can have any number of concurrent sessions. |
** | => In a single session, the user cannot consume more than 120 seconds |
** | (2 minutes) of CPU time. |
** | => A single call made by the user cannot consume more than 30 seconds of CPU |
** | time. |
** | => A single session cannot last for more than 45 minutes. |
** | => A single session cannot be idle for more than 5 minutes. |
** | => In a single session, the number of data blocks read from memory and disk |
** | is subject to the limit specified in the DEFAULT profile. |
** | => A single call made by the user cannot read more than 1000 data blocks from |
** | memory and disk. |
** | => A single session cannot allocate more than 15 kilobytes of memory in the |
** | SGA. |
** | => In a single session, the total resource cost cannot exceed 5 million |
** | service units. The formula for calculating the total resource cost is |
** | specified by the ALTER RESOURCE COST statement. |
** | => Since the developer_profile profile omits a limit for password limits, the |
** | user is subject to the limits on these resources specified in the DEFAULT |
** | profile. |
** +-----------------------------------------------------------------------------------+
*/
CREATE PROFILE developer_profile LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION 12000
CPU_PER_CALL 3000
CONNECT_TIME 45
IDLE_TIME 5
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K
COMPOSITE_LIMIT 5000000
/

End Script

Oracle Database Error Messages



Oracle Database High Availability Any organization evaluating a database solution for enterprise data must also evaluate the High Availability (HA) capabilities of the database. Data is one of the most critical business assets of an organization. If this data is not available and/or not protected, companies may stand to lose millions of dollars in business downtime as well as negative publicity. Building a highly available data infrastructure is critical to the success of all organizations in today's fast moving economy.

Well, the reason for above error is that i have taken the above script from a 11g database and running it on 10g database. 11g has bring some changes in password management. Below code is executed on 11g and user created successfully, which is expected result.