Oracle存儲空間管理及應(yīng)用方案

1、Oracle存儲空間管理及應(yīng)用摘要：本文詳細(xì)介紹了Oracle存儲架構(gòu)及其存儲體系的管理和應(yīng)用，包括表空間、回滾段、臨時表等，還對其在管理和應(yīng)用時常見的錯誤進(jìn)行了分析探討主題詞：Oracle技術(shù) 表空間 數(shù)據(jù)文件 回滾段 臨時表 錯誤引言數(shù)據(jù)庫空間的有效使用和維護(hù)不僅是數(shù)據(jù)庫管理的重要工作，也是大多數(shù)開發(fā)人員所關(guān)心的內(nèi)容，它直接關(guān)系到數(shù)據(jù)庫性能的發(fā)揮。Oracle提供了不少方法用于數(shù)據(jù)空間的使用、監(jiān)控和維護(hù)，同時也在各版本中陸續(xù)對這方面的功能進(jìn)行了增強(qiáng)，目的在于簡化這方面工作的復(fù)雜度，提高應(yīng)用的運(yùn)行效率。本文希望通過系統(tǒng)地介紹這方面的有關(guān)概念，讓大家能更好地規(guī)劃使用數(shù)據(jù)空間，正確使用Oracl

2、e提供的有關(guān)功能特性，提高應(yīng)用的執(zhí)行效率。Oracle數(shù)據(jù)庫的存儲體系及有關(guān)概念2.1 Oracle數(shù)據(jù)庫的邏輯結(jié)構(gòu)從應(yīng)用者的角度來考察數(shù)據(jù)庫的組成。自下向上，數(shù)據(jù)庫的邏輯結(jié)構(gòu)共有6層：2.2 Oracle數(shù)據(jù)庫的存儲結(jié)構(gòu)數(shù)據(jù)庫的存儲結(jié)構(gòu)指邏輯結(jié)構(gòu)在物理上的實(shí)現(xiàn)，共有3層 其中：數(shù)據(jù)文件：用于存放所有的數(shù)據(jù)，以DBF為擴(kuò)展名。日志文件：記錄了對數(shù)據(jù)庫進(jìn)行的所有操作，以LOG為擴(kuò)展名。控制文件：記錄了數(shù)據(jù)庫所有文件的控制信息，以CTL為擴(kuò)展名。綜上，Oracle數(shù)據(jù)庫的數(shù)據(jù)存儲空間在邏輯上分為多個表空間，每個表空間由系統(tǒng)中的一個或多個物理數(shù)據(jù)文件構(gòu)成；Oracle存儲數(shù)據(jù)的基本單位是塊，其大小在

3、建庫時由DB_BLOCK_SIZE參數(shù)確定，一個或多個連續(xù)的塊構(gòu)成一個區(qū)間（EXTENT），它作為數(shù)據(jù)對象存儲的基本單位來使用。在Oracle中，每個基本數(shù)據(jù)對象使用的空間稱為段（SEGMENT），段存放在唯一的表空間上，每個段實(shí)際上是一系列區(qū)片（更為準(zhǔn)確地是數(shù)據(jù)塊）的集合。每個簡單數(shù)據(jù)對象對應(yīng)一個段；對于分區(qū)對象如分區(qū)表、索引，則每個（子）分區(qū)對應(yīng)一個段，由各個（子）分區(qū)共同構(gòu)成一個完整的數(shù)據(jù)對象。名詞解釋：表空間（Tablespace）：為數(shù)據(jù)庫提供使用空間的邏輯結(jié)構(gòu)，其對應(yīng)物理結(jié)構(gòu)是數(shù)據(jù)文件，一個表空間可以包含多個數(shù)據(jù)文件。表空間可分為System表空間、非System表空間和回滾段表空

4、間，其中，System表空間是安裝數(shù)據(jù)庫時自動建立的，它包含數(shù)據(jù)庫的全部數(shù)據(jù)字典，存儲過程、包、函數(shù)和觸發(fā)器的定義以及系統(tǒng)回滾段。除此之外，還能包含用戶數(shù)據(jù)。段（Segment）:數(shù)據(jù)庫一種邏輯結(jié)構(gòu)，如表段，索引段，回滾段等，段存在于表空間中，并對應(yīng)一定的存儲空間，共有四種類型的段：表/數(shù)據(jù)（Table/Data）：保存表數(shù)據(jù)索引(Index)：保存索引數(shù)據(jù)回滾(Rollback)：保存回滾數(shù)據(jù)，用于回滾一次事務(wù)處理，以及保持讀操作一致性臨時(Temporary)：用于滿足排序的需要，比如查詢和索引創(chuàng)建中的ORDER BY從句區(qū)間（Extent）:段的存儲可以分成一個或多個區(qū)間，每個區(qū)間占用一

5、定數(shù)量的數(shù)據(jù)塊（block）塊（Block）:數(shù)據(jù)庫最小的存儲單位，由Block參數(shù)（db_block_size）指定表空間及數(shù)據(jù)文件3.1 表空間簡介通常，有以下幾種類型的表空間（除數(shù)據(jù)表空間需用戶自己創(chuàng)建外，其它6種表空間在數(shù)據(jù)庫創(chuàng)建時均會默認(rèn)建立1個）：3.1.1 系統(tǒng)表空間（SYSTEM）系統(tǒng)表空間是每個Oracle數(shù)據(jù)庫都必須具備的部分，是安裝數(shù)據(jù)庫時自動建立的。它包含數(shù)據(jù)庫的全部數(shù)據(jù)字典，存儲過程、包、函數(shù)和觸發(fā)器的定義以及系統(tǒng)回滾段等管理數(shù)據(jù)庫自身所需的信息。一般來說，應(yīng)該盡量避免在SYSTEM表中存儲非SYSTEM用戶的對象。因為這樣會帶來數(shù)據(jù)庫維護(hù)和管理的很多問題。一旦SYS

