OracleDBA數(shù)據(jù)庫(kù)日常維護(hù)手冊(cè)常用SQL腳本

1、Oracle 數(shù)據(jù)庫(kù)日常維護(hù)【版本整理日期： 2011/02/26 】版本整理人： 1634068400QQ.COM本文檔包含以下內(nèi)容：1. Oracle 數(shù)據(jù)庫(kù)日常維護(hù)2. Oracle DBA常用管理腳本3. Oracle DB常用 SQL語(yǔ)句/*HTTP:/ERP100.TAOBAO.COM(若跳轉(zhuǎn)不成功，請(qǐng)復(fù)制到瀏覽器或聯(lián)系Q)Metalink Sharing*/在 Oracle 數(shù)據(jù)庫(kù)運(yùn)行期間， DBA 應(yīng)該對(duì)數(shù)據(jù)庫(kù)的運(yùn)行日志及表空間的使用情況進(jìn)行監(jiān)控，及早發(fā)現(xiàn)數(shù)據(jù)庫(kù)中存在的問題。一、Oracle 警告日志文件監(jiān)控Oracle 在運(yùn)行過程中，會(huì)在警告日志文件 (alert_SID.l

2、og)中記錄數(shù)據(jù)庫(kù)的一些運(yùn)行情況：l數(shù)據(jù)庫(kù)的啟動(dòng)、關(guān)閉，啟動(dòng)時(shí)的非缺省參數(shù)；l數(shù)據(jù)庫(kù)的重做日志切換情況，記錄每次切換的時(shí)間，及如果因?yàn)闄z查點(diǎn)(checkpoint)操作沒有執(zhí)行完成造成不能切換，會(huì)記錄不能切換的原因； l對(duì)數(shù)據(jù)庫(kù)進(jìn)行的某些操作，如創(chuàng)建或刪除表空間、增加數(shù)據(jù)文件；l數(shù)據(jù)庫(kù)發(fā)生的錯(cuò)誤，如表空間不夠、出現(xiàn)壞塊、數(shù)據(jù)庫(kù)內(nèi)部錯(cuò)誤(ORA 600)DBA 應(yīng)該定期檢查日志文件， 根據(jù)日志中發(fā)現(xiàn)的問題及時(shí)進(jìn)行 處理問題處理啟動(dòng)參數(shù)不對(duì)檢查初始化參數(shù)文件因?yàn)闄z查點(diǎn)操作或 歸檔操作沒有完成造成重做日志不能切換如果經(jīng)常發(fā)生這樣的情況， 可以考慮 增加重做日志文件 組；想辦法提高 檢查點(diǎn)或歸檔操作的

3、效率；有人未經(jīng)授權(quán) 刪除了表空間檢查數(shù)據(jù)庫(kù)的 安全問題，是否密碼太簡(jiǎn)單；如有必要，撤消某些用戶的系統(tǒng)權(quán)限出現(xiàn)壞塊檢查是否是硬 件問題 (如磁盤本 生有壞塊)，如果不 是，檢查是那個(gè) 數(shù)據(jù)庫(kù)對(duì) 象出現(xiàn)了壞塊，對(duì) 這個(gè)對(duì)象進(jìn)行重建表空間不夠增加數(shù)據(jù)文件 到相應(yīng)的表空間出現(xiàn) ORA-600根據(jù)日志文件的內(nèi) 容查看相應(yīng)的 TRC文件，如果 是 Oracle 的 bug，要及時(shí)打上相應(yīng)的補(bǔ)丁二、數(shù)據(jù)庫(kù)表空間使用情況監(jiān)控 （字典管理 表空間）數(shù)據(jù)庫(kù)運(yùn)行 了一段時(shí)間后，由于不斷的在表空間 上創(chuàng)建和刪除對(duì)象，會(huì)在表空間上產(chǎn)生大量的碎片，DBA 應(yīng)該及時(shí) 了解表空間的 碎片和可 用空間情況， 以決定是否要 對(duì)碎

4、片進(jìn)行整理或?yàn)楸砜臻g增加數(shù)據(jù)文件。select tablespace_name,count(*) chunks , max(bytes/1024/1024) max_chunkfrom dba_free_spacegroup by tablespace_name;上面的 SQL 列出了數(shù)據(jù)庫(kù)中每 個(gè)表空間的空 閑塊情況,如下所示： TABLESPACE_NAMECHUNKSMAX_CHUNKINDX157.9921875RBS3490.992188RMAN_TS116.515625SYSTEM1207.296875TEMP2070.8046875TOOLS111.8359375USERS677

