I need some help with writing a query which will tell me what user is executing what sql. There is one user who has about 35 people logged in with same username and I need to determine if they are executing some sql or doing nothing I just need the first line of sql. I tried to join v$session and v$sqltext but it is not giving me exactly what I need. any help would be really appreciated.
(oracle8i on sun solaris
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
That query was perfect. But I need some more info. What I need to do is run this query every five minutes and store the data in a table and flat file. But I do not want the data over written every time the script is executed. So can you please help me to create a script which can do this on OS and DB level.
You can do it in three steps:
1/ create a table to keep your data
2/ create a procedure to get your date
3/ create a job to get the data at regular intervals.
I write the following from memory, so they may be some syntax error, check it:
<BLOCKQUOTE><CODE>
create table watch_session (
mydate date,
username varchar2(30),
sid number,
status varchar2(8),
sql_text varchar2(1000),
primary key (mydate, username, sid)
)
/
create or replace procedure get_sessions is
begin
insert into watch_sessions (
select sysdate, s.username, s.sid, s.status, q.sql_text
from v$session s, v$sql q
where q.address (+) = s.sql_address
and q.hash_value (+) = s.sql_hash_value);
commit;
end;
/
var jobno number;
exec dbms_job.submit (:jobno, 'get_sessions', sysdate, 'sysdate+1/(24*12)');
select * from user_jobs where job = :jobno;
</CODE></BLOCKQUOTE>
Business Accounts
Answer for Membership
by: adixitPosted on 2004-02-06 at 21:58:27ID: 10296946
select s.username, s.sid, s.status, q.sql_text
from v$session s, v$sql q
where q.address (+) = s.sql_address
and q.hash_value (+) = s.sql_hash_value
order by 1,2
/