6、TEM表空間損壞了，只能重新生成數(shù)據(jù)庫。3.1.2 臨時表空間（TEMP）臨時表空間并不包含真正的數(shù)據(jù)，臨時表空間中存儲數(shù)據(jù)庫中動態(tài)生成的對象，如排序操作或表連接時的臨時數(shù)據(jù)等；臨時表空間也是Oracle用于存儲其所有臨時表的所在。在一些非常繁忙的數(shù)據(jù)庫中，可能會存在多個臨時表空間。3.1.3 工具表空間（TOOLS）工具表空間用于保存數(shù)據(jù)庫工具軟件所需的數(shù)據(jù)庫對象，如像Oracle Reports這樣的工具軟件在工作時就有自己的一組專用表。Oracle Reports把這些表存放在數(shù)據(jù)庫中。大多數(shù)DBA都將支持工具運(yùn)行所需的表存放在這個表空間中。3.1.4 用戶表空間（USERS）用戶表空間

7、用于存放用戶的私有信息，用戶的專用數(shù)據(jù)庫對象。3.1.5 回滾表空間（RBS）Oracle數(shù)據(jù)庫需要在一個地方保存恢復(fù)（Undo）信息?；貪L表空間中存放數(shù)據(jù)庫對象的回滾段，在出現(xiàn)失敗和臨時終止事務(wù)時，可以用回滾段使數(shù)據(jù)庫對象退回到舊值。3.1.6 數(shù)據(jù)和索引表空間數(shù)據(jù)表空間用來存放用戶的應(yīng)用數(shù)據(jù)，索引表空間用來存放應(yīng)用數(shù)據(jù)對象的索引。索引可以幫助Oracle迅速找到表中存放的數(shù)據(jù)。3.2 表空間的管理和使用3.2.1 創(chuàng)建表空間創(chuàng)建表空間的腳本示例如下：create tablespace ts_name datafile-創(chuàng)建名為ts-name的表空間 c:oracleoradatafile1

8、.dbf size 100m ,-表空間的文件1，大小為100Mc:oracleoradatafile2.dbf size 100m -表空間的文件2，大小為100Mminimum extent 550k -最小區(qū)間大小為550Klogging/nologging-記錄日志/不記錄日志default storage (initial 500k -初始大小為500Knext 500k -下一個區(qū)大小為500Kmaxextents 500 -最大區(qū)間數(shù)為500個pctinccease 0) -增量為0online/offline -聯(lián)機(jī)/脫機(jī)permanent/temporary -永久/臨時【巡

9、檢實(shí)例】-創(chuàng)建數(shù)據(jù)表空間-數(shù)據(jù)文件e:oracleoradataqinxjgl_data01.dbf，初始尺寸1G，可自動擴(kuò)展，最大為5G-存儲屬性：初始大小 10M,下一個大小10M,增量為0，最大數(shù)量為500create tablespace xjgl_data datafile e:oracleoradataqinxjgl_data01.dbf size 1000Mautoextend on next 500m maxsize 5000m default storage(initial 10M next 10M maxextents 500 pctincrease 0);-創(chuàng)建索引表空間

10、（最好跟數(shù)據(jù)表空間建在不同的磁盤上）-數(shù)據(jù)文件：e:oracleoradataqinxjgl_idx01.dbf，初始尺寸500G，可自動擴(kuò)展，最大為2G-存儲屬性：初始大小 5M,下一個大小5M,增量為0，最大數(shù)量為400create tablespace xjgl_idx datafile e:oracleoradataqinxjgl_idx01.dbf size 500Mautoextend on next 250m maxsize 2000m default storage(initial 5M next 5M maxextents 400 pctincrease 0);-創(chuàng)建LOB段

11、表空間-數(shù)據(jù)文件：e:oracleoradataqinxjgl_idx01.dbf，初始尺寸500G，可自動擴(kuò)展，最大為1G-存儲屬性：初始大小 5M,下一個大小5M,增量為0，最大數(shù)量為100create tablespace xjgl_data_lobs datafile e:oracleoradataqinxjgl_data_lobs01.dbf size 500Mautoextend on next 250m maxsize 1000mdefault storage(initial 10M next 10M maxextents 100 pctincrease 0);3.2.2 改變表

12、空間尺寸為表空間增加新的數(shù)據(jù)文件 其腳本如下所示:alter tablespace xjgl_data add datafile e:oracleoradataqinxjgl_data02.dbf size 200m 調(diào)整數(shù)據(jù)文件尺寸其腳本如下所示:alter database datafile e:oracleoradataqinxjgl_data01.dbf resize 400m允許（或禁止）數(shù)據(jù)文件的自動擴(kuò)展 其腳本如下所示alter database datafile e:oracleoradataqinxjgl_data02.dbf autoextend on next 10m m

13、axsize 500m; 改變數(shù)據(jù)文件的可用性 其腳本如下所示alter database datafile e:oracleoradataqinxjgl_data02.dbf onlineoffline以上調(diào)整通過Oracle DBA Studio進(jìn)行也可調(diào)整數(shù)據(jù)文件尺寸時，如果是減小，不能減小到小于已用到空間數(shù)量3.2.3 改變表空間狀態(tài)和存儲設(shè)置表空間聯(lián)機(jī) 其腳本如下所示:ALTER TABLESPACE xjgl_data ONLINE表空間脫機(jī) 其腳本如下所示:ALTER TABLESPACE xjgl_data OFFLINE NORMALTEMPORARY/ IMMEDIATE/

14、 FOR RECOVER設(shè)置表空間為只讀ALTER TABLESPACE xjgl_data READ ONLY修改表空間的存儲設(shè)置ALTER TABLESPACE xjgl_data DEFAULT STORAGE ()整理空間碎片可通過先將某表空間用export導(dǎo)出，再用import將其導(dǎo)入實(shí)現(xiàn)刪除表空間DROP TABLESPACE “xjgl_data” including contents;注： 表空間非空時，加上including contents關(guān)鍵字才可將其刪除刪除表空間并不是刪除正在使用的數(shù)據(jù)文件，必須使用操作系統(tǒng)刪除數(shù)據(jù)數(shù)據(jù)文件3.2.4表空間遷移可通過下列步驟實(shí)現(xiàn)（只適用

