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

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

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

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

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

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

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

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

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

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

11、elect job,log_user,last_date,failuresfrom dba_jobs;如果 FAILURES 列是一個大于 0 的數(shù)的話， 說明 JOB 運(yùn)行失敗， 要進(jìn)一步的檢查。七、數(shù)據(jù)庫壞塊的 處理當(dāng) Oracle 數(shù)據(jù)庫出現(xiàn)壞塊時， Oracle會在警告日志文件 （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ù)文件的 絕對文件號， <BLOCK> 代表壞塊是數(shù)據(jù)文件 上的第幾個數(shù)據(jù)塊出現(xiàn) 這種情況時，應(yīng)該 首先 檢查是否是硬 件及操作 系統(tǒng)上 的故障導(dǎo)致Oracle 數(shù)據(jù)庫出現(xiàn)壞塊。在 排除了數(shù)據(jù)庫以外的原因后，再對發(fā)生壞塊的數(shù)據(jù)庫對象進(jìn)行處理。1. 確定發(fā)生壞塊的數(shù)據(jù)庫對 象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ā)生壞塊的對 象是一個索引，那么可以直接把索引 DROP 掉后，再根據(jù)表里的記錄進(jìn)行重建；如果發(fā)生壞塊的表的記錄 可以根據(jù)其它表的記錄生成的 話，那么可以直接把這個表 DROP 掉后重建；如果有數(shù)據(jù)庫的 備份，則恢復(fù)數(shù)據(jù)庫的 方法來進(jìn)行修復(fù)；如果表里的記錄沒有 其它辦法恢復(fù)，那么壞塊上的記錄就丟失了， 只能把表中其它數(shù)據(jù)塊上的記錄取出來，然后對這個表進(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 要注意對操作系統(tǒng)的監(jiān)控：l文件系統(tǒng)的空間使用情況 （df -k）， 必要時對 Oracle 的警告日志及 TRC 文件進(jìn)行清理l如果 Oracle 提供網(wǎng)絡(luò)服 務(wù)，檢查網(wǎng)絡(luò)連接是否正常l檢查操作 系統(tǒng)的資源使用情況 是否正常l檢查數(shù)據(jù)庫 服務(wù)器有沒有硬件故障，如磁盤、內(nèi)存報錯.數(shù)據(jù)字典和動態(tài)性能視圖數(shù)據(jù)字典是 oracle數(shù)據(jù)庫的 最重要的組成部分， 它提供了數(shù)據(jù)庫的 相關(guān)系統(tǒng)信息；動態(tài)性能視圖記載了例程啟動以來的相關(guān)性能信息。數(shù)據(jù)字典記載了數(shù)據(jù)庫的 系統(tǒng)信息，它是只讀表和視圖的集會。數(shù)據(jù)字典包含數(shù)據(jù)字典基表和數(shù)據(jù)字典視圖兩部分，

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

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

18、 另一方面可以取得與磁盤和內(nèi)存結(jié)構(gòu)相關(guān)的其他信息。所有的動態(tài)性能視圖都是以 V_$開始的，oracle為每個動態(tài)性能視圖提供了相應(yīng)的同義詞（以 V$開始） 常用的動態(tài)性能視圖V$FIXED_TABL用E 于列出所有可用的動態(tài)性能視圖和動態(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選項。其中，TRUE表示該選項已經(jīng)安裝，F(xiàn)ALSE表示該選項沒有安裝。V$SESSION用于顯示會話的

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

20、_OBJEC用T于顯示被加鎖的數(shù)據(jù)庫對 象。V$ROLLNAM和E V$ROLLSTATV$ROLLNAM動E態(tài)性能視圖用于顯示處于 online狀態(tài)的 undo 段，而 V$ROLLSTAT用于顯示 undo 段統(tǒng)計信息。通過在二者之間執(zhí)行 連接查詢，可以顯示 undo 段的詳細(xì)統(tǒng)計信息。V$TABLESPAC 用E于 顯 示 表 空 間 的 信 息 。 V$TEMPFILE用于顯示當(dāng)前數(shù)據(jù)庫 所包含的臨時文件。2.常用 DBA 管理腳本一、數(shù)據(jù)庫 構(gòu)架體系1、表空間的監(jiān)控 是一個重要的任務(wù)，我們必須時刻關(guān)心表空間的 設(shè)置， 是否滿足現(xiàn)在應(yīng)用的 需求，以下的語句可以查詢到表空間的 詳細(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、對于某些數(shù)據(jù)文件沒有 設(shè)置為自動擴(kuò)展的表空間 來說，如果表空間 滿了，就將意味著數(shù)據(jù)庫可能會因為沒有空間 而停止下來。監(jiān)控表空間， 最主要的就是監(jiān)控剩余空間的大小或者是使用率。以下是 監(jiān)控表空間使用 率與剩余空間大小的語句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)控表空間的 剩余空間，有時 候我們也 有必要了解 一下該表空間 是否具有自動擴(kuò)展空間的能 力，雖然我們建議在生產(chǎn)系統(tǒng) 中預(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、我相信使用字典管理 的表空間的 也不少吧，因為字典管理 的表空間中，每 個表的下一個區(qū)間的大小是不可以預(yù)料的，所以我們必須監(jiān)控那些表在 字典管理 的表空間中的 下一個區(qū)間的分配將會引起性能問題或 由于是非擴(kuò)展的表空間 而導(dǎo)致系統(tǒng)停止。以下語句檢查那些表的擴(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ù)也是很需要注意的一個問題，如果一 個段的占用空間太大，或者跨越太多的區(qū)間（在字典管理 的表空間中， 將有嚴(yán)重的性能影響）， 如果段沒有可以再分配的區(qū)間，將導(dǎo)致 數(shù)據(jù)庫錯誤。 所以，段的大小與區(qū)間監(jiān)控也是一個很重要的工作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、對象的空間分配與空間利用情況，除 了從各個方面的分析，如分析表，查詢rowid 等方法外，其實 oracle 提供了一個查詢空間的包 dbms_space，如果 我們稍封裝一下，將是非常好用的一個東西。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ù)庫的 索引如果有比較頻繁的 Delete 操作，將可能導(dǎo)致索引 產(chǎn)生很多碎片， 所以，在有的時 候，需要對所有的索引重新 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這個時候，我們打開 spool 出來的文件， 就可以直接運(yùn)行了。9、表的主鍵是必要的，沒有

39、主鍵的表可以說是不符合設(shè)計規(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)整中的主要問題了，但是，過低的命中率肯定是不可以的，在任何情況下，我們必須保證有一個大的 data buffer 和一個高的命中率。這個語句可以獲得整體的數(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、庫緩沖說明了 SQL語句的重載率，當(dāng)然，一個 SQL語句應(yīng)當(dāng)被執(zhí)行的 越多越好，如果重 載率比較

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

42、行鎖?？梢酝ㄟ^ alter system kill sessionsid,serial# 來殺掉會話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ā)生 了鎖等待， 我們可能更想知道是誰鎖了表而引起誰的等待以下的語句可以查詢到誰鎖了表， 而誰在等待。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é)果是一個樹狀結(jié)構(gòu) ，如果有 子節(jié)點，則表示有等待發(fā)生。如果想知道鎖用了哪個回滾段，還可以關(guān)聯(lián)到 V$rollname ，其中 xidusn 就是回滾段的 USN5、如果發(fā)生 了事務(wù)或鎖，想知道哪些回滾段正在被使用嗎？ 其實通過事務(wù)表， 我們可以詳細(xì)的查詢到事務(wù)與回滾段之間的關(guān)系。同時，如果關(guān) 聯(lián)會話表，我們則可以知道是哪個會話發(fā)動了這個事務(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、利用會話跟蹤或者是想查看某個會話的跟蹤文件，那么查詢到 OS上的進(jìn)程或線程號是非常重要的，因為文件的 令名中，就包含這個信息，以下的語句可以查詢到進(jìn)程或線程號， 由此就可以找到對應(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操作時的操作。以下就是開始索引監(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 ，他提供了一個更新版的 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