-
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
-
 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?
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;
/
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|