Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the script for dealing with ORACLE problems". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the script for dealing with ORACLE problems".

After checking the operating system load to log on to the database server, the first thing is to confirm whether the CPU, memory, and Imax O are abnormal through the system command. The command of each system is different. Common are top, topas, vmstat, iostat View wait events-wait_eventcol event for a45SELECT inst_id,EVENT, SUM (DECODE (WAIT_TIME, 0,0,1)) "Prev", SUM (DECODE (WAIT_TIME, 0,1,0)) "Curr", COUNT (*) "Tot", sum (SECONDS_IN_WAIT) SECONDS_IN_WAITFROM GV$SESSION_WAITWHERE event NOT IN ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client') 'gcs remote message') AND event NOT LIKE'% idle%' AND event NOT LIKE'% Idle%' AND event NOT LIKE'% Streams AQ%'GROUP BY inst_id,EVENTORDER BY 1pje 5 desc Check the session based on the waiting event-- session_by_eventSELECT / * + rule * / sid, s.serialconversation, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj,s.username, s.machine, BLOCKING_INSTANCE | |'. | | blocking_session b_sess FROM v$session s, v$process p WHERE event='&event_name' AND s.paddr = p.addr order by 6 Query the details of a session-- session_by_sidSELECT s.sid, s.serializations, spid, event, sql_id, PREV_SQL_ID, seconds_in_wait ws, row_wait_obj# obj,s.username, s.machine, module,blocking_session banners essentials logonball time FROM v$session s, v$process p WHERE sid ='& sid' AND s.paddr = p.addr Query object information-- obj_infocol OBJECT_NAME for a30select owner,object_name,subobject_name,object_type from dba_objects where object_id=&oid; query SQL statement information-- sql_textselect sql_id,SQL_fullTEXT from v$sqlarea-- DBA_HIST_SQLTEXTwhere (sql_id='&sqlid' or hash_value=to_number ('& hashvale')) and rownum100 and type in ('TX','TM') order by 3Ling 9 -- query the lock select / * + rule*/s.sid,p.spid,l.type,round (max (l.ctime) / 60Power0) in the database, lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name from v$session s, v$process pdcmccccccdccccccccccccccccccccccccccccccccccccccccccccccdcccccccccccccccccdcccccccccccdcccccccccccccccccccccccccccccccccccccdccdhccccccccccccccccccccccccccccdLoction select / * + rule*/s.sid,p.spid,l.type,round (max (l.ctime) Dba_objects b where o.SESSION_ID=s.sid and s.sid=l.sid and o.OBJECT_ID=b.OBJECT_ID and s.paddr = p.addr and l.ctime > 100and l.type in ('TX','TM','FB') group by s.sidreagep.spidrect l.typewrits.sqlSecretidreages.USERNAMEJournal b.ownerdirection b.objectpapertypeauthorb.objectaccountypename order by 9dy1co.3 Keep the on-the-spot evidence-- systemstate dumpsqlplus-prelim / as sysdbaoradebug setmypidoradebug unlimit;oradebug dump systemstate 266: Murray wait for 1 minoradebug dump systemstate 266; UV wait for 1 minoradebug dump systemstate 266; oradebug tracefile_name;--hanganalyzeoradebug setmypidoradebug unlimit Oradebug dump hanganalyze 3--wait for 1 minoradebug dump hanganalyze 3--wait for 1 minoradebug dump hanganalyze 3oradebug tracefile_name kill session-- kill_sessset line 199col event format A35 event-kill a SID session SELECT / * + rule * / sid, s.serialconversation, 'kill-9' | | spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sid='&sid' AND s.paddr = p.addr order by 1 -- according to SQL_ID kill session SELECT / * + rule * / sid, s.serialconversation, 'kill-9' | | spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sql_id='&sql_id' AND s.paddr = p.addr order by 1 -- Kill session SELECT / * + rule * / sid, s.serialkeeper, 'kill-9' according to waiting event | | spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE event='&event' AND s.paddr = p.addr order by 1 'p.addr order by'-based on user kill session SELECT / * + rule * / sid, s.serialconversation, 'kill-9' | | spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE username='&username' AND s.paddr = p.addr order by 1 -- kill all LOCAL=NO processes ps-ef | grep LOCAL=NO | grep $ORACLE_SID | grep-v grep | awk'{print $2}'| xargs kill-9 Thank you for reading. The above is the content of "what is the script for dealing with ORACLE problems". After the study of this article, I believe you have a deeper understanding of what the script for dealing with ORACLE problems is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.