5、1.3671875其中，CHUNKS列表示表空間中有 多少可用的空閑塊( 每個(gè)空閑塊是由一些連續(xù)的 Oracle 數(shù)據(jù)塊組成) ，如果這樣的空閑塊過多，比如平均到每個(gè)數(shù)據(jù)文件上超過了 100 個(gè)，那么該表空間的 碎片狀況就比較嚴(yán)重了，可以嘗試用以下的 SQL 命令進(jìn)行表空間 相鄰碎片的接合：alter tablespace表空間名 cascade;此處是有誤吧， coalesce;然后再執(zhí)行查看表空間碎片的 SQL 語(yǔ)句，看表空間的 碎片有沒有減少。如果沒有效果，并且表空間的 碎片已經(jīng)嚴(yán)重影響到了數(shù)據(jù)庫(kù)的運(yùn)行， 則考慮對(duì)該表空間進(jìn)行重建。MAX_CHUNK 列的結(jié)果是表空間上最大的可用塊大小，

6、如果該表空間 上的 對(duì)象 所需 分配 的空 間 (NEXT 值 ) 大于可 用塊的大 小的 話， 就會(huì) 提示ORA-1652、ORA-1653、ORA-1654 的錯(cuò)誤信息，DBA 應(yīng)該及時(shí)對(duì)表空間的空間進(jìn)行擴(kuò)充，以避免這些錯(cuò)誤發(fā)生。對(duì)表空間的 擴(kuò)充對(duì)表空間的數(shù)據(jù)文件 大小進(jìn)行擴(kuò)展，或向表空間增加數(shù)據(jù)文件， 具體操作見“存儲(chǔ)管理”部份。三、查看數(shù)據(jù)庫(kù)的 連接情況DBA 要定時(shí)對(duì)數(shù)據(jù)庫(kù)的 連接情況進(jìn)行檢查， 看與數(shù)據(jù)庫(kù)建 立的會(huì)話數(shù)目是不是正常，如果建立了過多的連接，會(huì)消耗數(shù)據(jù)庫(kù)的 資源。同時(shí)，對(duì)一些 “掛死” 的連接，可能會(huì)需要 DBA 手工進(jìn)行清理。以下的 SQL 語(yǔ)句列出當(dāng)前數(shù)據(jù)庫(kù)建 立的

7、會(huì)話情況：select sid,serial#,username,program,machine,status from v$session;輸出結(jié)果為：SID SERIAL#USERNAMEPROGRAMMACHINESTATUS11ORACLE.EXEWORK3ACTIVE21ORACLE.EXEWORK3ACTIVE31ORACLE.EXEWORK3ACTIVE41ORACLE.EXEWORK3ACTIVE53ORACLE.EXEWORK3ACTIVE61ORACLE.EXEWORK3ACTIVE71ORACLE.EXEWORK3ACTIVE827 SYSSQLPLUS.EXE WORK

8、GROUPWORK3 ACTIVE115 DBSNMPdbsnmp.exeWORKGROUPWORK3 INACTIVE其中，SID會(huì)話(session)的 ID 號(hào)；SERIAL#會(huì)話的序列號(hào)，和 SID 一起用來唯一標(biāo)識(shí)一個(gè)會(huì)話；USERNAME建立該會(huì)話的用戶名；PROGRAM這個(gè)會(huì)話是用什么工具 連接到數(shù)據(jù)庫(kù)的；STATUS當(dāng)前這個(gè)會(huì)話的狀態(tài)， ACTIVE表示會(huì)話正在執(zhí)行某些任務(wù)，INACTIVE表示當(dāng)前會(huì)話沒有執(zhí)行 任何操作；如果 DBA 要手工斷開某個(gè)會(huì)話，則執(zhí)行：alter system kill session 'SID,SERIAL#'注意， 上例中 SID

9、 為 1 到 7(USERNAME 列為空) 的會(huì)話， 是 Oracle的后臺(tái)進(jìn)程，不 要對(duì)這些會(huì)話進(jìn)行任何操作。四、控制文件的備份在數(shù)據(jù)庫(kù) 結(jié)構(gòu)發(fā)生變化時(shí)，如增加 了表空間，增加 了數(shù)據(jù)文件或重做日志文件這些操作， 都會(huì)造成 Oracle 數(shù)據(jù)庫(kù)控 制文件的變化， DBA 應(yīng)及進(jìn)行控 制文件的備份，備份方法是：執(zhí)行 SQL 語(yǔ)句：alter databasebackup controlfile to '/home/backup/control.bak'或：alter databasebackup controlfile to trace;這樣，會(huì)在 USER_DUMP_DES

10、T( 初始化參數(shù)文件中 指定)目錄下生成創(chuàng)建控制文件的 SQL 命令。五、檢查數(shù)據(jù)庫(kù)文件的 狀態(tài)DBA 要及時(shí)查看數(shù)據(jù)庫(kù)中數(shù)據(jù)文件的 狀態(tài)（ 如被誤刪除 ），根據(jù)實(shí)際情況決定如何進(jìn)行處理，檢查數(shù)據(jù)文件的 狀態(tài)的 SQL 如下：select file_name,status from dba_data_files;如果數(shù)據(jù)文件的 STATUS列不是AVAILABLE ，那么就要采取相應(yīng)的措施， 如對(duì)該數(shù)據(jù)文件進(jìn)行 恢復(fù)操作，或重建該數(shù)據(jù)文件 所在的表空間。六、檢查數(shù)據(jù)庫(kù) 定時(shí)作業(yè)的完成情況如果數(shù)據(jù)庫(kù)使用 了 Oracle 的 JOB 來完成一些 定時(shí)作業(yè)，要對(duì)這些 JOB 的運(yùn)行情況進(jìn)行檢查：s

11、elect job,log_user,last_date,failuresfrom dba_jobs;如果 FAILURES 列是一個(gè)大于 0 的數(shù)的話， 說明 JOB 運(yùn)行失敗， 要進(jìn)一步的檢查。七、數(shù)據(jù)庫(kù)壞塊的 處理當(dāng) Oracle 數(shù)據(jù)庫(kù)出現(xiàn)壞塊時(shí)， Oracle會(huì)在警告日志文件 （alert_SID.log） 中記錄壞塊的 信息：ORA-01578: ORACLEdata block corrupted(file# 7, block # <BLOCK)>ORA-01110: data file <AFN> : '/oracle1/oradata/V920

12、/oradata/V816/users01.dbf'其中， AFN代表壞塊所在數(shù)據(jù)文件的 絕對(duì)文件號(hào)， <BLOCK> 代表壞塊是數(shù)據(jù)文件 上的第幾個(gè)數(shù)據(jù)塊出現(xiàn) 這種情況時(shí)，應(yīng)該 首先 檢查是否是硬 件及操作 系統(tǒng)上 的故障導(dǎo)致Oracle 數(shù)據(jù)庫(kù)出現(xiàn)壞塊。在 排除了數(shù)據(jù)庫(kù)以外的原因后，再對(duì)發(fā)生壞塊的數(shù)據(jù)庫(kù)對(duì)象進(jìn)行處理。1. 確定發(fā)生壞塊的數(shù)據(jù)庫(kù)對(duì) 象SELECT tablespace_name,segment_type, owner, segment_nameFROM dba_extentsWHERE file_id =<AFN>AND <BLOCK&g

13、t; betweenblock_idAND block_id+blocks-1; 2決定修復(fù)方法如果發(fā)生壞塊的對(duì) 象是一個(gè)索引，那么可以直接把索引 DROP 掉后，再根據(jù)表里的記錄進(jìn)行重建；如果發(fā)生壞塊的表的記錄 可以根據(jù)其它表的記錄生成的 話，那么可以直接把這個(gè)表 DROP 掉后重建；如果有數(shù)據(jù)庫(kù)的 備份，則恢復(fù)數(shù)據(jù)庫(kù)的 方法來進(jìn)行修復(fù)；如果表里的記錄沒有 其它辦法恢復(fù)，那么壞塊上的記錄就丟失了， 只能把表中其它數(shù)據(jù)塊上的記錄取出來，然后對(duì)這個(gè)表進(jìn)行重建。 3用 Oracle 提供的 DBMS_REPAIR 包標(biāo)記出壞塊exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS

14、('<schema>','<tablename>');4. 使用 Create table as select命令將表中其它塊上的記錄保存到另一張表上create table corrupt_table_bakasselect * from corrupt_table;5. 用 DROP TABLE 命令刪除有壞塊的表drop table corrupt_table;6. 用 alter table rename命令恢復(fù)原來的表alter table corrupt_table_bakrename to corrupt_table;7.

15、如果表上存在索引，則要重建表上的索引八、操作系統(tǒng)相關(guān)維護(hù)DBA 要注意對(duì)操作系統(tǒng)的監(jiān)控：l文件系統(tǒng)的空間使用情況 （df -k）， 必要時(shí)對(duì) Oracle 的警告日志及 TRC 文件進(jìn)行清理l如果 Oracle 提供網(wǎng)絡(luò)服 務(wù)，檢查網(wǎng)絡(luò)連接是否正常l檢查操作 系統(tǒng)的資源使用情況 是否正常l檢查數(shù)據(jù)庫(kù) 服務(wù)器有沒有硬件故障，如磁盤、內(nèi)存報(bào)錯(cuò).數(shù)據(jù)字典和動(dòng)態(tài)性能視圖數(shù)據(jù)字典是 oracle數(shù)據(jù)庫(kù)的 最重要的組成部分， 它提供了數(shù)據(jù)庫(kù)的 相關(guān)系統(tǒng)信息；動(dòng)態(tài)性能視圖記載了例程啟動(dòng)以來的相關(guān)性能信息。數(shù)據(jù)字典記載了數(shù)據(jù)庫(kù)的 系統(tǒng)信息，它是只讀表和視圖的集會(huì)。數(shù)據(jù)字典包含數(shù)據(jù)字典基表和數(shù)據(jù)字典視圖兩部分，

16、其中，基表存儲(chǔ)數(shù)據(jù)庫(kù)的 基本信息，普通用戶不能之間訪問數(shù)據(jù)字典基表；數(shù)據(jù) 字典視圖是基于數(shù)據(jù)字典基表建立的視圖， 普通用戶可以通過查詢數(shù)據(jù)字典視圖取得系統(tǒng)信息。數(shù)據(jù)字典視圖主要包括USER_XXX,ALL_XXX,DBA_X三XX種類型 。USER_XX用X 于顯示當(dāng)前用戶所擁有的所有對(duì)象，它只返回 用戶所對(duì)應(yīng)的所有對(duì)象。DBA_XXX用于顯示整個(gè) 數(shù)據(jù)庫(kù)范圍內(nèi)的詳細(xì)系統(tǒng)信息，它會(huì)顯示所有方案所擁有的數(shù)據(jù)庫(kù)對(duì) 象。常用數(shù)據(jù)字典DICT用于顯示當(dāng)前用戶可訪問的所有數(shù)據(jù) 字典視圖， 并給出了這些數(shù)據(jù)字典視圖的作用。DICT_COLUMN用S于顯示數(shù)據(jù)字典視圖的每個(gè)列的作用。 DUAL用于取得函數(shù)的

17、返回值。GLOBAL_NAM用E于顯示當(dāng)前數(shù)據(jù)庫(kù)的 全名。IND 用于顯示當(dāng)前用戶所擁有的所有索引和索引的統(tǒng)計(jì)信息。OBJ用于顯示當(dāng)前用戶所擁有的所有對(duì)象。SEQ用于顯示當(dāng)前用戶所擁有的所有序列。SYN用于顯示當(dāng)前用戶所擁有的同義詞和同義詞所對(duì)應(yīng)的數(shù)據(jù)庫(kù)對(duì) 象名。TAB用于顯示當(dāng)前用戶所用于的表， 視圖和序列。動(dòng)態(tài)性能視圖用于記錄當(dāng)前例程的活動(dòng)信息。啟動(dòng)例程時(shí)， oracle會(huì)自動(dòng)建立動(dòng)態(tài)性能視圖；停止例程時(shí)， oracle會(huì)自動(dòng)刪除動(dòng) 態(tài)性能視圖。需要注意的時(shí)， 數(shù)據(jù)字典的信息時(shí)從數(shù)據(jù)文件中 取得，而動(dòng)態(tài)性能視圖時(shí)從 SGA和控制文件中 取得。通過查詢動(dòng)態(tài)性能視圖，一方面可以 獲得性能數(shù)據(jù)，

18、 另一方面可以取得與磁盤和內(nèi)存結(jié)構(gòu)相關(guān)的其他信息。所有的動(dòng)態(tài)性能視圖都是以 V_$開始的，oracle為每個(gè)動(dòng)態(tài)性能視圖提供了相應(yīng)的同義詞（以 V$開始） 常用的動(dòng)態(tài)性能視圖V$FIXED_TABL用E 于列出所有可用的動(dòng)態(tài)性能視圖和動(dòng)態(tài)性能表。V$INSTANC用E 于獲取當(dāng)前例 程的詳細(xì)信息。V$SGA用于取得 SGA更詳細(xì)的信息。V$PARAMETE用R于取得初始化 參數(shù)的詳細(xì)信息。 V$VERSION用于取得 oracle版本的詳細(xì)信息。V$OPTION用于顯示已經(jīng)安裝的 oracle選項(xiàng)。其中，TRUE表示該選項(xiàng)已經(jīng)安裝，F(xiàn)ALSE表示該選項(xiàng)沒有安裝。V$SESSION用于顯示會(huì)話的

19、詳細(xì)信息。V$PROCES用S進(jìn)程）。于顯示與 oracle相關(guān)的所有進(jìn)程的 信息（包括后臺(tái)進(jìn)程和服務(wù)器V$BGPROCES用S于顯示后臺(tái)進(jìn)程的 詳細(xì)信息。V$DATABASE用于取得當(dāng)前數(shù)據(jù)庫(kù)的 詳細(xì)信息（如數(shù)據(jù)庫(kù) 名，日志模式以及建立時(shí)間）。V$CONTROLFIL用E 于取得當(dāng)前數(shù)據(jù)庫(kù)所有控制文件的信息。V$DATAFILE用于取得當(dāng)前數(shù)據(jù)庫(kù) 所有數(shù)據(jù)文件的 詳細(xì)信息。V$DBFILE 用于取得數(shù)據(jù)文件 編號(hào)及名稱。V$LOGFILE用于顯示重做日志成 員的信息。V$LOG用于顯示日志組的詳細(xì)信息。V$THREAD用于取得重做線程的詳細(xì)信息。 V$LOCK用于顯示鎖信息。V$LOCKED

20、_OBJEC用T于顯示被加鎖的數(shù)據(jù)庫(kù)對(duì) 象。V$ROLLNAM和E V$ROLLSTATV$ROLLNAM動(dòng)E態(tài)性能視圖用于顯示處于 online狀態(tài)的 undo 段，而 V$ROLLSTAT用于顯示 undo 段統(tǒng)計(jì)信息。通過在二者之間執(zhí)行 連接查詢，可以顯示 undo 段的詳細(xì)統(tǒng)計(jì)信息。V$TABLESPAC 用E于 顯 示 表 空 間 的 信 息 。 V$TEMPFILE用于顯示當(dāng)前數(shù)據(jù)庫(kù) 所包含的臨時(shí)文件。2.常用 DBA 管理腳本一、數(shù)據(jù)庫(kù) 構(gòu)架體系1、表空間的監(jiān)控 是一個(gè)重要的任務(wù)，我們必須時(shí)刻關(guān)心表空間的 設(shè)置， 是否滿足現(xiàn)在應(yīng)用的 需求，以下的語(yǔ)句可以查詢到表空間的 詳細(xì)信息S

21、ELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS, MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,CONTENTS,LOGGING,EXTENT_MANAGEMENT, - Columns not available in v8.0.x ALLOCATION_TYPE, - Remove these columns if running PLUGGED_IN, - against a v8.0.x database SEGMENT_SPACE_MANAGEMENT -use only

22、 in v9.2.x or later FROM DBA_TABLESPACESORDER BY TABLESPACE_NAME;2、對(duì)于某些數(shù)據(jù)文件沒有 設(shè)置為自動(dòng)擴(kuò)展的表空間 來說，如果表空間 滿了，就將意味著數(shù)據(jù)庫(kù)可能會(huì)因?yàn)闆]有空間 而停止下來。監(jiān)控表空間， 最主要的就是監(jiān)控剩余空間的大小或者是使用率。以下是 監(jiān)控表空間使用 率與剩余空間大小的語(yǔ)句SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)"

