Oracle數(shù)據(jù)庫日常檢查文檔

1、oracle數(shù)據(jù)庫日常檢查文檔內(nèi)容數(shù)據(jù)庫狀態(tài)檢査檢查方法select status, database status from v$instanee;檢查結(jié)果正常異常備注一返回結(jié)果是:open active屬正常內(nèi)容表空間使用情況檢查方法select d. tablespace_name, file_name file_name，space sum_space(m)，space-nvl(free_space,0) used_space(m)， round(1-nvl(free_space,0)/space)*100,2)used_rate (%)，aut0extens1blefrom (sel

2、ectfile_id, file_name, tablespace_name, round(sum(bytes)/(1024*1 024),2) space,sum (blocks) blocksfrom dba_data_filesgroup by tablespace_name, f1le_1d, f1le_name) d,(selectfile_id, tablespace_name, round(sum(bytes)/(1024*1024), 2)free_spacefrom dba_free_spacegroup by tablespace_name, f1le_1d) e,(sel

3、ect file_id, autoextensible from dbadata_files) fwhere d. tablespace_name 二 e. tablespace_name (+) and d. file_id 二 e. ftle_td(+) and d. ftle_td 二 f. file_id(+)union all -if have tempfileselect d. tablespace name,file name "file name",space sum_space (m)，used_space used_space (m)，round(nvl

4、(used_space, 0)/space*100, 2)used_rate (%)，z, autoextensiblefrom(selectftle_td, ftle_name, tablespace_name, round(sum(bytes)/(1024*1024),2) space,sum (blocks) blocksfrom dba_temp_filesgroup by tablespace name, file id,file name) d,(selectfile_id, tablespace_name, round(sum(bytes_used) /(1024*1024),2

5、) used_space,round(sum(bytes_free)/(1024*1024), 2) free_spacefrom v$temp space headergroup by tablespace_name, f1le_1d) e,(select file_id, autoextensible from dba_temp_files) fwhere d. tablespace_name 二 e. tablespace.name (+) and d. ftle_td=e. file_id (+) and d. file_id = f. file_id (+)order by tabl

6、espace name, filename;檢查結(jié)果正常異常備注如果發(fā)現(xiàn)用戶表空間使用超過85%,馬上報告內(nèi)容alert sid. log 文件檢查方法檢査 alert sid. log 有無報錯信息(ora-600、ora-1578)$orac1e homeadmi norc1bdumpa1ertore1.log檢查結(jié)果正常異常備注如果無法登陸數(shù)據(jù)庫所在服務(wù)器，這步可忽略內(nèi)容備份文件是否正常檢查方法檢查文件大小及生成fi期 檢查export的日志文件檢查結(jié)果正常異常備注我們在本地數(shù)據(jù)庫每天都做一些dmp備份，注意檢查備份的自動執(zhí) 行情況。內(nèi)容鎖與等待檢查方法select /*+ rule *

7、/ ipadc',decoded, xidusn , 0, 3, 0)1. oracle usernameuser name,o. owner, o. object_name, o. object_type, s. sid, s. serial# from v$locked_objectl, dba_objectso, v$session swhere 1. object_id=o. object_id and 1. session_id二s. sid order by o.object id, xidusn desc;檢查結(jié)杲止常異常備注發(fā)現(xiàn)并報告鎖與等待的對象內(nèi)容數(shù)據(jù)庫是否有失效的

8、約束檢查方法select owner, constraint_name, constraint_type, table_namefrom dba constraintswhere owner not in ('sys'system')and status二'disabled，;檢查結(jié)果正常異常備注內(nèi)容數(shù)據(jù)庫是否有失效的觸發(fā)器檢查方法select owner, tr1gger_name, tr1gger_type from dba_tr1ggerswhere owner not in ('sys'system')and status二

9、9;disabled'檢查結(jié)果正常異常備注內(nèi)容檢查是否有失效的索引檢查方法select index_name,owner,table_name,tablespace_namefrom dba_indcxcswhere owner not in (' sys，,' system')and status != 'valid'檢查結(jié)果正常異常備注內(nèi)容檢查是否有無效的對象檢杳方法select objcct name，object_type,own er,statusfrom dba_objectswhere status !='valid'and owner not in (' sys， system')and object_type inc trtgger，, 'view',' procedure',' functtov );檢查結(jié)果正常異常備注如杲存在無效的對象，手工重新編譯一下。內(nèi)容檢