15、于非系統(tǒng)表空間。不能用于回滾段、臨時段的表空間）置表空間狀態(tài)為脫機(jī)alter tablespace xjgl_data offline;使用操作系統(tǒng)命令來移動文件$move e:oracleoradataqinxjgl_data01.dbf e:使用alter tablespace來更改數(shù)據(jù)庫中的文件名alter database rename file e:oracleoradataqinxjgl_data01.dbf to e:xjgl_data01.dbf;重新置表空間狀態(tài)為聯(lián)機(jī)alter tablespace xjgl_data online;3.3 管理數(shù)據(jù)文件數(shù)據(jù)文件是構(gòu)成表空間和

16、數(shù)據(jù)庫的物理文件，存放在操作系統(tǒng)中。數(shù)據(jù)文件管理使用時應(yīng)注意以下幾點(diǎn)：初始化參數(shù)DB_FILES（默認(rèn)為1024）設(shè)置一個實(shí)例可以建立的最大數(shù)據(jù)文件數(shù)。當(dāng)數(shù)據(jù)文件的個數(shù)達(dá)到初始化參數(shù)DB_FILES的值時，必須修改DB_FILES的值并重新啟動數(shù)據(jù)庫使修改生效。歸檔模式下數(shù)據(jù)文件可以單獨(dú)OFFLINE，而非歸檔模式數(shù)據(jù)文件不能正常OFFLINE。數(shù)據(jù)文件的重命名和移植操作有兩種方法實(shí)現(xiàn)。對于單個非SYSTEM表空間的數(shù)據(jù)文件的轉(zhuǎn)移或重命名可以在數(shù)據(jù)庫打開的情況下完成，對于多個表空間或SYSTEM表空間的數(shù)據(jù)文件的轉(zhuǎn)移或重命名，只能在數(shù)據(jù)庫的MOUNT模式下進(jìn)行。Oracle沒有辦法刪除已經(jīng)加載

17、到數(shù)據(jù)庫的數(shù)據(jù)文件。只能通過刪除表空間的方式將表空間和它包含的全部數(shù)據(jù)文件刪除，不能只刪除一個單獨(dú)的數(shù)據(jù)文件。ALTER DATABASE DATAFILE OFFLINE DROP語句只能使數(shù)據(jù)文件不再使用，并沒有從數(shù)據(jù)庫上真正刪除掉數(shù)據(jù)文件。初始化參數(shù)DB_BLOCK_CHECKSUM設(shè)置Oracle是否對BLOCK進(jìn)行校驗。不管是否設(shè)置這個值，SYSTEM表空間都會進(jìn)行校驗。如果設(shè)置為TRUE，那么DBWn進(jìn)程會對每個寫回磁盤的BLOCK進(jìn)行校驗計算，結(jié)果存放到BLOCK的頭部。Oracle下次讀取該塊時，會根據(jù)這個校驗結(jié)果驗證是否存在磁盤錯誤3.4 應(yīng)用開發(fā)時的若干注意事項在多磁盤系統(tǒng)

18、，為提高數(shù)據(jù)庫的性能，數(shù)據(jù)表空間和索引表空間應(yīng)分別存儲，回滾段（RBS）表空間和數(shù)據(jù)表空間分別存儲，系統(tǒng)表（SYSTEM）空間與數(shù)據(jù)庫中其它表空間分別存儲。創(chuàng)建表及其它對象時，要為其分配一個表空間，如果未指定表空間，則使用當(dāng)前系統(tǒng)用戶確省的表空間。一般可以按如下步驟進(jìn)行用create user 命令建立用戶并給其分配默認(rèn)表空間和臨時表空間，如create user XJGL_SA identified by xjgl default tablespace xjgl_data temporary tablespace temp;給用戶授權(quán)grant connect,resource to XJG

19、L_SA;更改以新建的用戶（XJGL_SA）登陸connect xjgl_sa/xjgl;創(chuàng)建表及其它對象這樣建表時若不指定表空間，對象將會默認(rèn)創(chuàng)建到xjgl_data表空間上若表中存在大型對象LOB數(shù)據(jù)字段段，為提高數(shù)據(jù)庫檢索性能，一般都需要為LOB數(shù)據(jù)指定專用的表空間，并指定nocache 參數(shù)（表示不這些LOB數(shù)據(jù)將不會存貯在內(nèi)存中，以便在查詢時獲得最快的訪問速度）在建立索引時，如果不指定相應(yīng)的索引表空間名，那么，該索引就會建立在數(shù)據(jù)表空間中。這是程序員經(jīng)常忽略的一個問題。應(yīng)該在建索引時，明確的指明相應(yīng)的索引表空間(使用using index tablespace子句)。如CREATE

20、TABLE TEST (NAME VARCHAR2(30) PRIMARY KEY USING INDEX TABLESPACE xjgl_idx PCTFREE 0 STORAGE ( )AGE NUMBER);創(chuàng)建觸發(fā)器、存儲過程、函數(shù)和包時都不能為其指定存儲空間，它們都會被存儲到系統(tǒng)（SYSTEM）表空間中回滾段及臨時表的使用4.1 回滾段4.1.1 回滾段概述回滾段用于存放數(shù)據(jù)修改之前的值（包括數(shù)據(jù)修改之前的位置和值）?；貪L段頭部包含正在使用的該回滾段事務(wù)的信息。一個事務(wù)只能使用一個回滾段來存放它的回滾信息，而一個回滾段可以存放多個事務(wù)的回滾信息。4.1.2 回滾段的作用事務(wù)回滾：當(dāng)事