23、;,ROUND(1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"FROM(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKSFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME) D,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE

24、FROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)UNION ALL -if have tempfileSELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",NVL(FRE

25、E_SPACE,0) "FREE_SPACE(M)"FROM(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKSFROM DBA_TEMP_FILESGROUP BY TABLESPACE_NAME) D,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE FROM V$TEMP_S

26、PACE_HEADERGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)3、除了監(jiān)控表空間的 剩余空間，有時(shí) 候我們也 有必要了解 一下該表空間 是否具有自動(dòng)擴(kuò)展空間的能 力，雖然我們建議在生產(chǎn)系統(tǒng) 中預(yù)先分配空間。以下語(yǔ)句將完成這一功能SELECT T.TABLESPACE_NAME,D.FILE_NAME, D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS FROM DBA_TABLESPACES T,DBA_DATA_FILES DWHERE T. TA

27、BLESPACE_NAME =D. TABLESPACE_NAME ORDER BY TABLESPACE_NAME,FILE_NAME4、我相信使用字典管理 的表空間的 也不少吧，因?yàn)樽值涔芾?的表空間中，每 個(gè)表的下一個(gè)區(qū)間的大小是不可以預(yù)料的，所以我們必須監(jiān)控那些表在 字典管理 的表空間中的 下一個(gè)區(qū)間的分配將會(huì)引起性能問題或 由于是非擴(kuò)展的表空間 而導(dǎo)致系統(tǒng)停止。以下語(yǔ)句檢查那些表的擴(kuò)展將引起表空間的 擴(kuò)展。SELECT A.OWNER,A.TABLE_NAME,A.NEXT_EXTENT,A.TABLESPACE_NAME FROM ALL_TABLES A,(SELECT TABL

28、ESPACE_NAME, MAX(BYTES) BIG_CHUNK FROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) FWHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK5、段的占用空間與區(qū)間數(shù)也是很需要注意的一個(gè)問題，如果一 個(gè)段的占用空間太大，或者跨越太多的區(qū)間（在字典管理 的表空間中， 將有嚴(yán)重的性能影響）， 如果段沒有可以再分配的區(qū)間，將導(dǎo)致 數(shù)據(jù)庫(kù)錯(cuò)誤。 所以，段的大小與區(qū)間監(jiān)控也是一個(gè)很重要的工作SELECT S.OWNER,S.SEGM

