Useful shortcuts for vi editor

Tuesday 14 October 2014

How to define new profile or change old profile in Oracle?

Please login to database server with ssh as oracle user and run that commands.

sqlplus / as sysdba

SQL> CREATE PROFILE engineer LIMIT 
SESSIONS_PER_USER 1
IDLE_TIME 30
CONNECT_TIME 600;

SQL> exit

-- SESSIONS_PER_USER; Specify the number of concurrent sessions to which you want to limit the user.
-- 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.
-- CONNECT_TIME; Specify the total elapsed time limit for a session, expressed in minutes.

other parameters:
CPU_PER_SESSION; Specify the CPU time limit for a session, expressed in hundredth of seconds.
CPU_PER_CALL; Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.
LOGICAL_READS_PER_SESSION; Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.
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).
PRIVATE_SGA; Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA). Refer to size_clause for information on that clause.
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.

Change current profile
ALTER PROFILE DEFAULT LIMIT IDLE_TIME 60;
ALTER PROFILE DEFAULT LIMIT CONNECT_TIME 6000;
ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 1;

Add this parameter into init.ora file permanently or apply this for already running instance temporary:
Option 1: 
*.resource_limit=TRUE 
Option 2:
alter system set resource_limit = true;