21、務(wù)修改表中數(shù)據(jù)的時候，該數(shù)據(jù)修改前的值（即前影像）會存放在回滾段中，當(dāng)用戶回滾事務(wù)（ROLLBACK）時，ORACLE將會利用回滾段中的數(shù)據(jù)前影像來將修改的數(shù)據(jù)恢復(fù)到原來的值。事務(wù)恢復(fù)：當(dāng)事務(wù)正在處理的時候，例程失敗，回滾段的信息保存在重做日志文件中，ORACLE將在下次打開數(shù)據(jù)庫時利用回滾來恢復(fù)未提交的數(shù)據(jù)。讀一致性：當(dāng)一個會話正在修改數(shù)據(jù)時，其他的會話將看不到該會話未提交的修改。而且，當(dāng)一個語句正在執(zhí)行時，該語句將看不到從該語句開始執(zhí)行后的未提交的修改（語句級讀一致性）。當(dāng)一個長時間的查詢正在執(zhí)行時，若其他會話改變了該查詢要查詢的某個數(shù)據(jù)塊，ORACLE將利用回滾段的數(shù)據(jù)前影像來構(gòu)造一個讀

22、一致性視圖。4.1.3 回滾段的種類系統(tǒng)回滾段：當(dāng)數(shù)據(jù)庫創(chuàng)建后，將自動創(chuàng)建一個系統(tǒng)回滾段，該回滾段只用于存放系統(tǒng)表空間中對象的前影像。非系統(tǒng)回滾段：擁有多個表空間的數(shù)據(jù)庫至少應(yīng)該有一個非系統(tǒng)回滾段，用于存放非系統(tǒng)表空間中對象的數(shù)據(jù)前影像。非系統(tǒng)回滾段又分為私有回滾段和公有回滾段，私有回滾段應(yīng)在參數(shù)文件的ROLLBACKSEGMENTS參數(shù)中列出，以便例程啟動時自動使其在線（ONLINE）。公有回滾段在例程啟動時自動在線。 DEFERED（延遲）回滾段：該回滾段在表空間離線（OFFLINE）時由系統(tǒng)自動創(chuàng)建，當(dāng)表空間再次在線（ONLINE）時由系統(tǒng)自動刪除，用于存放表空間離線時產(chǎn)生的回滾信息。4

23、.1.4 回滾段的使用分配回滾段：當(dāng)事務(wù)開始時，ORACLE將為該事務(wù)分配回滾段，并將擁有最少事務(wù)的回滾段分配給該事務(wù)。事務(wù)可以用以下語句申請指定的回滾段： SET TRANSTRACTION USE ROLLBACK SEGMENT rollback_segment事務(wù)將以順序，循環(huán)的方式使用回滾段的區(qū)（EXTENTS），當(dāng)當(dāng)前區(qū)用滿后移到下一個區(qū)。幾個事務(wù)可以寫在回滾段的同一個區(qū)，但每個回滾段的塊只能包含一個事務(wù)的信息?；貪L段的擴(kuò)展（EXTEND）：當(dāng)當(dāng)前回滾段區(qū)的所有塊用完而事務(wù)還需要更多的回滾空間時，回滾段的指針將移到下一個區(qū)。當(dāng)最后一個區(qū)用完，指針將移到第一個區(qū)的前面?；貪L段指針移到

24、下一個區(qū)的前提是下一個區(qū)沒有活動的事務(wù)，同時指針不能跨區(qū)。當(dāng)下一個區(qū)正在使用時，事務(wù)將為回滾段分配一個新的區(qū)，這種分配稱為回滾段的擴(kuò)展?；貪L段將一直擴(kuò)展到該回滾段區(qū)的個數(shù)到達(dá)回滾段的參數(shù)MAXEXTENTS的值時為止回滾段的回收: OPTIMAL(最佳大小)參數(shù)指明回滾段空閑時收縮到的位置，指明回滾段的OPTIMAL參數(shù)可以減少回滾段空間的浪費(fèi)。4.1.5 回滾段常用操作創(chuàng)建回滾段CREATE PUBLIC ROLLBACK SEGMENT rollback_segmentTABLESPACE tablespaceSTORAGE (INITIAL integerK|M NEXT integer

25、K|MMINEXTENTS integerMAXTENTS integer|UNLIMITEDOPTIMAL integerK|M|NULL) 注：回滾段可以在創(chuàng)建時指明PRIVATE或PUBLIC，一旦創(chuàng)建將不能修改。MINEXTENTS 必須大于等于2PCTINCREASE必須是0OPTIMAL如果要指定，必須大于等于回滾段的初始大?。ㄓ蒑INEXTENTS指定）建議：一般情況下，INITIAL=NEXT設(shè)置OPTIMAL參數(shù)來節(jié)約空間的使用不要設(shè)置MAXEXTENTS為UNLIMITED回滾段應(yīng)創(chuàng)建在一個特定的回滾段表空間內(nèi)使回滾段在線當(dāng)回滾段創(chuàng)建后，回滾段是離線的，不能被數(shù)據(jù)庫使用，為

26、了使回滾段被事務(wù)利用，必須將回滾段在線。可以用以下命令使回滾段在線：ALTER ROLLBACK SEGMENT rollback_segment ONLINE;為了使回滾段在數(shù)據(jù)庫啟動時自動在線，可以在數(shù)據(jù)庫的參數(shù)文件中列出回滾段的名字。例如在參數(shù)文件中加入以下一行ROLLBACK_SEGMENT=(rbs01,rbs02)修改回滾段的存儲參數(shù)可以使用ALTER ROLLBACK SEGMENT命令修改回滾段的存儲參數(shù)（包括OPTIMAL，MAXEXTENTS）。ALTER ROLLBACK SEGMENT rollback_segmentSTORAGE (NEXT integerK|MMI

27、NEXTENTS integerMAXEXTENTS integer|UNLIMITEDOPTIMAL integerK|M|NULL) 回收回滾段的空間如果指定了回滾段的OPTIMAL參數(shù)，ORACLE將自動回收回滾段到OPTIMAL指定的位置。用戶也可以手動回收回滾段的空間。語法：ALTER ROLLBACK SEGMENT rollback_segment SHRINK TO integer K|M;如果不指明TO integer的數(shù)值，ORACLE將試圖回收到OPTIMAL的位置。使回滾段離線為了達(dá)到以下兩個目的需要回滾段離線： 阻止新的事務(wù)使用該回滾段； 該回滾段必須刪除。ALTER

