Re: Help required for system database trigger

kishanthakker_at_gmail.com wrote:

> Dear Daniel,
>
> How 'Wild Card' solution will help to solve this problem?. I M unable
> to understand. Please Clarify.

I misunderstood your issue. See if this works on your system.

conn / as sysdba

CREATE OR REPLACE TRIGGER kill_session
AFTER LOGON
ON DATABASE DECLARE
  cur_user dba_users.username%TYPE;
BEGIN
   SELECT user
   INTO cur_user
   FROM dual;

   IF cur_user = 'SCOTT' THEN
     RAISE program_error;
   END IF;
END kill_session;
/

conn scott/tiger

But before you do run this query:

col NAME format a30
col VALUE format a20
col DESCRIPTION format a60

SELECT x.ksppinm NAME, y.ksppstvl VALUE, ksppdesc DESCRIPTION FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')

AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND x.ksppinm = '_system_trig_enabled';

You may well need:

ALTER SYSTEM SET "_system_trig_enabled" = TRUE SCOPE=BOTH;

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org