LOGON Trigger In the Schema level to set sort area.

LOGON Trigger In the Schema level to set sort area size parameter value in session level.

Trigger Name: LOGON_TRIG_SORTAREA (Schema Level)

Purpose: To set the sort_area_size to a seccion as soon as connectivity established.

Parameters to be passed: None

Technology: Oracle PL/SQL

sub scripts: None

Code:

create or replace TRIGGER LOGON_TRIG_SORTAREA AFTER LOGON ON SCHEMA

BEGIN

EXECUTE IMMEDIATE (‘alter session set sort_area_size = 2147483648’);

END ;

Line 1) Creating trigger in the scema level , for example if we connect with user scott,

it sets sort_area_size in that user only

Line 2) BEGIN — Begin of the code

Line 3 ) To run the alter command in PL/SQL block ,we use EXECUTE IMMEDIATE command.

once the DB Connection is established with DB using scott user,it sets two parameters.

sort_area_size = 2147483648

(2 GB = 2*1024*1024*1024= 2147483648)

Line 4 &5 ) End of the Trigger Definition.

Trigger created.

SQL> show Errors

No errors.

SQL> select trigger_name from dba_triggers where trigger_name like ‘LOGON%’;

LOGON_TRIG_SORTAREA

See Also: