一、查看连接的IP

select sys_context('userenv','host') from dual;
SELECT UTL_INADDR.get_host_address('www.anysql.net') from dual  ----------------------依赖于DNS服务器
select sys_context('userenv','ip_address') from dual;  ------------查询当前机器的IP地址

二、锁查询

SELECT p.spid, a.serial#, c.object_name, b.session_id, b.oracle_username,  b.os_user_name FROM v$process p, v$session a, v$locked_object b, all_objects c WHERE p.addr = a.paddr AND a.process = b.process   AND c.object_id = b.object_id;

或

SELECT p.spid, a.serial#, c.object_name, b.session_id, b.oracle_username, b.os_user_name,a.MACHINE  FROM v$process p, v$session a, v$locked_object b, all_objects c
 WHERE p.addr = a.paddr AND a.process = b.process  AND c.object_id = b.object_id；

三、杀会话

SELECT sid,SERIAL# ,process FROM V$SESSION   WHERE  sid=10275 ; 或 SELECT sid,SERIAL# ,process FROM V$SESSION   WHERE SQL_ID= 'gxat9gzgfh02c';  （查询出来多个）   
select sid,serial#,username ,osuser from v$session where (sql_hash_value,sql_address)=(select hash_value,address from v$sqlarea where sql_id='gxat9gzgfh02c');

掉这个进程  alter system kill session 'sid,serial#';   immediate;

查看pid,kill掉

select spid, osuser, s.program  from v$session s,v$process p where s.paddr=p.addr and s.sid=1458 ;
#kill 10422;或#kill -9 10422

四、查询资源消耗高的语句

根据top的pid
select s.sid,s.serial#,p.con_id from v$session s,v$process p where s.paddr=p.addr and p.spid='251082';   
查出sid定位SQL
select sql_text from v$sqltext a where a.hash_value=(select sql_hash_value from v$session b where b.sid='1465') order by piece;