28、 ROLLBACK SEGMENT rollback_segment OFFLINE;如果有事務(wù)正在使用該回滾段，運(yùn)行該命令后，回滾段的狀態(tài)將是PENDING OFFLINE。事務(wù)結(jié)束后，狀態(tài)將改為OFFLINE，可以通過V$ROLLSTAT查詢回滾段的狀態(tài)。刪除回滾段當(dāng)回滾段不再需要或要重建以改變INITIAL，NEXT或MINEXTENTS參數(shù)時，可以將其刪除。DROP ROLLBACK SEGMENT rollback_segment;查詢回滾段的信息所用數(shù)據(jù)字典：DBA_ROLLBACK_SEGS可以查詢的信息：回滾段的標(biāo)識(SEGMENT_ID)、名稱(SEGMENT_NAME)、所

29、在表空間(TABLESPACE_NAME)、類型(OWNER)、狀態(tài)(STATUS)。如：SQLSELECT segment_name,tablespace_name,owner,status FROM dba_rollback_segs;回滾段的統(tǒng)計信息所用數(shù)據(jù)字典：V$ROLLNAME,V$ROLLSTAT如：SQLSELECT ,s.extents,s.rssize,s.optsize,s.hwmsize,s.xacts,s.status FROM v$rollname n,v$rollstat s WHERE n.usn=s.usn;查看回滾段的當(dāng)前活動事務(wù)所用數(shù)據(jù)字典：V$SESSI

30、ON,V$TRANSACTION。如SQLSELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublkFROM v$session s,v$transaction tWHERE s.saddr=t.ses_addr;回滾段的數(shù)量規(guī)劃對于OLTP系統(tǒng)，存在大量的小事務(wù)處理，一般建議：數(shù)量多的小回滾段；每四個事務(wù)一個回滾段；每個回滾段不要超過十個事務(wù)。對于批處理，一般建議：數(shù)量少的大回滾段；每個事務(wù)一個回滾段。4.2 臨時表的使用4.2.1 臨時表的基本概念Oracle臨時表用來保存事務(wù)或會話期間的中間結(jié)果。在臨時表中保存的數(shù)據(jù)只有對當(dāng)前會話是可

31、見的，任何會話都不能看到其他會話的數(shù)據(jù)，即使在當(dāng)前會話提交數(shù)據(jù)以后也是不可見的。一個會話從來不阻塞另一個會話使用臨時表。即使鎖定臨時表，一個會話也不會阻塞其他會話使用臨時表。臨時表將從用戶臨時表空間中分配空間，或者如果從有定義權(quán)的程序中訪問，將使用程序所有者的臨時表空間。全局臨時表實(shí)際上只是表本身的模板，不包含數(shù)據(jù)。創(chuàng)建臨時表的行為不包括存儲空間的分配，也不包括INITIAL的分配。因此，在運(yùn)行時當(dāng)一個會話首先將數(shù)據(jù)放到臨時表中時，這時將創(chuàng)建這個會話的臨時段。由于每個會話獲取自己的臨時段，每個用戶可能在不同的表空間中為臨時表分配空間。如USER1的default臨時表空間為TEMP1，他的臨時

32、表將從TEMP1中分配空間，USER2的default臨時表空間為TEMP2，他的臨時表將從TEMP2中分配空間。臨時表在每個數(shù)據(jù)庫中只需創(chuàng)建一次，不必在每個存儲過程中創(chuàng)建。臨時表總是存在的，除非手動的刪除它。臨時表作為對象存在數(shù)據(jù)字典中，并且總是保持為空，直到有會話在其中放入數(shù)據(jù)。Oracle允許創(chuàng)建基于臨時表的視圖和存儲過程。4.2.2 臨時表的創(chuàng)建在oracle中，應(yīng)用程序需要的臨時表應(yīng)該在程序安裝時創(chuàng)建，而不是在程序運(yùn)行時創(chuàng)建。有兩種類型的臨時表，會話特有的臨時表和事務(wù)特有的臨時表。創(chuàng)建會話特有的臨時表CREATE GLOBAL TEMPORARY ( ) ON COMMIT PRES

33、ERVE ROWS創(chuàng)建事務(wù)特有的臨時表CREATE GLOBAL TEMPORARY ( ) ON COMMIT DELETE ROWS；ON COMMIT PRESERVE ROWS 說明臨時表是會話指定，當(dāng)中斷會話時截斷表。ON COMMIT DELETE ROWS 說明臨時表是事務(wù)指定，每次提交后截斷表4.2.3 臨時表的特性和性能臨時表只在當(dāng)前連接內(nèi)有效臨時表不建立索引，所以如果數(shù)據(jù)量比較大或進(jìn)行多次查詢時，不推薦使用在僅僅查詢數(shù)據(jù)的時候建議用游標(biāo): open cursor for sql clause;有關(guān)存儲管理的常見Oracle錯誤分析5.1 ORA-01552：沒有可用的非系統(tǒng)

34、回滾段5.1.1錯誤現(xiàn)象ORA-01552 cannot use system rollback segment for non-system tablespace5.1.2原因分析可能有以下幾種原因：除了系統(tǒng)回滾段, 未創(chuàng)建其它回滾段 只創(chuàng)建了PRIVATE回滾段, 但I(xiàn)NITsid.ORA的ROLLBACK_SEGMENTS中未列出這些回滾段 創(chuàng)建了PUBLIC回滾段, 但這些回滾段都處于OFFLINE狀態(tài)5.1.3解決方式根據(jù)以上原因相應(yīng)解決。5.2 ORA-01555：快照太老5.2.1錯誤現(xiàn)象ORA_01555 snapshot too old: rollback segment n

