-
Hello DBAs:
Using Top Command on Unix RS/6000 I was able to figure out the process ID costing lots of CPU..
oracle@CG_JDA1:/home/oracle/admin/ODBMSPRD/udump_> top
USER PID %CPU %MEM SZ RSS TTY STAT STIME TIME COMMAND
oracle 148158 39.8 0.0 27172 13080 - A 09:59:29 4:02 oracleAAMPROD (
oracle 56374 7.9 0.0 32356 16520 - A 09:03:07 9:46 oracleODBMSPRD
Using v$process and v$session, I was also able to find out the machine and the user (application user)..
SQL> l
1 select substr(s.username,1,8) username, substr(s.osuser,1,10) osuser, s.process osprocess, p.sp
2 substr(s.machine,1,30) host,substr(s.terminal,1,30) terminal, s.type, s.sid, s.serial#,
3 substr(s.program ,1,25) program
4 from v$session s, v$process p
5 where p.addr=s.paddr
6* and p.spid = &spid
SQL> /
Enter value for spid: 56374
old 6: and p.spid = &spid
new 6: and p.spid = 56374
USERNAME OSUSER OSPROCESS SPID HOST
-------- ---------- --------- --------- ------------------------------
TERMINAL TYPE SID SERIAL#
------------------------------ ---------- ---------- ----------
PROGRAM
-------------------------
COTPROD Anon014 1128:1434 56374 CG_CITRIX\CG_CITRIX1
CG_CITRIX1 USER 7 4474
uniface.exe
Since it's the application user (Application is on Citrix), We can get only user as Anonxxx (anonymous), but I can track from Terminal which is same for all Anonymous users..
Can I further query the database to find out what the user is doing? I mean which SQL or Procedure's is he/she running?
Thanks, ST2000
-
Yes. You can see the running SQL statement from the following statement:
select a.osuser, c.spid, a.username, a.sid, a.status,
a.program, b.sql_text
from v$session a, v$sqltext_with_newlines b, v$process c
where a.sql_address = b.address
and a.sql_hash_value = b.hash_value
and a.type != 'BACKGROUND'
and a.paddr = c.addr
order by a.status, a.osuser, a.username, a.sid, b.piece ;
