Find top SQL information through operating system processes

Find top SQL information through operating system processes
SELECT 'username: '| s. username | chr (10) | 'schema: '| s. schemaname | chr (10) | 'osuser: '| s. osuser | chr (10) | 'spid: '| p. spid | chr (10) | 'sid: '| s. sid | chr (10) | 'serial #: '| s. serial # | chr (10) | 'Kill STRING: '| ''' | s. sid | ',' | s. serial # | ''' | chr (10) | 'machine: '| s. machine | chr (10) | 'Type: '| s. type | chr (10) | 'SQL _ ID:' | q. SQL _id | chr (10) | 'SQL _ TEXT:' | q. SQL _text FROM v $ session s, v $ process p, v $ SQL q WHERE s. paddr = p. addr AND p. spid = '& PID_FROM_ OS' AND s. SQL _id = q. SQL _id (+) 'username: '| S. USERNAME | CHR (10) | 'schema: '| S. SCHEMANAME | CHR (10) | 'osuser: '| S--------------------------------------------------------------------------------USERNAME: RPTSCHEMA: RPTOSUSER: zedSPID: 54657092SID: 1854 SERIAL #: 35907 kill string: '2017 07' MACHINE: aixTYPE: USERSQL_ID: 4wv8ms79s6m37SQL_TEXT: SELECT '000000' as timekey from dual 'username: '| S. USERNAME | CHR (10) | 'schema: '| S. SCHEMANAME | CHR (10) | 'osuser: '| s SQL> select * from table (dbms_xplan.display_cursor (' & SQL _id '); Enter value for SQL _id: partition 1: select * from table (partition ('& SQL _id') new 1: select * from table (dbms_xplan.display_cursor ('4wv8ms79s6m37 ') PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID 4wv8ms79s6m37, child number 0 ------------------------------------- SELECT '200' as timekey from dual Plan hash value: 1546270724 bytes | Id | Operation | Name | Rows | Cost (% CPU) | Time | --------------------------------------------------------- | 0 | select statement | 2 (100) | 1 | fast dual | 1 | 2 (0) | 00:00:01 | PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------13 rows selected. you can also use SQL> conn/as sysdbaConnected in another way. SQL> oradebug setospid 54657092 Oracle pid: 45, Unix process pid: 54657092, image: oracle @ aix (TNS V1-V3) SQL> oradebug current_ SQL; SELECT '2016' AS TIMEKEY FROM DUAL