trigger errors out

Hi all,

Would any one plase tell me what is wrong with this script:
ALTER trigger logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
update stats$user_log
set last_action = (select action from v$session where sys_context('USERENV','SESSIONID') = audsid)
where sys_context('USERENV','SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
update stats$user_log
set last_program = (select program from v$session where sys_context('USERENV','SESSIONID') = audsid)
where sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
update stats$user_log
set last_module = (select module from v$session where sys_context('USERENV','SESSIONID') = audsid)
where sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff day
-- ***************************************************
update stats$user_log
set logoff_day = sysdate
where sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff time
-- ***************************************************
update stats$user_log
set logoff_time = to_char(sysdate, 'hh24:mi:ss')
where sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Compute the elapsed minutes
-- ***************************************************
update stats$user_log
set elapsed_minutes = round((logoff_day - logon_day)*1440)
where sys_context('USERENV','SESSIONID') = session_id;
COMMIT;
END;
/

I still get error as followed:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
7/39 PL/SQL: ORA-00942: table or view does not exist
12/1 PL/SQL: SQL Statement ignored
13/41 PL/SQL: ORA-00942: table or view does not exist
18/1 PL/SQL: SQL Statement ignored
19/39 PL/SQL: ORA-00942: table or view does not exist

the table is exist because other log on trigger works fine!!
records from table:
USER_ID SESSION_ID HOST LAST_PROGRAM LAST_ACTION LAST_MODULE LOGON_DAY LOGON_TIME LOGOFF_DA LOGOFF_TIM ELAPSED_MINUTES
------------------------------ ---------- ------------------------------ ------------------------------------------------ -------------------------------- -------------------------------- --------- ---------- --------- ---------- ---------------
2032 1260 6434 14-AUG-08 09:07:10
ADMIN 1261 6434 14-AUG-08 09:08:04
2032 1262 6434 14-AUG-08 09:20:22

3 rows selected.

Thanks
Pat

who owns the offending tables?
why don't you fully qualify the table names?

You need to do a direct grant on v$session to the schema that owns the trigger.
By the way you could have made the trigger much cleaner.

Code:

---

ALTER trigger logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
  thisSession v$session%ROWTYPE;
BEGIN
  SELECT *
    INTO thisSession
    WHERE sys_context('USERENV','SESSIONID') = audsid;

  UPDATE stats$user_log
      SET last_action    = thisSession.action,
          last_program    = thisSession.program,
          last_module    = thisSession.module,
          logoff_day      = SYSDATE,
          logoff_time    = TO_CHAR(SYSDATE, 'hh24:mi:ss'),
          elapsed_minutes = ROUND((SYSDATE - logon_day)*1440)
    WHERE sys_context('USERENV','SESSIONID') = session_id;
  COMMIT;
END;
/

---

Hi,

the table is belong to same user that created the trigger which is admin user!! but how can i find where it is located now because i can't seem to find the table or trigger in system but it is exist!!

Please guide me!!

thanks
pat

problem is the admin account i have is not sufficient to give priveliges to the schema owner where the table and triggere resides... how can i grant the permission without having sysDBA rights!! i can execute and select from V$session but when i try to run

grant select on v$session to 2032
*
ERROR at line 1:
ORA-01031: insufficient privileges

Quote:

---

Originally Posted by midora

how can i grant the permission without having sysDBA rights!!

---

You can't! You might be able to get some of the information using context and not v$session.

i.e. SYS_CONTEXT('USERENV','HOST')

http://www.oracle-base.com/articles/...dAddresses.php

also for the following trigger Can i script to exclude certain userid:::

CREATE OR REPLACE TRIGGER LOGON_AUDIT_TRIGGER AFTER
LOGON ON DATABASE BEGIN
insert into tablename values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);

thanks
Pat

So you are asking if you can add an if then else block to a trigger? :confused:

Code:

---

CREATE OR REPLACE TRIGGER LOGON_AUDIT_TRIGGER
AFTER LOGON ON DATABASE
BEGIN
  IF USER != 'RUMPLESTILTSKIN'
  THEN
      insert into tablename values(
        user,
        sys_context('USERENV','SESSIONID'),
        sys_context('USERENV','HOST'),
        null,        null,
        null,        sysdate,
        to_char(sysdate, 'hh24:mi:ss'),
        null,        null,  null );
  END IF;
END;
/

---