35、umber string with name string too small5.2.2原因分析產(chǎn)生該錯誤，可能有以下幾種原因：回滾段太少/太小數(shù)據(jù)庫中有太多的事務(wù)修改數(shù)據(jù)未提交, 就發(fā)生已提交事務(wù)曾使用的空間被重用, 從而造成一個延續(xù)時間長的查詢所請求的數(shù)據(jù)已經(jīng)不在回滾段中. 回滾段被破壞由于回滾段被破壞, 造成事務(wù)無法將修改前的內(nèi)容(read-consistent snapshot) 放入回滾段FETCH ACROSS COMMIT當(dāng)一個進(jìn)程打開一個CURSOR, 然后循環(huán)執(zhí)行FETCH, UPDATE, COMMIT, 如果更新的表與FETCH的是同一個表, 就很可能發(fā)生ORA-0155

36、5錯誤.不適當(dāng)?shù)腛PTIMAL（最佳大?。﹨?shù)太小的OPTIMAL參數(shù)會使回滾段很快被SHRINK（收縮）, 造成后續(xù)讀取操作訪問時, 先前的內(nèi)容已丟失DB_BLOCK_BUFFER太小如果讀一致性所請求的塊的先前內(nèi)容在緩沖區(qū)中, 那么就不用去訪問回滾段. 而如果緩沖區(qū)太小, 使得先前版本的內(nèi)容在CACHE中的可能性變小, 從而必須頻繁的訪問回滾段來獲取先前的內(nèi)容, 這將大大增大ORA-01555發(fā)生的可能.5.2.3解決方式根據(jù)以上原因相應(yīng)解決。對1) 創(chuàng)建更多的回滾段, 為回滾段設(shè)置較大的EXTENT以及較大的MINEXTENTS對2) 將被破壞的回滾段OFFLINE, 刪除重建對3) 使

