| sql query log [message #133605] |
Sat, 20 August 2005 00:16  |
owais303
Messages: 19
Registered: July 2005
Location: India
|
Junior Member |
|
|
hi,
v$sql provides only the query text,is it possible to have details like 1.the ip of the client machine from where the queries were fired
2.Query date and database user name
and how to enable query log generation in oracle 9i
Thanks
|
|
|
|
| Re: sql query log [message #133637 is a reply to message #133605] |
Sat, 20 August 2005 08:45  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Look into enabling auditing. It is covered in the oracle documentation.
You may also consider some application level instrumentation.
|
|
|
|
| Re: sql query log [message #133724 is a reply to message #133605] |
Sun, 21 August 2005 22:23 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
You can use v$session and join by the adderesshash/sqlhash to v$sql. V$session has all the information you need.
This is one of the queries I use in one of our monitoring modules:(I'm writing this by heart - don't have to code to cppy and paste, but you could change it to fit your needs... check the syntax though)
select distinct s.sid, UPPER (client_info), UPPER (terminal) as OS_terminal,UPPER (username) as oracle_user,s.schemaname as Schema_user,s.OSuser, --s.process as OS_ProcessID,UPPER (machine) as OS_machine_name, logon_time, UPPER (program), s.status as Session_Status, AC.owner, ac.object, ac.type,
decode ( s.command , 3 , 'SELECT', 2,'INSERT', 6, 'UPDATE',7,'DELETE', 44,'COMMIT',45,'ROLLBACK',47,'PLsql eXECUTE' ,s.command ), vsql.sql_text, vsql.optimizer_cost, vsql.cpu_time, vsql.optimizer_mode
from V$SQL vsql, v$session s, v$access ac
where s.sql_hash_value = vsql.hash_value
and s.sql_address = vsql.address
AND ac.sid = s.sid
and s.username is not null
and ac.owner not in('SYS', 'SYSTEM')
AND AC.OBJECT <>'SYNONYM'
AND AC.TYPE NOT IN('SYNONYM','NON-EXISTENT')
--AND machine <>'domain\computer_name'
--AND s.OSuser <>'user_name'
--and rownum<10
-- order by sqltext.piece
Hope this helps.mj
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
