Oracle Logbook

July 11, 2008

Logon Trigger

Filed under: Uncategorized — heliodias @ 8:38 pm

Sometimes you need to something happen just after logon, e.g. an alter session , or an audit DML.

So you may use Logon trigger to do this:

 

GRANT ALTER SESSION TO SYSTEM;

GRANT CREATE SESSION TO SYSTEM;

create or replace trigger set_trace after logon on database
begin
if user not in (‘SYS’,’SYSTEM’) then
if user=’GERAL’ THEN
execute immediate ‘alter session set TRACEFILE_IDENTIFIER= ”CLIENT_TRACE10046”’;
execute immediate ‘alter session set timed_statistics=true’;
execute immediate ‘alter session set max_dump_file_size=unlimited’;
execute immediate ‘alter session set events ”10046 trace name context forever, level 12”’;
end if;
end if;
exception
when others then
null;
end;
/

 

GRANT ALTER SESSION TO SYSTEM;

GRANT CREATE SESSION TO SYSTEM;

create or replace trigger GC_set_session after logon on database
begin
if user not in (‘SYS’,’SYSTEM’) then
  execute immediate ‘alter session set session_cached_cursors=250’;
end if;
exception
when others then
null;
end;
/

Advertisements

Create a free website or blog at WordPress.com.