Tracing the top SQL

Reference:

1.

$ top
----------------------------------------
PID USERNAME THR PR NCE  SIZE   RES STATE   TIME FLTS    CPU COMMAND
20521 oracle     1 40   0  1.8G  1.7G run     6:37    0 47.77% oracle
20845 oracle     1 40   0  1.8G  1.7G cpu02   0:41    0 40.98% oracle

2.

$ ps -ef|grep 20521, 20845

3.getsql.sql

SELECT   /*+ ORDERED */
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = ‘&pid’))
ORDER BY piece ASC
/

4.sid,serial

SQL> select sid,serial#,username,USERNAME MACHINE from v$session
where
Enter value for spid: 20521
-------------------------------------------------------
old 3: select addr from v$process where spid = &spid)
new 3: select addr from v$process where spid = 20521)

SID SERIAL# USERNAME MACHINE
—————————————————————-
45 38991 HSUSER_V51 hswapjsptl1.hurray.com.cn

5.exec dbms_system.set_sql_trace_in_session

SQL> exec dbms_system.set_sql_trace_in_session(45,38991,true);
-------------------------------------
PL/SQL procedure successfully completed.

6. kill
under system: KILL-9 SPID e.g.kill -9 22702

in Oracle: alter system kill session 'sid,serial#';
           e.g.SQL> alter system kill session '154,56090' immediate;

$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 27 11:09:50 2005
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> select sid,username,status from v$session;
       SID USERNAME                       STATUS
---------- ------------------------------ --------
....
       154 SCOTT                          KILLED
...
30 rows selected.

SQL> select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=&sid);
Enter value for sid: 154
old   1: select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=&sid)
new   1: select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=154)
'KILL-9'||SPID
--------------------
kill -9 22702

SQL> !

Under System normaly this process will be killed under system if not:

$ ps -ef|grep 22702
oracle   22702     1  0 Oct25 ?        00:00:02 oracledanaly (LOCAL=NO)
oracle   12082 12063  0 11:12 pts/1    00:00:00 grep 22702
$ kill -9 22702
$ ps -ef|grep 22702
oracle   12088 12063  0 11:12 pts/1    00:00:00 grep 22702
$ exit

Back to Oracle

SQL> select sid,username,status from v$session;
       SID USERNAME                       STATUS
---------- ------------------------------ --------
...
       154 SCOTT                          KILLED
...
30 rows selected.

SQL> select sid,serial#,username from v$session where sid=154;
       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       154      56090 SCOTT

SQL> alter system kill session '154,56090' immediate;
System altered.

SQL> select sid,serial#,username from v$session where sid=154;
no rows selected