29、ENT_NAME,S.SEGMENT_TYPE,S.PARTITION_NAME, ROUND(BYTES/(1024*1024),2) "USED_SPACE(M)",EXTENTS USED_EXTENTS,S.MAX_EXTENTS,S.BLOCKS ALLOCATED_BLOCKS, S.BLOCKS USED_BOLCKS,S.PCT_INCREASE,S.NEXT_EXTENT/1024 "NEXT_EXTENT(K)"FROM DBA_SEGMENTS SWHERE S.OWNER NOT IN ('SYS','SY

30、STEM')ORDER BY Used_Extents DESC6、對(duì)象的空間分配與空間利用情況，除 了從各個(gè)方面的分析，如分析表，查詢r(jià)owid 等方法外，其實(shí) oracle 提供了一個(gè)查詢空間的包 dbms_space，如果 我們稍封裝一下，將是非常好用的一個(gè)東西。CREATE OR REPLACE PROCEDURE show_space(p_segname in varchar2,p_type in varchar2 default 'TABLE' , p_owner in varchar2 default user) ASv_segname varchar2(

31、100); v_type varchar2(10); l_free_blks number; l_total_blocks number; l_total_bytes number; l_unused_blocks number; l_unused_bytes number; l_LastUsedExtFileId number; l_LastUsedExtBlockId number;l_LAST_USED_BLOCK number;PROCEDURE p( p_label in varchar2, p_num in number ) ISBEGINdbms_output.put_line(

32、 rpad(p_label,40,'.')| p_num );END;BEGINv_segname := upper(p_segname); v_type := p_type;if (p_type = 'i' or p_type = 'I') then v_type := 'INDEX'end if;if (p_type = 't' or p_type = 'T') then v_type := 'TABLE'end if;if (p_type = 'c' or p_

33、type = 'C') then v_type := 'CLUSTER'end if;- 以下部分不能用于 ASSM dbms_space.free_blocks( segment_owner => p_owner, segment_name => v_segname, segment_type => v_type, freelist_group_id => 0, free_blks => l_free_blks );- 以上部分不能用于 ASSMdbms_space.unused_space( segment_owner =>

34、; p_owner, segment_name => v_segname, segment_type => v_type, total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes,LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, LAST_USED_EXTENT_BLOCK_ID => l_LastUse

35、dExtBlockId, LAST_USED_BLOCK => l_LAST_USED_BLOCK );- 顯示結(jié)果p( 'Free Blocks', l_free_blks );p( 'Total Blocks', l_total_blocks ); p( 'Total Bytes', l_total_bytes );p( 'Unused Blocks', l_unused_blocks ); p( 'Unused Bytes', l_unused_bytes );p( 'Last Used Ext

36、 FileId', l_LastUsedExtFileId );p( 'Last Used Ext BlockId', l_LastUsedExtBlockId ); p( 'Last Used Block', l_LAST_USED_BLOCK ); END;執(zhí)行結(jié)果將如下所示SQL> set serveroutput on; SQL> exec show_space('test'); Free Blocks.1Total Blocks.8Total Bytes.65536Unused Blocks.6Unused Byte

37、s.49152Last Used Ext FileId.1Last Used Ext BlockId.48521Last Used Block.2PL/SQL procedure successfully completed8、數(shù)據(jù)庫(kù)的 索引如果有比較頻繁的 Delete 操作，將可能導(dǎo)致索引 產(chǎn)生很多碎片， 所以，在有的時(shí) 候，需要對(duì)所有的索引重新 REBUILD，以便合并索引塊， 減少碎片，提高查詢速度。SQL> set heading off SQL> set feedback off SQL> spool d:index.sqlSQL> SELECT '

38、alter index ' | index_name | ' rebuild '|'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);' FROM all_indexesWHERE ( tablespace_name != 'INDEXES'OR next_extent != ( 256 * 1024 )AND owner = USER SQL>spool off這個(gè)時(shí)候，我們打開 spool 出來的文件， 就可以直接運(yùn)行了。9、表的主鍵是必要的，沒有

