1.
2.
3.
[code]
vi traceTop.sql ### add following lines
[/code]
4.
5.
6. enter the pid on top cpu process
7. check the session
8.
top
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1467 oracle 20 0 2598m 86m 28m R 101 1.1 7134:54 oracle
23442 oracle 20 0 2537m 27m 23m R 101 0.3 7151:10 oracle
7826 oracle 20 0 2540m 42m 35m R 99 0.5 8121:54 oracle
##### 确认是远程链接
2.
ps -ef | grep 23442
3.
[code]
vi traceTop.sql ### add following lines
[/code]
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 = '23442'))
ORDER BY piece ASC
/
4.
sqlplus 'as/ sysdba'
5.
sqlplus>@traceTop.sql
6. enter the pid on top cpu process
7. check the session
select s.username, s.osuser, s.sid, s.serial#, p.spid
from v$session s,v$process p
where s.paddr = p.addr
and s.username is not null;
8.
SQL> select sid,serial#,username from v$session where sid=142;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
142 54125 CLEARSPACE
SQL> alter system kill session '142,54125';
System altered.