ORACLE数据库日常维护手册(最全+最实用)

BYFWYANG1ORACLE日常维护手册查看数据库版本SELECT*FROMV$VERSION;查看数据库语言环境SELECTUSERENV('LANGUAGE')FROMDUAL;查看ORACLE实例状态SELECTINSTANCE_NAME,HOST_NAME,STARTUP_TIME,STATUS,DATABASE_STATUSFROMV$INSTANCE;查看ORACLE监听状态lsnrctlstatus查看数据库归档模式SELECTNAME,LOG_MODE,OPEN_MODEFROMV$DATABASE;查看回收站中对象SELECTOBJECT_NAME,ORIGINAL_NAME,TYPEFROMRECYCLEBIN;清空回收站中对象PURGERECYCLEBIN;还原回收站中的对象FLASHBACKTABLEBIN$GOZUQZ6GS222JZDCCTFLHQ==$0TOBEFOREDROPRENAMETOTEST;BYFWYANG2闪回误删除的表FLASHBACKTABLEAAATOBEFOREDROP;闪回表中记录到某一时间点ALTERTABLETESTENABLEROWMOVEMENT;FLASHBACKTABLETESTTOTIMESTAMPTO_TIMESTAMP('2009-10-1521:17:47','YYYY-MM-DDHH24:MI:SS');查看当前会话SELECTSID,SERIAL#,USERNAME,PROGRAM,MACHINE,STATUSFROMV$SESSION;查看DDL锁SELECT*FROMDBA_DDL_LOCKSWHEREOWNER='FWYANG';检查等待事件SELECTSID,A.USERNAME,EVENT,WAIT_CLASS,T1.SQL_TEXTFROMV$SESSIONA,V$SQLAREAT1WHEREWAIT_CLASS'Idle'ANDA.SQL_ID=T1.SQL_ID;检查数据文件状态SELECTFILE_NAME,STATUSFROMDBA_DATA_FILES;检查表空间使用情况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),BYFWYANG3F.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_NAMEORDERBY4DESC;收缩表空间ALTERTABLESPCETS_AJ_DATACOALESCE;增加表空间大小SELECTT.TABLESPACE_NAME,T.FILE_NAME,T.BYTES/1024/1024/1024FROMDBA_DATA_FILESTWHERET.TABLESPACE_NAME='TS_AJ_DATA';ALTERTABLESPACETS_AJ_DATAADDDATAFILE'/DATA/TS_AJ_DATA05_10G.DBF'SIZE10000MAUTOEXTENDOFF;检查不起作用的约束SELECTOWNER,CONSTRAINT_NAME,TABLE_NAME,CONSTRAINT_TYPE,STATUSFROMDBA_CONSTRAINTSWHERESTATUS='DISABLE';检查发生坏块的数据库对象SELECTTABLESPACE_NAME,SEGMENT_TYPE,OWNER,SEGMENT_NAMEFROMDBA_EXTENTSWHEREFILE_ID=AFNANDBLOCKBETWEENBLOCK_IDANDBLOCK_ID+BLOCKS-1;BYFWYANG4检查无效的数据库对象SELECTOWNER,OBJECT_NAME,OBJECT_TYPEFROMDBA_OBJECTSWHERESTATUS='INVALID';查看语句执行进度SELECTSE.SID,OPNAME,TRUNC(SOFAR/TOTALWORK*100,2)||'%'ASPCT_WORK,ELAPSED_SECONDSELAPSED,ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR)REMAIN_TIME,SQL_TEXTFROMV$SESSION_LONGOPSSL,V$SQLAREASA,V$SESSIONSEWHERESL.SQL_HASH_VALUE=SA.HASH_VALUEANDSL.SID=SE.SIDANDSOFAR!=TOTALWORKORDERBYSTART_TIME;检查碎片程度高的表SELECTSEGMENT_NAMETABLE_NAME,COUNT(*)EXTENTSFROMDBA_SEGMENTSWHEREOWNERNOTIN('SYS','SYSTEM')GROUPBYSEGMENT_NAMEHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMDBA_SEGMENTSGROUPBYSEGMENT_NAME);检查表空间的I/O比例SELECTDF.TABLESPACE_NAMENAME,DF.FILE_NAMEFILE,F.PHYRDSPYR,F.PHYBLKRDPBR,F.PHYWRTSPYW,F.PHYBLKWRTPBWFROMV$FILESTATF,DBA_DATA_FILESDFWHEREF.FILE#=DF.FILE_IDORDERBYDF.TABLESPACE_NAME;BYFWYANG5检查碎片程度高的表SELECTsegment_nametable_name,COUNT(*)extentsFROMdba_segmentsWHEREownerNOTIN('SYS','SYSTEM')GROUPBYsegment_nameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMdba_segmentsGROUPBYsegment_name);检查文件系统的I/O比例SELECTSUBSTR(A.FILE#,1,2)#,SUBSTR(A.NAME,1,30)NAME,A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTSFROMV$DATAFILEA,V$FILESTATBWHEREA.FILE#=B.FILE#;检查消耗CPU最高的进程SELECTP.PIDPID,S.SIDSID,P.SPIDSPID,S.USERNAMEUSERNAME,S.OSUSEROSNAME,P.SERIAL#S_#,P.TERMINAL,P.PROGRAMPROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT,1,80))SQLFROMFROMV$PROCESSP,V$SESSIONS,V$SQLAREAAWHEREP.ADDR=S.PADDRANDS.SQL_ADDRESS=A.ADDRESS(+)ANDP.SERIAL#'1';检查运行很久的SQLSELECTUSERNAME,BYFWYANG6SID,OPNAME,ROUND(SOFAR*100/TOTALWORK,0)||'%'ASPROGRESS,TIME_REMAINING,SQL_TEXTFROMV$SESSION_LONGOPS,V$SQLWHERETIME_REMAINING0ANDSQL_ADDRESS=ADDRESSANDSQL_HASH_VALUE=HASH_VALUE;等待时间最多的5个系统等待事件的获取SELECT*FROM(SELECT*FROMV$SYSTEM_EVENTWHEREEVENTNOTLIKE'SQL%'ORDERBYTOTAL_WAITSDESC)WHEREROWNUM=5;查找前十条性能差的SQLSELECT*FROM(SELECTPARSING_USER_IDEXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXTFROMV$SQLAREAORDERBYDISK_READSDESC)WHEREROWNUM10;检查死锁SELECTbs.usernameBlockingUser,bs.usernameDBUser,ws.usernameWaitingUser,bs.SIDSID,ws.SIDWSID,bs.serial#Serial#,bs.sql_addressaddress,bs.sql_hash_valueSqlhash,bs.programBlockingApp,ws.programWaitingApp,bs.machineBlockingMachine,ws.machineWaitingMachine,bs.osuserBlockingOSUser,ws.osuserWaitingOSUser,bs.serial#Serial#,ws.serial#WSerial#,DECODE(wk.TYPE,BYFWYANG7'MR','MediaRecovery','RT','RedoThread','UN','USERName','TX','Transaction','TM','DML','UL','PL/SQLUSERLOCK','DX','DistributedXaction','CF','ControlFILE','IS','InstanceState','FS','FILESET','IR','InstanceRecovery','ST','DiskSPACETransaction','TS','TempSegment','IV','LibraryCacheInvalidation','LS','LOGSTARTORSwitch','RW','ROWWait','SQ','SequenceNumber','TE','ExtendTABLE','TT','TempTABLE',wk.TYPE)lock_type,DECODE(hk.lmode,0,'None',1,'NULL',2,'ROW-S(SS)',3,'ROW-X(SX)',4,'SHARE',5,'S/ROW-X(SSX)',6,'EXCLUSIVE',TO_CHAR(hk.lmode))mode_held,DECODE(wk.request,0,'None',1,'NULL',2,'ROW-S(SS)',3,'ROW-X(SX)',4,'SHARE',5,'S/ROW-X(SSX)',6,'EXCLUSIVE',TO_CHAR(wk.request))mode_requested,TO_CHAR(hk.id1)lock_id1,TO_CHAR(hk.id2)lock_id2,DECODE(hk.BLOCK,BYFWYANG80,'NOTBlocking',/**//*Notblockinganyotherproces