37、用大的回滾段（只能減少該錯誤發(fā)生的可能, 不能完全避免）減少提交頻率（只能減少該錯誤發(fā)生的可能, 不能完全避免）建立一個臨時表, 存放要更新的表的查詢列(如主鍵及相關(guān)的條件列), 從臨時表FETCH, 更新原來的表.捕獲ORA-01555錯誤, 關(guān)閉并重新打開CURSOR, 繼續(xù)執(zhí)行循環(huán)對4)仔細(xì)設(shè)計OPTIMAL參數(shù), 不要讓回滾段過于頻繁的EXTEND/SHRINK對5)盡可能的增大 DB_BLOCK_BUFFER值5.3 ORA-01578：Oracle數(shù)據(jù)塊被破壞 5.3.1錯誤現(xiàn)象ORA-01578:Oracle data block corrupted(file # num,blo

38、ck # num)5.3.2原因分析當(dāng)ORACLE訪問一個數(shù)據(jù)塊時，由于1、硬件的I/O錯誤；2、操作系統(tǒng)的I/O錯誤或緩沖問題；3、內(nèi)存或頁問題；4、ORACLE試圖訪問一個未被格式化的系統(tǒng)塊；5、數(shù)據(jù)文件部分溢出等上述幾種情況的一種，都有可能報ORA-01578的錯誤。5.3.3解決方式由于ORACLE只有在訪問到有問題的數(shù)據(jù)文件時才會報錯，所以報錯的時間有可能會比實(shí)際出錯的時間要晚，如果ORA-01578出錯信息提示數(shù)據(jù)壞塊指向的是用戶自己的數(shù)據(jù)文件，則用以下方法來解決： 如果通過下面的SQL語句查出的壞塊出現(xiàn)有索引上，則只需重建索引即可 SQLSelect owner,segment_

39、name,segment_type from dba_extents where file_id= and between block_id and block_id+blocks-1;-(和分別是ORA-01578報出的壞塊出現(xiàn)的文件號和塊號) 如果壞塊出現(xiàn)在表上，先用以下語句分析是否為永久性壞塊（建議多執(zhí)行一兩次，有助于鑒別數(shù)據(jù)壞塊是永久性的（硬盤上的物理壞塊）還是隨機(jī)性的（內(nèi)存或硬件錯誤引起）： SQLAnalyze table validate structure cascade; 執(zhí)行該命令后，可能會出現(xiàn)以下的結(jié)果： ORA-01578：與原先錯誤信息有相同的參數(shù)，為永久性的物理或邏

40、輯壞塊；與原先錯誤信息有不同的參數(shù)，可能與內(nèi)存，page space和I/O設(shè)備有關(guān)。 如果用戶有此表的最新備份，那么最好是用此備份來恢復(fù)此表，或者使用event 10231來取出壞塊以外的數(shù)據(jù)： 先關(guān)閉數(shù)據(jù)庫 編輯init.ora文件，加入： event=”10231 trace name context forever,level 10” .startup restrict 創(chuàng)建一個臨時表：SQLcreate table errortemp as select * from error;(error是壞表的表名) 把event從init.ora文件中刪掉并重起數(shù)據(jù)庫 rename壞表，把臨

41、時表rename成壞表的表名 創(chuàng)建表上的INDEX等5.4 ORA-01628：區(qū)達(dá)到回滾段最大值5.4.1錯誤現(xiàn)象ORA-01628:max # of extents num reached for rollback segment num 5.4.2原因分析這種錯誤通常為一個回滾段和一個表空間已經(jīng)達(dá)到MAXEXTENTS參數(shù)設(shè)置的極限。5.4.3解決方式使用SQL命令A(yù)LTER TABLESPACESTORAGE（MAXEXTENTS XXXX）來增加 MAXEXTENTS，其中“XXXX”值必須大于錯誤信息中所指的數(shù)值，也可以重新創(chuàng)建較大的范圍尺寸，使用帶有選項COMPRESS=Y的Ex

42、port工具導(dǎo)出表，如果表空間有可用空間，先給表做一個備份，用alter tablespace tablespace_name更改其名字，然后再裝載表回數(shù)據(jù)庫。 查看其錯誤出現(xiàn)的地方，如果出現(xiàn)在回滾段或索引上，那么必須將其刪除并重建，如果出現(xiàn)在臨時表空間，修改臨時表空間的存儲字段，便可解決這個問題。 5.5 ORA-01650：回滾段不能擴(kuò)展5.5.1錯誤現(xiàn)象ORA-01650:unable to extend rollback segment NAME by NUM in tablespace NAME5.5.2原因分析該錯誤為回滾段表空間不足引起的，這也是ORACLE DBA最常見ORAC

43、LE錯誤信息當(dāng)用戶在做一個非常龐大的數(shù)據(jù)操作導(dǎo)致現(xiàn)有回滾段的不足，使可分配用的回滾段表空間已滿，無法再進(jìn)行分配，就會出現(xiàn)上述的錯誤。5.5.3解決方式使用“ALTER TABLESPACE tablespace_name ADD DATAFILE filename SIZE size_of_file”命令增加表空間，根據(jù)具體的情況可以增加一個或多個表空間文件。當(dāng)然這與還與你主機(jī)上的裸盤設(shè)備有關(guān)，如果你主機(jī)的裸盤設(shè)備已經(jīng)沒有多余的使用空間，建議不要輕意的增加回滾段表空間的大小，可使用下列的語句先查詢一下剩余的tablespace空間有多少：Select user_name,sql_text fr

44、om V$open_cursor where user_name=;如果多余的空間比較多，就可以適當(dāng)追加一個大的回滾段給表空間使用，從而避免上述的錯誤。你也可以用以下語句來檢測一下rollback segment的競爭狀況：Select class,count from V$waitstat where calss in(system undo header,system undo block,undo header,undo block);和 Select sum(value) from V$sysstat where name in (db_block_gets,consistents g

45、ets); 如果任何一個class in count/sum(value)大于1%，就應(yīng)該考慮增加rollback segment。 5.6 ORA-01652：臨時段不能擴(kuò)展5.6.1錯誤現(xiàn)象ORA-01652:unable to extend temp segment by num in tablespace name 5.6.2原因分析ORACLE臨時段表空間不足，因為ORACLE總是盡量分配連續(xù)空間，一但沒有足夠的可分配空間或者分配不連續(xù)就會出現(xiàn)上述的現(xiàn)象。5.6.3解決方式由于ORACLE將表空間作為邏輯結(jié)構(gòu)單元，而表空間的物理結(jié)構(gòu)是數(shù)據(jù)文件，數(shù)據(jù)文件在磁盤上物理地創(chuàng)建，表空間的所有

46、對象也存在于磁盤上，為了給表空間增加空間，就必須增加數(shù)據(jù)文件。先查看一下指定表空間的可用空間，使用視圖SYS.DBA_FREE_SPACE，視圖中每條記錄代表可用空間的碎片大?。?SQLSelect file_id,block_id,blocks,bytes from sys.dba_free_space where tablespace_name=; 返回的信息可初步確定可用空間的最大塊，看一下它是否小于錯誤信息中提到的尺寸，再查看一下缺省的表空間參數(shù)： SQLSELECT INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,PCT_INCREASE FROM SY

47、S.DBA_TABLESPACES WHERE TABLESPACE_NAME=name; 通過下面的SQL命令修改臨時段表空間的缺省存儲值： SQLALTER TABLESPACE name DEFAULT STORAGE (INITIAL XXX NEXT YYY); 適當(dāng)增大缺省值的大小有可能解決出現(xiàn)的錯誤問題，也可以通過修改用戶的臨時表空間大小來解決這個問題： SQLALTER USER username TEMPORARY TABLESPACE new_tablespace_name; 使用ALTER TABLESPACE命令，一但完成，所增加的空間就可使用，無需退出數(shù)據(jù)庫或使表空間

48、脫機(jī)，但要注意，一旦添加了數(shù)據(jù)文件，就不能再刪除它，若要刪除，就要刪除表空間。 附錄資料：不需要的可以自行刪除年數(shù)據(jù)庫-oracle-學(xué)習(xí)之路數(shù)據(jù)庫基礎(chǔ)數(shù)據(jù)庫主鍵生成Select Substr(To_char(dbms_random.value), 2, 22) | getUUID.NextvalFrom dual ;數(shù)據(jù)庫空間查詢-表空間在哪些空間下select df.tablespace_name 表空間名,totalspace 總空間M,freespace 剩余空間M,round(1-freespace/totalspace)*100,2) 使用率%from(select tablesp

49、ace_name,round(sum(bytes)/1024/1024) totalspacefrom dba_data_filesgroup by tablespace_name) df,(select tablespace_name,round(sum(bytes)/1024/1024) freespacefrom dba_free_spacegroup by tablespace_name) fswhere df.tablespace_name=fs.tablespace_name;-查用戶下所用空間SELECT owner, tablespace_name, ROUND (SUM (B

50、YTES) / 1024 / 1024, 2) USED(M) FROM dba_segmentsGROUP BY owner, tablespace_nameORDER BY SUM (BYTES) DESC;-查用戶下所有表所占空間select OWNER, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mmmfrom dba_segments twhere t.owner = JIANGSUand t.segment_type=TABLEgroup by OWNER, t.segment_name, t.segmen

51、t_typeorder by mmm desc;oracle 之刪除重復(fù)數(shù)據(jù)select a.rowid,a.* from 表名 a where a.rowid != (select max(b.rowid) from 表名 b where a.字段1 = b.字段1 and a.字段2 = b.字段2 )刪除delete from 表名 a where a.rowid != (select max(b.rowid) from 表名 b where a.字段1 = b.字段1 and a.字段2 = b.字段2 )oracle 之查詢數(shù)據(jù)第一條記錄select * from tab rownu

