公司某个系统设计不合理,导致数据库经常死锁,所以想写脚本删死锁,但是遇到了点问题,请大家帮忙看看:
DECLARE
MY_SID V$SESSION.SID%TYPE;
MY_SERIAL V$SESSION.SERIAL#%TYPE;
CURSOR MY_CUR IS
SELECT B.SID, B.SERIAL#
FROM V$LOCKED_OBJECT A
JOIN V$SESSION B ON A.SESSION_ID = B.SID;
BEGIN
OPEN MY_CUR;
LOOP
FETCH MY_CUR
INTO MY_SID, MY_SERIAL;
EXIT WHEN MY_CUR%NOTFOUND;
execute immediate 'ALTER SYSTEM KILL SESSION(' || MY_SID || ',' ||
MY_SERIAL || ')';
END LOOP;
CLOSE MY_CUR;
END;红色那句总是报:ORA-00026:missing or invalid session ID
DECLARE
MY_SID V$SESSION.SID%TYPE;
MY_SERIAL V$SESSION.SERIAL#%TYPE;
CURSOR MY_CUR IS
SELECT B.SID, B.SERIAL#
FROM V$LOCKED_OBJECT A
JOIN V$SESSION B ON A.SESSION_ID = B.SID;
BEGIN
OPEN MY_CUR;
LOOP
FETCH MY_CUR
INTO MY_SID, MY_SERIAL;
EXIT WHEN MY_CUR%NOTFOUND;
execute immediate 'ALTER SYSTEM KILL SESSION(' || MY_SID || ',' ||
MY_SERIAL || ')';
END LOOP;
CLOSE MY_CUR;
END;红色那句总是报:ORA-00026:missing or invalid session ID
MY_SID V$SESSION.SID%TYPE;
MY_SERIAL V$SESSION.SERIAL#%TYPE;
MY_SQL VARCHAR2(4000);
CURSOR MY_CUR IS
SELECT B.SID, B.SERIAL#
FROM V$LOCKED_OBJECT A
JOIN V$SESSION B ON A.SESSION_ID = B.SID;
BEGIN
OPEN MY_CUR;
LOOP
FETCH MY_CUR
INTO MY_SID, MY_SERIAL;
EXIT WHEN MY_CUR%NOTFOUND;
MYSQL:='ALTER SYSTEM KILL SESSION('''|| MY_SID ||''','''||MY_SERIAL||''')';
execute immediate MYSQL;
END LOOP;
CLOSE MY_CUR;
END;
引号的问题这里面是'' 为'
execute immediate 'ALTER SYSTEM KILL SESSION(' ''|| MY_SID || ''',' ''||
MY_SERIAL || ''')';
execute immediate 'ALTER SYSTEM KILL SESSION' ''|| MY_SID || ''',' ''||
MY_SERIAL || '';