oracle查询和终止正在运行的存储过程

1.查询正在运行的存储过程
select  name  from v$db_object_cache where type like '%PROCE%' and locks >0 and pins >0;    

2.找到对应存储过程的sid
select sid from v$access o where o.OBJECT like 'SP_S_REPORT_MOBILE%'   （参数是存储过程名称）

3.根据sid找到对应的serial#
select sid,serial# from v$session a WHERE A.SID=sid

4.终止运行存储过程
alter system kill session 'sid,serial#'  (例如：alter system kill session '1061,25354')

查阻塞
SELECT l.session_id sid, s.serial#,a.sql_text,l.locked_mode,l.oracle_username,
       s.user#,l.os_user_name,s.machine,s.terminal,a.action
  FROM v$sqlarea a,v$session s, v$locked_object l
 WHERE l.session_id = s.sid AND s.prev_sql_addr = a.address;
 
 --找出执行慢的语句 前20条
 SELECT *
  FROM  (SELECT  to_char(a.sql_fulltext) sql_text1 ,module,sql_id,
         disk_reads / DECODE (executions, 0, 1, executions) AS tt
        FROM v$sqlarea a
        WHERE LENGTH(A.SQL_FULLTEXT)<4000
        ORDER BY tt DESC)
WHERE ROWNUM <= 20

查看表空间大小
select a.tablespace_name,total,free,total-free used from
(select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
  group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
  group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;