这是程序:
CREATE OR REPLACE PROCEDURE P_MONITOR
--(AN_MINUTES NUMBER DEFAULT 60)
/*******************************************
存储过程用途:识别出系统中超过一定空闲连接时间(
AS_MINUTES)的用户,并将其kill掉参数:
AN_MINUTES 空闲时间数,单位为分钟,默认为60分钟
********************************************/
AS
v_str VARCHAR2(200);
v_username v$session.username%TYPE;
v_status v$session.status%TYPE;
v_machine v$session.machine%TYPE;
v_operators varchar2(200);
CURSOR C_users IS
SELECT s.username,
s.status, s.machine, 'alter system kill session '
||''''||s.sid||','||s.serial# ||'''' operates
FROM v$session s, v$process p
WHERE TYPE = 'USER'
AND p.addr = s.paddr
AND status != 'KILLED'
AND SUBSTR (machine, 1, 7) NOT IN ('RUNHEIT')
AND last_call_et > 3600
ORDER BY last_call_et desc;BEGINLOOP
FETCH C_users INTO v_username,v_status,v_machine,v_operators;
EXIT WHEN C_users%NOTFOUND;
v_str := v_operators;
EXECUTE IMMEDIATE v_str;
END LOOP;
END P_MONITOR;----------------------
我找不出错误,请朋友指点下!
CREATE OR REPLACE PROCEDURE P_MONITOR
--(AN_MINUTES NUMBER DEFAULT 60)
/*******************************************
存储过程用途:识别出系统中超过一定空闲连接时间(
AS_MINUTES)的用户,并将其kill掉参数:
AN_MINUTES 空闲时间数,单位为分钟,默认为60分钟
********************************************/
AS
v_str VARCHAR2(200);
v_username v$session.username%TYPE;
v_status v$session.status%TYPE;
v_machine v$session.machine%TYPE;
v_operators varchar2(200);
CURSOR C_users IS
SELECT s.username,
s.status, s.machine, 'alter system kill session '
||''''||s.sid||','||s.serial# ||'''' operates
FROM v$session s, v$process p
WHERE TYPE = 'USER'
AND p.addr = s.paddr
AND status != 'KILLED'
AND SUBSTR (machine, 1, 7) NOT IN ('RUNHEIT')
AND last_call_et > 3600
ORDER BY last_call_et desc;BEGINLOOP
FETCH C_users INTO v_username,v_status,v_machine,v_operators;
EXIT WHEN C_users%NOTFOUND;
v_str := v_operators;
EXECUTE IMMEDIATE v_str;
END LOOP;
END P_MONITOR;----------------------
我找不出错误,请朋友指点下!
解决方案 »
- 急!实际返回的行数走出请求的行数
- 分数不多,但是值得大家进来讨论
- Oracle.DataAccess.dll 的问题
- 关于数据字典问题
- oracle 返回的select 结果集的proc 如何写?
- pl/sql 写一段从数据库曲值生成txt或csv文件的程序,我写的为什么不对??
- 我现在条件恶劣 买ORACLE的基础书都没有 希望哪位能给我介绍在网上的电子书籍或者好的网站
- [100分]如何记录主数据库一段时间内的动作,并且把动作内容导出?
- 这样的查询Sql语句怎么写
- oracle如何将表空间A的数据备份还原到表空间B上去
- 一存储过程,提示缺失右括号问题
- 创建索引后,如果数据量大,如何能解决同步索引慢的问题!!
||''''||s.sid||','||s.serial# ||'''' operates ----->
试下:
s.status, s.machine, 'alter system kill session '
||''''||to_char(s.sid)||','||s.serial# ||'''' operates
这样才能保证游标是有数据的
你说错了吧,fetch..... 应该放在循环里面才对啊,因为每行的fecth...都是变化的
打开游标后,一定要先取出数据,这时用fetch
再循环的时候,也需要用fetch
再进入循环之前不用fetch,游标不能定位
CREATE OR REPLACE PROCEDURE P_MONITOR
--(AN_MINUTES NUMBER DEFAULT 60)
/*******************************************
存储过程用途:识别出系统中超过一定空闲连接时间(
AS_MINUTES)的用户,并将其kill掉参数:
AN_MINUTES 空闲时间数,单位为分钟,默认为60分钟
********************************************/
AS
v_str VARCHAR2(200);
v_username v$session.username%TYPE;
v_status v$session.status%TYPE;
v_machine v$session.machine%TYPE;
v_operators varchar2(200);
CURSOR C_users IS
SELECT s.username,
s.status, s.machine, 'alter system kill session '
||''''||s.sid||','||s.serial# ||'''' operates
FROM v$session s, v$process p
WHERE TYPE = 'USER'
AND p.addr = s.paddr
AND status != 'KILLED'
AND SUBSTR (machine, 1, 7) NOT IN ('RUNHEIT')
AND last_call_et > 3600
ORDER BY last_call_et desc;BEGIN
OPEN C_users;
LOOP
FETCH C_users INTO v_username,v_status,v_machine,v_operators;
EXIT WHEN C_users%NOTFOUND;
v_str := v_operators;
EXECUTE IMMEDIATE v_str;
END LOOP;
CLOSE C_users;END P_MONITOR;
--(AN_MINUTES NUMBER DEFAULT 60)
/*******************************************
存储过程用途:识别出系统中超过一定空闲连接时间(
AS_MINUTES)的用户,并将其kill掉参数:
AN_MINUTES 空闲时间数,单位为分钟,默认为60分钟
********************************************/
AS
v_str VARCHAR2(200);
v_username v$session.username%TYPE;
v_status v$session.status%TYPE;
v_machine v$session.machine%TYPE;
v_operators varchar2(200);
CURSOR C_users IS
SELECT s.username,
s.status, s.machine, 'alter system kill session '
||''''||s.sid||','||s.serial# ||'''' operates
FROM v$session s, v$process p
WHERE TYPE = 'USER'
AND p.addr = s.paddr
AND status != 'KILLED'
AND SUBSTR (machine, 1, 7) NOT IN ('RUNHEIT')
AND last_call_et > 3600
ORDER BY last_call_et desc;BEGIN
OPEN C_users;
LOOP
FETCH C_users INTO v_username,v_status,v_machine,v_operators;
EXIT WHEN C_users%NOTFOUND;
v_str := v_operators;
EXECUTE IMMEDIATE v_str;
END LOOP;
CLOSE C_users;END P_MONITOR;
--(AN_MINUTES NUMBER DEFAULT 60)
/*******************************************
存储过程用途:识别出系统中超过一定空闲连接时间(
AS_MINUTES)的用户,并将其kill掉参数:
AN_MINUTES 空闲时间数,单位为分钟,默认为60分钟
********************************************/
AS
v_str VARCHAR2(200);
v_username v$session.username%TYPE;
v_status v$session.status%TYPE;
v_machine v$session.machine%TYPE;
v_operators varchar2(200); CURSOR C_users IS
SELECT s.username,
s.status, s.machine, 'alter system kill session '
||''''||s.sid||','||s.serial# ||'''' operates
FROM v$session s, v$process p
WHERE TYPE = 'USER'
AND p.addr = s.paddr
AND status != 'KILLED'
AND SUBSTR (machine, 1, 7) NOT IN ('RUNHEIT')
AND last_call_et > 3600
ORDER BY last_call_et desc; BEGIN
OPEN C_users;
FETCH C_users INTO v_username,v_status,v_machine,v_operators;
LOOP
FETCH C_users INTO v_username,v_status,v_machine,v_operators;
EXIT WHEN C_users%NOTFOUND;
v_str := v_operators;
EXECUTE IMMEDIATE v_str;
END LOOP;
CLOSE C_users; END P_MONITOR; 这样应该就对了
, s.status
, s.machine
, 'alter system kill session ' ||''''||s.sid||','||s.serial# ||'''' operates
from v$session s
, v$process p
where p.addr = s.paddr
and s.TYPE = 'USER'
and status != 'killed'
and substr (machine, 1, 7) not in ('runheit')
and last_call_et > 3600
order by last_call_et desc;你的query有问题,在我机器上跑没通过,好像有特殊字符,用上面这个替换掉你的sql再编译一次。有错误的话show err以下贴出来