Solved

Cannot select V$SESSION in before LOGOFF trigger

Posted on 2007-12-06
4
1,049 Views
Last Modified: 2008-02-01
Do you know why I cannot compile this trigger?

CREATE OR REPLACE TRIGGER LOGOFF_TRIGGER
BEFORE LOGOFF ON DATABASE
DECLARE
BEGIN
  insert into TEST
  (USERNAME)
  select distinct USERNAME
  from V$SESSION;
END;
/
0
Comment
Question by:Zopilote
  • 2
4 Comments
 
LVL 9

Expert Comment

by:joebednarz
Comment Utility
Not sure "why" it does that... but try this instead:

create trigger logoff_trigger
before logoff on database
begin
  insert into test values(sys_context('userenv','session_user'));
end;
/
0
 
LVL 5

Author Comment

by:Zopilote
Comment Utility
Problem solved. No direct grant.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 50 total points
Comment Utility
whomever owns the trigger doesn't have select access on v$session.

I was able to compile your trigger fine and it ran correctly as well.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
sorry, didn't see your post.  yes, you are correct, that's "why"

sys_context is a good workaround too
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now