52、m sqlplus /nologSQL*Plus: Release .0 Production on Fri Jan 20 02:29:37 2006Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL connect /as sysdbaConnected to an idle instance.SQL startupORACLE instance started.Total System Global Area 135352820 bytesFixed Size 455156 bytesVariable

53、Size 109051904 bytesDatabase Buffers 25165824 bytesRedo Buffers 679936 bytesDatabase mounted.2)關(guān)閉數(shù)據(jù)庫： HYPERLINK mailto:oraclesuse92 oraclesuse92: sqlplus /nologSQL*Plus: Release .0 Production on Fri Jan 20 02:29:37 2006Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL connect /as

54、 sysdbaConnected to an idle instance.SQL shutdwon abort;3)啟動監(jiān)聽器 HYPERLINK mailto:oraclesuse92 oraclesuse92: lsnrctl start4)停止監(jiān)聽器 HYPERLINK mailto:oraclesuse92 oraclesuse92: lsnrctl stop5)查看監(jiān)聽器狀態(tài) HYPERLINK mailto:oraclesuse92 oraclesuse92: lsnrctl status或lsnrctl命令進(jìn)入監(jiān)聽程序后再用statusoracle 之備份工作背景：Oracle

55、10g 服務(wù)器，Oracle 10g 客戶端，windowsXP 操作平臺要求：Oracle 數(shù)據(jù)庫服務(wù)器對數(shù)據(jù)庫 ytcn 每天自動備份一次。解決方案：利用任務(wù)計劃、批處理文件和 Oracle 的 exp 導(dǎo)出功能，根據(jù)日期自動生成 Oracle 備份文件。詳細(xì)步驟： 1） 創(chuàng)建批處理文件 ytcn.batytcn.bat中詳細(xì)內(nèi)容如下：echo offecho 正在備份銀通網(wǎng) Oracle 數(shù)據(jù)庫，請稍等exp userid=ytcn/ytcnytcn as sysdba file=e:/bak/ytcn/oracle/ytcn/ytcn%date:0,4%date:5,2%date:8,

56、2%.dmp log=e:/bak/ytcn/oracle/ytcn/ytcn%date:0,4%date:5,2%date:8,2%.log full=yecho 任務(wù)完成！其中紅色部分是根據(jù)需要進(jìn)行變動的地方，例如作者的項目名“銀通網(wǎng)”，數(shù)據(jù)庫 ytcn 用戶名 ytcn，密碼 ytcn，要在目錄 e:/bak/ytcn/oracle/ytcn 下生成形如 ytcn20090711.dmp 和 ytcn20090711.log 的備份和日志文件，全表導(dǎo)出。另外：%date%的值在不同的系統(tǒng)、語言版本下可能是不一樣的，控制面板里面區(qū)域選項的設(shè)定也會改變%date%的值。請先在命令行中測試 e

57、cho %date% 的返回值。%date:4,10% 是返回日期函數(shù)，后的第一個參數(shù)是要截取的起始位置（從0開始），第二個參數(shù)是要截取的長度，如沒有則是截取到最后，參數(shù)可酌情修改。 如需要準(zhǔn)確的時間做為文件名，請用%time%函數(shù)，參數(shù)同上。 2） 添加一個任務(wù)計劃 ytcn開始 所有程序 附件 系統(tǒng)工具 任務(wù)計劃 添加任務(wù)計劃 下一步 在 瀏覽 中查找剛剛寫好的 ytcn.bat 文件 任務(wù)名輸入ytcn,執(zhí)行這個任務(wù)選擇每天,下一步 起始時間下午12:00,起始日期2009-7-11,下一步 輸入用戶名及密碼,用戶名要求是管理員權(quán)限用戶名,下一步 完成點(diǎn)擊完成之后，會在任務(wù)計劃欄目下新增

58、一個名為ytcn的任務(wù)計劃，表明已經(jīng)配置完畢。備注：有時點(diǎn)擊完成 之后,系統(tǒng)警告已創(chuàng)建新任務(wù)，但可能不能運(yùn)行，因為無法設(shè)置賬戶信息。指定的錯誤是：Ox80041315：任務(wù)計劃程序服務(wù)沒有運(yùn)行這是因為電腦的任務(wù)計劃程序服務(wù)沒有啟動起來。開始 所有程序 管理工具 服務(wù)，找到Task Scheduler服務(wù)，發(fā)現(xiàn)啟動類型為已禁用,右鍵單擊更改為自動，并把它啟動起來，然后重新添加一次任務(wù)計劃 ytcn 就可以了。oracle之命中率查詢Buffer cache由數(shù)據(jù)塊組成。1. Buffer cache的工作原理LRU列表：MRU 。 LRU.（全表掃描FTS放在LRU端。）緩沖區(qū)塊的狀態(tài)：Free

59、、Pinned、Clean、Dirty.Dirty List或Write List（寫列表）。數(shù)據(jù)庫寫進(jìn)程DBW0將緩沖區(qū)高速緩存中的數(shù)據(jù)寫到數(shù)據(jù)文件中。2. 測量Buffer cache的性能測量Buffer cache的命中率：SQL select 1 - (physical.value - direct.value - lobs.value) / logical.value) Buffer Cache Hit Ratio from V$SYSSTAT physical, V$SYSSTAT direct, V$SYSSTAT lobs, V$SYSSTAT logical where =

60、 physical reads And = physical reads direct and = physical reads direct (lob) And = session logical reads;“Buffer Cache Hit Ratio”的值要 90%.使用STATSPACK來監(jiān)視Buffer cache.使用REPORT.TXT來監(jiān)視Buffer cache.非命中率指標(biāo)：Free Buffer Inspected、Free Buffer Waits、Buffer Busy Waits.（V$sysstat）使用Performance Manager（數(shù)據(jù)庫例程）來監(jiān)視