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?
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 email@example.com
Rohit Nirkhe,Oracle DBA,OCP 8i
Just wanted to thank you all for your valuable help with my questions!!
Click Here to Expand Forum to Full Width