1.查询当前活动会话数之和

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 1000
--活动会话数之和
select count(*) from v$sessions where state='ACTIVE';
--当前用户分组会话数之和
SELECT user_name,count(*) from v$sessions where state='ACTIVE'
group by user_name
order by 2;

2.查询当前连接的客户端之后

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 1000
--当前连接数据库的客户端主机名和总和
select CLNT_HOST,count(*) from v$sessions 
group by CLNT_HOST
order by 1;
--当前连接数据库的客户端用户之和
select CLNT_HOST,user_name,count(*) from v$sessions 
group by CLNT_HOST,user_name
order by 1,2;

3.查询数据库不同时间的会话数之和

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 1000
--查询不同应用不同时间创建的数据库会话之和,按照时间排序
select create_time,appname,count(*) from v$sessions group by create_time,appname order by 1;
--查询数据库不同创建时间对应的数据库会话数，按照会话排列
select create_time,count(*) from v$sessions group by create_time order by 1;

4.当前活动会话的详细信息

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 5000
set linesize 1000
set long 5000
--查询当前活动会话，不含sql自己，按照时间排序，显示活动会话id，用户名，最近接收信息到当前时间间隔，显示sql语句的前40个字符
SELECT  a.sess_id   sid,
      a.user_name,
      datediff(ss, a.LAST_RECV_TIME,sysdate) last_ela,
	   substr(a.sql_text,0,40) sql_text
 FROM V$SESSIONS a
 where a.STATE='ACTIVE'
 and a.sql_text not like '%SELECT%a.sess_id%a.SESS_SEQ%'
  order by last_ela 
;
--查询当前数据库的活动会话，不显示SQL自己，并以字节为单位，字节小于1000的SQL语句，打印会话id，当前用户名，执行计划的hash值，执行次数，最后一次执行时间和当前系统时间间隔，显示SQL文件前40个字符
select * from (
SELECT distinct a.sess_id  sid,
      a.user_name,
      b.sql_id sql_id,
      c.HASH_VALUE plan_hash_value,
      b.N_EXEC execs,
      datediff(ss, a.LAST_RECV_TIME,sysdate) last_ela,
      substr(a.sql_text,0,40) sql_text
 FROM V$SESSIONS a left join v$sqltext b
 on a.sql_text=b.SQL_TEXT
 left join v$sql_plan c  on b.sql_id=c.sql_id
 where a.STATE='ACTIVE'
 and lengthb(SF_GET_SESSION_SQL(a.SESS_ID))<=1000
 and a.sql_text not like '%SELECT%a.sess_id%a.SESS_SEQ%'
union  all
  SELECT  a.sess_id  sid,
      a.user_name,
	  NULL AS sql_id,
	  NULL as plan_hash_value,
	  NULL as execs,
      datediff(ss, a.LAST_RECV_TIME,sysdate) last_ela,
	  substr(a.sql_text,0,40) sql_text
 FROM V$SESSIONS a
 where a.STATE='ACTIVE'
 and lengthb(SF_GET_SESSION_SQL(a.SESS_ID))>1000
 )
 order by last_ela 
 ;

5.查询当前数据库活动会话

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 1000

SELECT  a.sess_id || ',' || a.SESS_SEQ  sid,
      a.user_name,
      datediff(ss, a.last_send_time,sysdate) last_ela,
      substr(a.SQL_TEXT,1,80) sql_text
 FROM V$SESSIONS a 
 WHERE a.STATE='ACTIVE' and a.sql_text not like '%datediff%last_ela%SESSIONS%'
 order by 4,3
;

6.根据SID杀掉缓冲区SQL

select 'SP_CLOSE_SESSION('||sid||');' from (
SELECT  a.sess_id  sid,
        a.SESS_SEQ seq,
      a.user_name,
      b.CACHE_ITEM sql_id,
      b.HASH_VALUE plan_hash_value,
      datediff(ss, a.last_send_time,sysdate) last_ela,
      substr(a.sql_text,0,20) sql_head,
      SF_GET_SESSION_SQL(a.SESS_ID) sql_text,
      trx_id
 FROM V$SESSIONS a left join v$cachesql b
 on SF_GET_SESSION_SQL(a.SESS_ID)=b.SQL)
 where &SESS_kill;

7.根据SQL_ID查询SQL

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 1000
set linesize 1000
SET LONG 2000

select 
     sql_text
from v$sqltext
where sql_id='&v_sqlid';