how can i find out oracle client sql 's from unix pid

[beksalur]

hi all,
i wonder how can i find out the sql statements which Oracle Clients executes from their respective unix process id(pid).

Thanks in advance.

 

[sem]

Try this query:

select p.spid, t.SQL_TEXT
from v$session s, v$process p, v$sqltext t
where p.pid=s.sid and s.sql_address=t.address
order by p.spid, t.piece

Regards, Dima

 

[sybaseguru]

Or to get more details try the following:

select substr(s.username,1,10) username
        , p.spid spid
        , s.sid||','|| s.serial# sidserial
        , s.process, s.program, s.machine, s.osuser, s.status
        ,decode(s.command
                ,1,'Create Table'
                ,2,'Insert'
                ,3,'Select'
                ,4,'Create Cluster'
                ,5,'Alter Cluster'
                ,6,'Update'
                ,7,'Delete'
                ,8,'Drop'
                ,9,'Create Index'
                ,10,'Drop Index'
                ,11,'Alter Index'
                ,12,'Drop Table'
                ,15,'Alter Table'
                ,17,'Grant'
                ,18,'Revoke'
                ,19,'Create Synonym'
                ,20,'Drop Synonym'
                ,21,'Create View'
                ,22,'Drop View'
                ,26,'Lock Table'
                ,27,'No Operation'
                ,28,'Rename'
                ,29,'Comment'
                ,30,'Audit'
                ,31,'NoAudit'
                ,32,'Create External DB'
                ,33,'Drop External DB'
                ,34,'Create Database'
                ,35,'Alter Database'
                ,36,'Create Rollback Seg'
                ,37,'Alter Rollback Seg'
                ,38,'Drop Rollback Seg'
                ,39,'Create Tablespace'
                ,40,'Alter Tablespace'
                ,41,'Drop Tablespace'
                ,42,'Alter Session'
                ,43,'Alter User'
                ,44,'Commit'
                ,45,'Rollback'
                ,46,'Savepoint'
                ,'Dont Know') command
        ,t.sql_text
from    v$session s, v$sqltext t, v$process p
where    s.sql_address = t.address(+)
and    s.sql_hash_value = t.hash_value(+)
and    p.pid=s.sid
and    s.username is not null
order by s.username, p.spid, sidserial, command, s.program
        , s.machine, s.osuser, s.status, t.piece

 

[beksalur]

thanks!