这篇文章主要讲解了“ORACLE问题处理的脚本是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“ORACLE问题处理的脚本是什么”吧！

登上数据库服务器后，第一个就是通过系统命令确认下CPU、内存、I/O是否异常，每个系统的命令不一样，常见的有top、topas、vmstat、iostat查看等待事件

--wait_eventcoleventfora45SELECTinst_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_WAITFROMGV$SESSION_WAITWHEREeventNOTIN('smontimer','pmontimer','rdbmsipcmessage','SQL*Netmessagefromclient','gcsremotemessage')ANDeventNOTLIKE'%idle%'ANDeventNOTLIKE'%Idle%'ANDeventNOTLIKE'%StreamsAQ%'GROUPBYinst_id,EVENTORDERBY1,5desc;根据等待事件查会话

--session_by_eventSELECT/*+rule*/sid,s.serial#,spid,event,sql_id,seconds_in_waitws,row_wait_obj#obj,s.username,s.machine,BLOCKING_INSTANCE||'.'||blocking_sessionb_sessFROMv$sessions,v$processpWHEREevent='&event_name'ANDs.paddr=p.addrorderby6;查询某个会话详情

--session_by_sidSELECTs.sid,s.serial#,spid,event,sql_id,PREV_SQL_ID,seconds_in_waitws,row_wait_obj#obj,s.username,s.machine,module,blocking_sessionb_sess,logon_timeFROMv$sessions,v$processpWHEREsid='&sid'ANDs.paddr=p.addr;查询对象信息

--obj_infocolOBJECT_NAMEfora30selectowner,object_name,subobject_name,object_typefromdba_objectswhereobject_id=&oid;查询SQL语句信息

--sql_textselectsql_id,SQL_fullTEXTfromv$sqlarea--DBA_HIST_SQLTEXTwhere(sql_id='&sqlid'orhash_value=to_number('&hashvale'))andrownum<2;查询会话阻塞情况

--blocking_sessselectcount(*),blocking_sessionfromv$sessionwhereblocking_sessionisnotnullgroupbyblocking_session;查询数据库的锁

--locksetlinesize180colusernamefora15colownerfora15colOBJECT_NAMEfora30colSPIDfora10--查询某个会话的锁select/*+rule*/SESSION_ID,OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODEfromgv$locked_objectwheresession_id=&sid;--查询TM、TX锁select/*+rule*/*fromv$lockwherectime>100andtypein('TX','TM')orderby3,9;--查询数据库中的锁select/*+rule*/s.sid,p.spid,l.type,round(max(l.ctime)/60,0)lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_namefromv$sessions,v$processp,v$lockl,v$locked_objecto,dba_objectsbwhereo.SESSION_ID=s.sidands.sid=l.sidando.OBJECT_ID=b.OBJECT_IDands.paddr=p.addrandl.ctime>100andl.typein('TX','TM','FB')groupbys.sid,p.spid,l.type,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_nameorderby9,1,3;保留现场证据

--systemstatedumpsqlplus-prelim/assysdbaoradebugsetmypidoradebugunlimit;oradebugdumpsystemstate266;--waitfor1minoradebugdumpsystemstate266;--waitfor1minoradebugdumpsystemstate266;oradebugtracefile_name;--hanganalyzeoradebugsetmypidoradebugunlimit;oradebugdumphanganalyze3--waitfor1minoradebugdumphanganalyze3--waitfor1minoradebugdumphanganalyze3oradebugtracefile_name杀会话

--kill_sesssetline199coleventformata35--杀某个SID会话SELECT/*+rule*/sid,s.serial#,'kill-9'||spid,event,blocking_sessionb_sessFROMv$sessions,v$processpWHEREsid='&sid'ANDs.paddr=p.addrorderby1;--根据SQL_ID杀会话SELECT/*+rule*/sid,s.serial#,'kill-9'||spid,event,blocking_sessionb_sessFROMv$sessions,v$processpWHEREsql_id='&sql_id'ANDs.paddr=p.addrorderby1;--根据等待事件杀会话SELECT/*+rule*/sid,s.serial#,'kill-9'||spid,event,blocking_sessionb_sessFROMv$sessions,v$processpWHEREevent='&event'ANDs.paddr=p.addrorderby1;--根据用户杀会话SELECT/*+rule*/sid,s.serial#,'kill-9'||spid,event,blocking_sessionb_sessFROMv$sessions,v$processpWHEREusername='&username'ANDs.paddr=p.addrorderby1;--kill所有LOCAL=NO进程ps-ef|grepLOCAL=NO|grep$ORACLE_SID|grep-vgrep|awk'{print$2}'|xargskill-9

感谢各位的阅读，以上就是“ORACLE问题处理的脚本是什么”的内容了，经过本文的学习后，相信大家对ORACLE问题处理的脚本是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！