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:' | | Smuri- -- USERNAME: RPTSCHEMA:RPTOSUSER:zedSPID:54657092SID:1854SERIAL#:35907KILL STRING: '1854 camera 35907 machine: aixTYPE:USERSQL_ID:4wv8ms79s6m37SQL_TEXT: SELECT' 073000' 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: 4wv8ms79s6m37

Old 1: select * from table (dbms_xplan.display_cursor (('& sql_id')

New 1: select * from table (dbms_xplan.display_cursor (('4wv8ms79s6m37')

PLAN_TABLE_OUTPUT

SQL_ID 4wv8ms79s6m37, child number 0

-

SELECT '073000' AS TIMEKEY FROM DUAL

Plan hash value: 1546270724

-

| | 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.

It can also be done in another way

SQL > conn / as sysdba

Connected.

SQL > oradebug setospid 54657092

Oracle pid: 45, Unix process pid: 54657092, p_w_picpath: oracle@aix (TNS V1-V3)

SQL > oradebug current_sql

SELECT '235959' AS TIMEKEY FROM DUAL