oracle数据库日常管理语句汇总

一、查看临时表空间使用情况Selectf.tablespace_name,sum(f.bytes_free+f.bytes_used)/1024/1024/1024totalGB,sum((f.bytes_free+f.bytes_used)-nvl(p.bytes_used,0))/1024/1024/1024FreeGB,sum(nvl(p.bytes_used,0))/1024/1024/1024UsedGBfromsys.v_$temp_space_headerf,dba_temp_filesd,sys.v_$temp_extent_poolpwheref.tablespace_name(+)=d.tablespace_nameandf.file_id(+)=d.file_idandp.file_id(+)=d.file_idgroupbyf.tablespace_name二、查看使用临时表空间的SQLSelectse.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))asSpace,tablespace,segtype,sql_textfromv$sort_usagesu,v$parameterp,v$sessionse,v$sqlswherep.name='db_block_size'andsu.session_addr=se.saddrands.hash_value=su.sqlhashands.address=su.sqladdrorderbyse.username,se.sid三、收缩临时表空间altertablespacetempshrinkspace;altertablespacetempshrinktempfile''四、重建索引alterindexPK_CROSSRELATIONrebuild;五、查看表空间使用情况SELECTUPPER(F.TABLESPACE_NAME)表空间名,D.TOT_GROOTTE_MB表空间大小(M),D.TOT_GROOTTE_MB-F.TOTAL_BYTES已使用空间(M),TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99')||'%'使用比,F.TOTAL_BYTES空闲空间(M),F.MAX_BYTES最大块(M)FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTESFROMSYS.DBA_FREE_SPACEGROUPBYTABLESPACE_NAME)F,(SELECTDD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MBFROMSYS.DBA_DATA_FILESDDGROUPBYDD.TABLESPACE_NAME)DWHERED.TABLESPACE_NAME=F.TABLESPACE_NAMEORDERBY1;六、查询表空间的总容量selecttablespace_name,sum(bytes)/1024/1024asMBfromdba_data_filesgroupbytablespace_name;七、查询表空间使用率selecttotal.tablespace_name,round(total.MB,2)asTotal_MB,考试大论坛round(total.MB-free.MB,2)asUsed_MB,round((1-free.MB/total.MB)*100,2)||'%'asUsed_Pctfrom(selecttablespace_name,sum(bytes)/1024/1024asMBfromdba_free_spacegroupbytablespace_name)free,(selecttablespace_name,sum(bytes)/1024/1024asMBfromdba_data_filesgroupbytablespace_name)totalwherefree.tablespace_name=total.tablespace_name;八、查找当前表级锁selectsess.sid,sess.serial#,lo.oracle_username,lo.os_user_name,ao.object_name,lo.locked_modefromv$locked_objectlo,dba_objectsao,v$sessionsesswhereao.object_id=lo.object_idandlo.session_id=sess.sid;杀掉锁表进程：altersystemkillsession'436,35123';九、监控当前数据库谁在运行什么SQL语句selectosuser,username,sql_textfromv$sessiona,v$sqltextbwherea.sql_address=b.addressorderbyaddress,piece;十、找使用CPU多的用户sessionselecta.sid,spid,status,substr(a.program,1,40)prog,a.terminal,osuser,value/60/100valuefromv$sessiona,v$processb,v$sesstatcwherec.statistic#=12andc.sid=a.sidanda.paddr=b.addrorderbyvaluedesc;十一、查看死锁信息SELECT(SELECTusernameFROMv$sessionWHERESID=a.SID)blocker,a.SID,'isblocking',(SELECTusernameFROMv$sessionWHERESID=b.SID)blockee,b.SIDFROMv$locka,v$lockbWHEREa.BLOCK=1ANDb.request0ANDa.id1=b.id1ANDa.id2=b.id2;十二、具有最高等待的对象SELECTo.OWNER,o.object_name,o.object_type,a.event,SUM(a.wait_time+a.time_waited)total_wait_timeFROMv$active_session_historya,dba_objectsoWHEREa.sample_timeBETWEENSYSDATE-30/2880ANDSYSDATEANDa.current_obj#=o.object_idGROUPBYo.OWNER,o.object_name,o.object_type,a.eventORDERBYtotal_wait_timeDESC;十三、查看具有最高等待的对象SELECTa.session_id,s.osuser,s.machine,s.program,o.owner,o.object_name,o.object_type,a.event,SUM(a.wait_time+a.time_waited)total_wait_timeFROMv$active_session_historya,dba_objectso,v$sessionsWHEREa.sample_timeBETWEENSYSDATE-30/2880ANDSYSDATEANDa.current_obj#=o.object_idANDa.session_id=s.SIDGROUPBYo.owner,o.object_name,o.object_type,a.event,a.session_id,s.program,s.machine,s.osuserORDERBYtotal_wait_timeDESC;十四、查看等待最多的SQLSELECTa.program,a.session_id,a.user_id,d.username,s.sql_text,SUM(a.wait_time+a.time_waited)total_wait_timeFROMv$active_session_historya,v$sqlareas,dba_usersdWHEREa.sample_timeBETWEENSYSDATE-30/2880ANDSYSDATEANDa.sql_id=s.sql_idANDa.user_id=d.user_idGROUPBYa.program,a.session_id,a.user_id,s.sql_text,d.username;十五、显示正在等待锁的所有会话SELECT*FROMDBA_WAITERS;十七、查数据库中正在执行的SQLSELECTSE.INST_ID,--实例SQ.SQL_TEXT,/*SQL文本*/SQ.SQL_FULLTEXT,/*SQL全部文本*/SE.SID,/*会话的唯一标识，通常要对某个会话进行分析前，首先就需要获得该会话的SID。*/--SE.SERIAL#,/*会话的序号*/SQ.OPTIMIZER_COSTASCOST_,/*COST值*/SE.LAST_CALL_ETCONTINUE_TIME,/*执行时间可能是单个sql也可能是整个功能*/SE.PREV_EXEC_START,/*SQLexecutionstartofthelastexecutedSQLstatement*/SE.EVENT,/*等待事件*/SE.LOCKWAIT,/*是否等待LOCK(SE，P)*/SE.MACHINE,/*客户端的机器名。(WORKGROUP\PC-201211082055)*/SQ.SQL_ID,/*SQL_ID*/SE.USERNAME,/*创建该会话的用户名*/SE.LOGON_TIME/*登陆时间*/--SE.TERMINAL,/*客户端运行的终端名。(PC-201211082055)*/--,SQ.HASH_VALUE,/*一个SQL产生的HASH值*/--SQ.PLAN_HASH_VALUE/*执行SQL的HASH值(解析后HASH值)，与SQL_ADDRESS关联查询其他SQL相关视图后即可查询会话当前正在执行的SQL语句*/FROMGV$SESSIONSE,/*会话信息。每一个连接到ORACLE数据库的会话都能在该视图中对应一条记录，根据该视图中的信息可以查询该会话使用的用户，正在执行或者刚刚执行的SQL语句*//*[GV$SQLAREA多节点]*/GV$SQLAREASQ/*跟踪所有SHAREDPOOL中的共享CURSOR信息，包括执行次数，逻辑读，物理读等*/WHERESE.SQL_HASH_VALUE=SQ.HASH_VALUEANDSE.STATUS='ACTIVE'ANDSE.SQL_ID=SQ.SQL_IDANDSQ.INST_ID=SE.INST_IDANDSE.USERNAMEisnotnull;--过滤条件--ANDSE.USERNAME='FWSB'--用户名--ANDSQ.COMMAND_TYPEIN(2,3,5,6,189)--ANDSE.SID!=USERENV('SID')/*rac集群环境误用*/--ANDMACHINE!='WORKGROUP\MHQ-PC';十八、每天执行慢的SQL十九、查看非绑定变量的SQLSELECTS.SQL_TEXT,S.SQL_FULLTEXT,S.SQL_ID,ROUND(ELAPSED_TIME/1000000/(CASEWHEN(EXECUTIONS=0ORNVL(EXECUTIONS,1)=1)THEN1ELSEEXECUTIONSEND