发表于 2009-1-5 19:48
| 
鍦∣racle 8x涓疄鐜拌嚜鍔ㄦ柇寮鍚庡啀杩炴帴
鍙戝竷: 2008-6-30 22:35 | 浣滆: admin | 鏌ョ湅: 8娆
鍦∣racle 8x涓疄鐜拌嚜鍔ㄦ柇寮鍚庡啀杩炴帴
銆愬瓧浣擄細灏 澶с
鍦∣racle 8x涓疄鐜拌嚜鍔ㄦ柇寮鍚庡啀杩炴帴
浣滆咃細- 鏂囩珷鏉ユ簮锛- 鐐瑰嚮鏁帮細203 鏇存柊鏃堕棿锛2007-8-1 
鍦∣racle 8x涓疄鐜拌嚜鍔ㄦ柇寮鍚庡啀杩炴帴
銆鍦ㄥ疄闄呯殑鏁版嵁搴撳簲鐢ㄤ腑锛屾垜浠粡甯搁亣鍒拌繖鏍蜂竴涓棶棰橈紝杩炴帴鍒癘racle鏁版嵁搴撶殑鐢ㄦ埛鍦ㄤ綔浜嗕竴娆℃搷浣滃悗锛屽啀涔熸病鏈夊悗缁搷浣滐紝浣嗗嵈闀挎椂闂存病鏈夊拰鏁版嵁搴撴柇寮杩炴帴銆傚浜庝竴涓皬鍨嬬殑搴旂敤绯荤粺鏉ヨ锛屾湰韬殑杩炴帴鏁扮洰灏辨湁闄愶紝杩欏ソ鍍忔病鏈変粈涔堜弗閲嶇殑鍚庢灉锛屼絾濡傛灉瀵逛簬涓涓ぇ鍨嬬殑鏁版嵁搴撳簲鐢ㄣ傚绋庡姟銆佸伐鍟嗙瓑锛屽鏋滄暟鎹簱鐨勮繛鎺ユ暟鐩緢澶氾紝瀵逛簬鏁版嵁搴撴湇鍔″櫒鏉ヨ锛屽涓涓繛鎺ュ氨瑕佸娑堣椾竴浠借祫婧愶紝濡傛灉澶ч噺鐢ㄦ埛杩炴帴杩涘叆鏁版嵁搴撶郴缁熶絾鍗翠笉杩涜浠讳綍鐨勬搷浣滐紝杩欐棤褰箣涓氨鐧界櫧閫犳垚浜嗘湇鍔″櫒绯荤粺璧勬簮鐨勬氮璐癸紝鍚屾椂閫犳垚鏈嶅姟鍣ㄨ礋杞界殑鎻愰珮锛屽浜庨偅浜涚‘瀹炲湪宸ヤ綔鐨勭敤鎴锋潵璁诧紝灏变笉鑳芥渶澶ч檺搴︾殑鍒╃敤鏈嶅姟鍣ㄧ殑璧勬簮锛屼弗閲嶆儏鍐典笅鍙兘閫犳垚绯荤粺鎬ц兘鐨勬ュ墽涓嬮檷銆
銆銆閽堝杩欑闂锛岃濡備綍澶勭悊鍛紵瀵逛簬鐩墠娴佽鐨勪笁灞傜粨鏋勶紙Browser/Application/Server锛夊紑鍙戞潵璁诧紝杩欎釜闂鍙互閫氳繃璁剧疆搴旂敤鏈嶅姟鍣ㄧ鐨勮繛鎺ュ叡浜睜锛坰hared pool锛夋潵閬垮厤銆備絾瀵逛簬浼犵粺鐨勪袱灞傜粨鏋勶紙Client/Server锛夊簲鐢紝灏卞繀椤荤敱鎴戜滑浜轰负骞查鏉ラ伩鍏嶈繖绉嶈祫婧愭氮璐规儏鍐点傚叿浣撳彲浠ラ氳繃涓涓悗鍙颁换鍔℃潵鐩戞帶绯荤粺涓殑鎵鏈夎繘绋嬶紝瀵逛簬閭ｄ簺绌洪棽瓒呰繃涓瀹氭椂闂寸殑杩涚▼閲囧彇涓瀹氱殑鐗规畩澶勭悊鎺柦锛屽鍦ㄥ鎴风鎻愮ず鐢ㄦ埛杩炴帴鏃堕棿澶暱锛屽鏋滄病鏈夊悗缁搷浣滅郴缁熷皢鑷姩鏉鎺夎杩炴帴鎴栬呯洿鎺ュ皢璇ョ┖闂茶繛鎺ユ潃鎺夈備笅闈㈠氨鏉ュ叿浣撹璁哄浣曞湪Oracle 8x鐜涓嬪疄鐜扮敤鎴疯繘绋嬬殑鑷姩鐩戞帶鍙婂叾瀵瑰瓒呰繃涓瀹氱┖闂叉椂闂磋繛鎺ョ殑澶勭悊鍔炴硶銆
銆銆涓銆佽瘑鍒郴缁熶腑瓒呰繃涓瀹氱┖闂叉椂闂寸殑杩炴帴
銆銆瑕佸疄鐜板悗鍙颁换鍔¤嚜鍔ㄥ瓒呰繃涓瀹氭椂闂寸┖闂茶繛鎺ョ殑澶勭悊锛岄鍏堢涓姝ュ伐浣滃氨鏄浠庢墍鏈変笌鏁版嵁搴撴湇鍔″櫒鐨勮繛鎺ヤ腑璇嗗埆鍑洪偅浜涜繛鎺ラ渶瑕佸鐞嗭紝涔熷氨鏄渶瑕佽幏寰椾笌鏈嶅姟鍣ㄨ繛鎺ョ殑姣忎釜鐢ㄦ埛鐨勭櫥闄嗘椂闂村強鍏舵渶鍚庝竴娆℃搷浣滃悗鐨勭┖闂叉椂闂淬傚湪Oracle绯荤粺涓紝鏈変竴涓姩鎬佹ц兘瑙嗗浘v$session锛岃瑙嗗浘淇濆瓨鐫绯荤粺褰撳墠杩炴帴鐨勫悇绉嶅姩鎬佷俊鎭傚叾涓紝鏈変袱涓瓧娈礚OGON_TIME鍜 LAST_CALL_ET鍙互寰楀埌涓婇潰鐨勪袱涓瓟妗堛
銆銆l LOGON_TIME鏄竴涓棩鏈熷瀷锛圖ate锛夊瓧娈碉紝涓虹敤鎴风櫥闄嗘椂闂达紱
銆銆l LAST_CALL_ET鏄竴涓暟瀛楀瀷锛圢umber锛夊瓧娈碉紝鍏跺惈涔夋槸鐢ㄦ埛鏈鍚庝竴鏉¤鍙ユ墽琛屽畬姣曞悗鐨勬椂闂达紝鍗曚綅涓虹銆傛瘡娆＄敤鎴锋墽琛屼竴涓柊鐨勮鍙ュ悗锛岃瀛楁澶嶄綅涓0锛岄噸鏂板紑濮嬭鏁般傛垜浠彲浠ラ氳繃璇ュ瓧娈垫潵鑾峰緱涓涓繛鎺ョ敤鎴锋渶鍚庝竴娆℃搷浣滄暟鎹簱鍚庣殑绌洪棽鏃堕棿銆
銆銆涓嬮潰鐨凷QL鏌ヨ璇彞鍙互寰楀埌涓庡綋鍓嶆暟鎹簱杩炴帴鐨勬墍鏈夌敤鎴风殑涓浜涘熀鏈儏鍐碉紝濡傜敤鎴峰悕銆佺姸鎬併佽繛鎺ユ満鍣ㄧ殑鍚嶇О锛屾搷浣滅郴缁熶腑鐢ㄦ埛鐨勫悕绉帮紝UNIX绯荤粺鐨勮繘绋嬪彿锛屽湪UNIX鎿嶄綔绯荤粺绾ф柇寮杩炴帴鐨勮鍙ワ紝Oracle鏁版嵁搴撶郴缁熸柇寮杩炴帴鐨勮鍙ワ紝鐧婚檰鏃堕棿浠ュ強鏈鍚庝竴娆℃搷浣滃埌鐜板湪鐨勭┖闂叉椂闂寸瓑绛夈
SELECT s.username 鐢ㄦ埛鍚嶇О, s.status 鐘舵,s.machine 鏈哄櫒鍚嶇О,
銆銆銆銆osuser 鎿嶄綔绯荤粺鐢ㄦ埛鍚嶇О,spid UNIX杩涚▼鍙,
'kill -9 '||spid UNIX绾ф柇寮杩炴帴,
'alter system kill session ' ||''''||s.sid||','||s.serial# || ''';' Oracle绾ф柇寮杩炴帴,
TO_CHAR (logon_time, 'dd/mm/yyyy hh24:mi:ss') 鐧婚檰鏃堕棿,
last_call_et 绌洪棽鏃堕棿绉,
TO_CHAR (TRUNC (last_call_et / 3600, 0))||' '||' HRS '||
TO_CHAR (
TRUNC ((last_call_et - TRUNC (last_call_et / 3600, 0) * 3600) / 60, 0)
) ||' MINS' 绌洪棽鏃堕棿灏忔椂鍒嗛挓,
module 妯″潡
FROM v$session s, v$process p
WHERE TYPE = 'USER'
AND p.addr = s.paddr
AND status != 'KILLED'
-- AND SUBSTR (machine, 1, 19) NOT IN ('鏈哄櫒鍚')
AND last_call_et > 60 * 60 * 1 -- 绌洪棽鏃堕棿瓒呰繃1灏忔椂鐨勮繛鎺
ORDER BY last_call_et desc;
銆銆鍦ㄤ笂闈㈢殑鏌ヨ涓紝鎴戜滑鍙互閫氳繃SUBSTR (machine, 1, 19) NOT IN ('鏈哄櫒鍚')杩欎釜鏉′欢鏉ュ睆钄戒竴浜涙満鍣紝杩欎簺鏈哄櫒鍙兘闇瑕佽繍琛屼竴浜涜楄垂寰堥暱鏃堕棿鐨凷QL璇彞鎴栧叾浠栦竴浜涚壒娈婃儏鍐电殑鏈哄櫒銆傚睆钄借繖浜涙満鍣ㄧ殑鍘熷洜灏辨槸鍦ㄥ悗闈㈢殑鍚庡彴鑷姩璇嗗埆鍙婂鐞嗕换鍔′腑瀵硅繖浜涙満鍣ㄤ笉浣滃鐞嗐
銆銆浜屻佽瘑鍒強鏂紑绌洪棽鐢ㄦ埛鐨勫瓨鍌ㄨ繃绋
銆銆涓婇潰鐨勬煡璇㈣鍙ュ彲浠ュ緱鍒扮郴缁熶腑鎵鏈夌殑杩炴帴鐢ㄦ埛鐨勪竴浜涘熀鏈儏鍐碉紝浣嗘槸鍙堝浣曟潵瀹炵幇绯荤粺鑷姩鍒ゆ柇绌洪棽瓒呰繃涓瀹氭椂闂寸殑杩炴帴骞跺皢鍏惰嚜鍔ㄦ柇寮鍛紵Oracle绯荤粺鎻愪緵浜嗕竴绉嶇О涔嬩负鍚庡彴浠诲姟锛圝ob锛夎嚜鍔ㄥ鐞嗙殑鏈哄埗銆傛垜浠彲浠ョ紪鍐欎竴涓悗鍙颁换鍔℃潵瀹氭椂鎵ц锛屼粠鑰屽垽鏂槸鍚﹀瓨鍦ㄨ繖鏍风殑鐢ㄦ埛杩炴帴锛屽鏋滃瓨鍦紝鍒欓氳繃鍚庡彴浠诲姟灏嗗叾鑷姩鏂紑銆
銆銆棣栧厛鍒涘缓涓涓瓨鍌ㄨ繃绋嬫潵瀹屾垚绌洪棽涓瀹氭椂闂寸敤鎴风殑璇嗗埆鍜屾柇寮宸ヤ綔锛岀劧鍚庢坊鍔犱竴涓悗鍙颁换鍔℃潵瀹氭椂锛堟牴鎹┖闂叉椂闂撮暱鐭潵纭畾锛夋墽琛岃杩囩▼锛屽嵆鍙疄鐜拌嚜鍔ㄦ柇寮绯荤粺涓┖闂茶秴杩囦竴瀹氭椂闂寸敤鎴风殑闇姹傘
銆銆瀛樺偍杩囩▼p_monitor瑙佷笅锛屽叾涓弬鏁癮n_nimutes涓虹敤鎴疯緭鍏ュ弬鏁帮紝鐢ㄦ潵纭畾璇嗗埆鍜屾柇寮澶氶暱绌洪棽鏃堕棿杩炴帴鐨勭敤鎴凤紝鍗曚綅涓哄垎閽燂紝榛樿涓60鍒嗛挓锛屼篃灏辨槸1灏忔椂銆傞渶瑕佹敞鎰忎竴鐐圭殑鏄紝璇ュ瓨鍌ㄨ繃绋嬶紝闇瑕佷互sys鐢ㄦ埛韬唤杩愯銆傜浉搴旓紝璋冪敤璇ュ瓨鍌ㄨ繃绋嬬殑鍚庡彴浠诲姟涔熼渶瑕佷互SYS韬唤鏉ユ坊鍔犮
CREATE OR REPLACE PROCEDURE P_MONITOR(
AN_MINUTES NUMBER DEFAULT 60)
/*******************************************
瀛樺偍杩囩▼鐢ㄩ旓細璇嗗埆鍑虹郴缁熶腑瓒呰繃涓瀹氱┖闂茶繛鎺ユ椂闂(
銆 AS_MINUTES)鐨勭敤鎴凤紝骞跺皢鍏秌ill鎺夊弬鏁:
銆 AN_MINUTES 绌洪棽鏃堕棿鏁帮紝鍗曚綅涓哄垎閽燂紝榛樿涓60鍒嗛挓
********************************************/
AS
銆v_Str VARCHAR2(100);
銆CURSOR C_users(v_minutes number) 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, 19) NOT IN ('闇瑕佸睆钄戒笉琚鐞嗙殑鏈哄櫒鍚')
銆AND last_call_et > v_minutes*60
銆ORDER BY last_call_et desc;
BEGIN
FOR T_users IN C_users(an_minutes) LOOP
v_Str := T_USERS.OPERATES;
EXECUTE IMMEDIATE v_str;
END LOOP;
END;
/
銆銆涓夈佸悗鍙颁换鍔＄殑瀹氭椂鎵ц
銆銆鏈鍚庯紝鎴戜滑涓虹郴缁熸坊鍔犱竴涓畾鏃朵换鍔★紝瀹氭椂璋冪敤涓婇潰鍒涘缓鐨勫瓨鍌ㄨ繃绋嬶紝鍗冲彲瀹屾垚绯荤粺鑷姩璇嗗埆鍜屽鐞嗙┖闂茬敤鎴风殑宸ヤ綔銆
銆銆涓嬮潰鏄竴涓疄闄呰皟鐢ㄧ殑渚嬪瓙锛屽湪sys鐢ㄦ埛涓嬶紝棣栧厛娣诲姞涓涓换鍔★紝璇ヤ换鍔℃瘡闅斿崐灏忔椂杩愯涓娆★紝姣忔鍧囪皟鐢≒_monitor瀛樺偍杩囩▼锛屾壘鍑虹郴缁熶腑绌洪棽鏃堕棿瓒呰繃1灏忔椂鐨勮繛鎺ワ紝鐒跺悗鑷姩鏂紑銆
DECLARE
jobno number;
BEGIN
DBMS_JOB.SUBMIT(
job => jobno,
what => 'p_monitor(60);',
next_date => SYSDATE,
interval => '/*1:Hr*/ sysdate + 30/1440); -- 姣忓崐灏忔椂杩愯涓娆
END;
/
