Monitor User Connectivity

[agilentfg]

Without turning trace on, can someone recommend a way to monitor user access or activity to an Oracle database? We are worried about users performing long-running queries using columns that are not keys or indexed.

Is there a table that has information about a particular user such as last connect time, how long connected, etc?

Thank you.

[santoshym]

Hi

Try V$session

Regards

[rohitsn]

hi,
See without tracing on we can find out which users are executing the sql statements.
U perform the following steps

1) Get the sid,serial# from v$session by running the following query,
sql > select sid,serial#,username,machine from v$session where username='&username';

2) Run the follwing query under user sys to get the sqltext running by that user.Enter the sid and serial# which u got from the previous query.

select sql_text from v$sqltext_with_newlines
where (hash_value,address) in(
select sql_hash_value,sql_address from v$session
where sid=&sid and serial#=&Serial)
order by address,piece


In case of any help please be free to ask me at rohitsn@altavista.com

Regards,
Rohit Nirkhe,Oracle DBA,OCP 8i
rohitsn@altavista.com

[agilentfg]

Just wanted to thank you all for your valuable help with my questions!!