39、主鍵的表可以說是不符合設(shè)計(jì)規(guī)范的， 所以我們需要監(jiān)控表是否有主鍵SELECT table_name FROM all_tables WHERE owner = USER MINUSSELECT table_nameFROM all_constraintsWHERE owner = USER AND constraint_type = 'P'二、性能監(jiān)控1、數(shù)據(jù)緩沖區(qū)的命中率已經(jīng)不是性能調(diào)整中的主要問題了，但是，過低的命中率肯定是不可以的，在任何情況下，我們必須保證有一個(gè)大的 data buffer 和一個(gè)高的命中率。這個(gè)語(yǔ)句可以獲得整體的數(shù)據(jù)緩沖命中率，越高越好SELECT a

40、.VALUE + b.VALUE logical_reads, c.VALUE phys_reads,round(100*(1-c.value/(a.value+b.value),4) hit_ratioFROM v$sysstat a,v$sysstat b,v$sysstat c WHERE a.NAME='db block gets'AND b.NAME='consistent gets'AND c.NAME='physical reads'2、庫(kù)緩沖說明了 SQL語(yǔ)句的重載率，當(dāng)然，一個(gè) SQL語(yǔ)句應(yīng)當(dāng)被執(zhí)行的 越多越好，如果重 載率比較

41、高， 就考慮增加共享池大小或者是提高 Bind 變量的使用以下語(yǔ)句查詢了 Sql 語(yǔ)句的重載率，越低越好SELECT SUM(pins) total_pins,SUM(reloads) total_reloads, SUM(reloads)/SUM(pins)*100 libcache_reload_ratio FROM v$librarycache3、用戶鎖，數(shù)據(jù)庫(kù)的 鎖有的時(shí)候是比較耗費(fèi)資源的，特別是發(fā)生鎖等待的時(shí)候， 我們必須找到發(fā)生等待的鎖，有可能的話，殺掉該進(jìn)程。這個(gè)語(yǔ)句將查找到數(shù)據(jù)庫(kù)中 所有的 DML語(yǔ)句產(chǎn)生的鎖， 還可以發(fā)現(xiàn)， 任何 DML語(yǔ)句其實(shí)產(chǎn)生了兩個(gè)鎖，一個(gè)是表鎖，一個(gè)是

42、行鎖?？梢酝ㄟ^ alter system kill sessionsid,serial# 來殺掉會(huì)話SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK',NULL) LOCK_LEVEL,o.owner,o.object_name,o.object_type, s.sid,s.serial#,s.terminal,s.machine,gram,s.osuser FROM v$session s,v$lock l,db

43、a_objects oWHERE l.sid = s.sidAND l.id1 = o.object_id(+)AND s.username is NOT NULL4、鎖與等待，如果發(fā)生 了鎖等待， 我們可能更想知道是誰(shuí)鎖了表而引起誰(shuí)的等待以下的語(yǔ)句可以查詢到誰(shuí)鎖了表， 而誰(shuí)在等待。SELECT/*+ rule*/lpad('',decode(l.xidusn,0,3,0)|l.oracle_username User_name,o.owner,o.object_name,o.object_type,s.sid,s.serial#FROM v$locked_object l,d

44、ba_objects o,v$session s WHERE l.object_id=o.object_idAND l.session_id=s.sidORDER BY o.object_id,xidusn DESC以上查詢結(jié)果是一個(gè)樹狀結(jié)構(gòu) ，如果有 子節(jié)點(diǎn)，則表示有等待發(fā)生。如果想知道鎖用了哪個(gè)回滾段，還可以關(guān)聯(lián)到 V$rollname ，其中 xidusn 就是回滾段的 USN5、如果發(fā)生 了事務(wù)或鎖，想知道哪些回滾段正在被使用嗎？ 其實(shí)通過事務(wù)表， 我們可以詳細(xì)的查詢到事務(wù)與回滾段之間的關(guān)系。同時(shí)，如果關(guān) 聯(lián)會(huì)話表，我們則可以知道是哪個(gè)會(huì)話發(fā)動(dòng)了這個(gè)事務(wù)。SELECT s.USERNA

45、ME,s.SID,s.SERIAL#,t.UBAFIL "UBA filenum",t.UBABLK "UBA Block number",t.USED_UBLK "Number os undo Blocks Used", t.START_TIME,t.STATUS,t.START_SCNB,t.XIDUSN RollID,r.NAME RollNameFROM v$session s,v$transaction t,v$rollname r WHERE s.SADDR=t.SES_ADDRAND t.XIDUSN=r.usn7、如果

46、利用會(huì)話跟蹤或者是想查看某個(gè)會(huì)話的跟蹤文件，那么查詢到 OS上的進(jìn)程或線程號(hào)是非常重要的，因?yàn)槲募?令名中，就包含這個(gè)信息，以下的語(yǔ)句可以查詢到進(jìn)程或線程號(hào)， 由此就可以找到對(duì)應(yīng)的文件。SELECT p1.value|''|p2.value|'_ora_'|p.spid filename FROMv$process p,v$session s, v$parameter p1, v$parameter p2WHERE = 'user_dump_dest' AND = 'db_name'AND p.a

47、ddr = s.paddrAND s.audsid = USERENV ('SESSIONID');8、在 ORACLE 9i中， 可以監(jiān)控索引的使用，如果沒有使用 到的索引，完全可以刪除掉， 減少 DML操作時(shí)的操作。以下就是開始索引監(jiān)控與停止索引 監(jiān)控的腳本set heading off set echo offset feedback offset pages 10000spool start_index_monitor.sqlSELECT 'alter index '|owner|'.'|index_name|' monitori

48、ng usage;' FROM dba_indexesWHERE owner = USER;spool offset heading on set echo onset feedback onset heading off set echo offset feedback offset pages 10000spool stop_index_monitor.sqlSELECT 'alter index '|owner|'.'|index_name|' nomonitoring usage;' FROM dba_indexesWHERE o

49、wner = USER;spool offset heading on set echo onset feedback on如果需要監(jiān)控更多的用戶，可以將 owner=User 改寫成別的監(jiān)控結(jié)果在視圖 v$object_usage中查詢感謝 fenng ，他提供了一個(gè)更新版的 show_space腳本CREATE OR REPLACE PROCEDURE show_space( p_segname IN VARCHAR2,p_owner IN VARCHAR2 DEFAULT USER, p_type IN VARCHAR2 DEFAULT 'TABLE',p_partiti

50、on IN VARCHAR2 DEFAULT NULL )- This procedure uses AUTHID CURRENT USER so it can query DBA_*- views using privileges from a ROLE and so it can be installed- once per database, instead of once per user who wanted to use it.AUTHID CURRENT_USERasl_free_blks number; l_total_blocks number; l_total_bytes

51、number; l_unused_blocks number; l_unused_bytes number; l_LastUsedExtFileId number; l_LastUsedExtBlockId number; l_LAST_USED_BLOCK number;l_segment_space_mgmt varchar2(255); l_unformatted_blocks number; l_unformatted_bytes number; l_fs1_blocks number; l_fs1_bytes number; l_fs2_blocks number; l_fs2_by

52、tes number; l_fs3_blocks number; l_fs3_bytes number; l_fs4_blocks number; l_fs4_bytes number; l_full_blocks number; l_full_bytes number;- Inline procedure to print out numbers nicely formatted- with a simple label.PROCEDURE p( p_label in varchar2, p_num in number ) ISBEGINdbms_output.put_line( rpad(

53、p_label,40,'.') | to_char(p_num,'999,999,999,999') );END;BEGIN- This query is executed dynamically in order to allow this procedure- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES- via a role as is customary.- NOTE: at runtime, the invoker MUST have access to thes

54、e two- views!- This query determines if the object is an ASSM object or not. BEGINEXECUTE IMMEDIATE'select ts.segment_space_managementFROM dba_segments seg, dba_tablespaces ts WHERE seg.segment_name = :p_segname AND (:p_partition is null orseg.partition_name = :p_partition) AND seg.owner = :p_ow

55、nerAND seg.tablespace_name = ts.tablespace_name' INTO l_segment_space_mgmtUSING p_segname, p_partition, p_partition, p_owner;EXCEPTIONWHEN too_many_rows THEN dbms_output.put_line( 'This must be a partitioned table, use p_partition => '); RETURN;END;- If the object is in an ASSM tables

56、pace, we must use this API- call to get space information; else we use the FREE_BLOCKS- API for the user managed segments. IF l_segment_space_mgmt = 'AUTO' THENdbms_space.space_usage( p_owner, p_segname, p_type, l_unformatted_blocks, l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes, l_fs2_bloc

57、ks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);p( 'Unformatted Blocks ', l_unformatted_blocks ); p( 'FS1 Blocks (0-25) ', l_fs1_blocks );p( 'FS2 Blocks (25-50) ', l_fs2_blocks );p( 'FS3 Blocks (50-75) ',

58、 l_fs3_blocks ); p( 'FS4 Blocks (75-100)', l_fs4_blocks ); p( 'Full Blocks ', l_full_blocks );ELSEdbms_space.free_blocks( segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, freelist_group_id => 0, free_blks => l_free_blks);p( 'Free Blocks', l_free