Oracle 10的資料庫邏輯結構

2.1.1Oracle系統體繫結構與其他資料庫產品不同，Oracle有其自己獨特的系統體繫結構。Oracle系統體繫結構是整個Oracle伺服器系統的架構，是管理和應用Oracle資料服務器的基礎和核心。Oracle系統體繫結構由三部分組成：邏輯結構、物理結構和執行個體。其中，執行個體是維繫物理結構和邏輯結構的核心，2-1和圖2-2所示。圖2-1表明了資料庫三級模式及其物理檔案之間的關係。圖2-1  資料庫模式及其物理檔案關係圖2-2  Oracle系統體繫結構與功能不論是Oracle的應用開發還是資料庫管理都是以執行個體作為切入點的。只不過Oracle的應用程式開發主要是以資料庫的邏輯對象為主(如表、索引和視圖等),而資料庫管理則是針對資料庫的全部內容。Oracle資料庫由構成物理結構的各種檔案組成，如資料檔案、控制檔案和重做記錄檔等；執行個體是Oracle在記憶體中分配的一段地區SGA和伺服器後台進程的集合。Oracle資料庫伺服器就是資料庫和執行個體的組合。2.1.2Oracle邏輯結構Oracle的邏輯結構是一種階層。主要由：資料表空間、段、區和資料區塊等概念組成。邏輯結構是面向使用者的，使用者使用Oracle開發應用程式使用的就是邏輯結構。資料庫儲存層次結構及其構成關係,結構對象也從資料區塊到資料表空間形成了不同層次的粒度關係，2-3和圖2-4所示。 圖2-3  Oracle 10g資料庫階層圖          圖2-4 段、區和資料區塊之間的關係 1. 資料區塊Oracle資料區塊（Data Block）是一組連續的作業系統塊。分配資料庫塊大小是在Oracle資料庫建立時設定的，資料區塊是Oracle讀寫的基本單位。資料區塊的大小一般是作業系統塊大小的整數倍，這樣可以避免不必要的系統I/O操作。從Oracle9i開始，在同一資料庫中不同資料表空間的資料區塊大小可以不同。資料區塊是Oracle最基本的儲存單位，而資料表空間、段、區間則是邏輯組織的構成成員。在資料庫緩衝區中的每一個塊都是一個資料區塊，一個資料區塊不能跨越多個檔案。資料區塊的結構主要包括： ·標題：包括一般的塊資訊，如塊地址，段類型等。 ·表目錄：包括有關表在該資料區塊中的行資訊。·Oracle體繫結構第2章行目錄：包括有關在該資料區塊中行地址等資訊。 ·行資料：包括表或索引資料。一行可跨越多個資料區塊。 ·空閑空間：分配空閑空間是用於插入新的行和需要額外空間的行更新。通過空間管理參數pctfree可控制空閑空間的使用。空閑空間的管理既可以是自動的也可以是手動的。在資料操作中，有兩種語句可以增加資料庫塊的空閑空間：一個是Delete刪除語句，另一個是Update更新現有行。釋放的空閑空間可用於insert語句，如果insert語句是與產生空閑空間的語句在同一個事務之中，並在其後執行，則insert語句可直接使用產生的空閑空間。如果insert語句是在一個與產生空閑空間的語句相分離的事務中，則insert語句可在其他事務提交後，並在其需要空間時，使用之前產生的空閑空間。資料區塊中釋放的空間可能是連續的，也可能不連續。Oracle只有在出現下列情況時，才會合并資料區塊的空閑空間：當insert或update語句要使用一個資料區塊，該資料區塊的空閑空間足以儲存新的一行，而且空閑空間均是片段，資料區塊中連續空間無法插入一行的時候。除此而外，Oracle在系統效能下降時也需要壓縮資料區塊的空間。能夠對空閑空間產生影響的參數有兩個：pctfree和pctused。對於手工管理的資料表空間，在特定段中的所有資料區塊，可使用兩個空間管理參數pctfree和pctused來控制insert和update對空閑空間的使用。當建立或修改表時可指定這兩個參數。建立或修改一個擁有自己的索引段的索引時可指定pctfree參數。pctfree參數為塊中行的更新預留了空閑空間的最小百分比，預設值為10。例如，假定在Create table語句中指定了pctfree為20，則說明在該表的資料區段內每個資料區塊的20%被作為可利用的空閑空間，用於更新已在資料區塊記憶體在的資料行。其餘80%是用於插入新的資料行，直到達到80%為止。顯然，pctfree值越小，則為現存行更新所預留的空間越少。因此，如果pctfree設定得太高，則在全表掃描期間增加I/O，浪費磁碟空間；如果pctfree設定得太低，則會導致行遷移。pctused參數設定了資料區塊是否是閒置界限。當資料區塊的使用空間低於pctused的值時，此資料區塊標誌為空白閑，該空閑空間僅用於插入新的行。如果資料區塊已經達到了由pctfree所確定的上邊界時，Oracle就認為此資料區塊已經無法再插入新的行。例如，假定在Create table語句中指定pctused為40，則當小於或等於39時，該資料區塊才是可用的。所以，可將資料區塊填得更滿，這樣可節省空間的，但卻增加了處理開銷，因為資料區塊的空閑空間總是要被更新的行佔據，所以對資料區塊需要頻繁地進行重新組織。比較低的pctused增加了資料庫的空閑空間，但減少了更新操作的處理開銷。所以，如果pctused設定過高，則會降低磁碟的利用率導致行遷移；若pctused設定過低，則浪費磁碟空間，增加全表掃描時的I/O輸出。pctused是與pctfree相對的參數。那麼，如何選擇pctfree和pctused的值呢？有個公式可供參考。顯然，pctfree和pctused的之和不能超過100。若兩者之和低於100，則空間的利用與系統的I/O之間的最佳平衡點是：pctfree與pctused之和等於100%減去一行的大小占塊空間大小的百分比。例如，如果塊大小為2048位元組，則它需要100個位元組的開銷，而行大小是390位元組（為可用塊的20%）。為了充分利用空間，pctfree與pctused之和最好為80%。那麼，怎樣確定資料區塊大小呢？有兩個因素需要考慮：一是資料庫環境類型。例如，是DSS環境還是OLTP環境？在資料倉儲環境（OLAP或DSS）下，使用者需要進行許多已耗用時間很長的查詢，所以應當使用大的資料區塊。在OLTP系統中，使用者處理大量的小型事務，採用較小資料區塊能夠獲得更好的效果。二是SGA的大小。資料庫緩衝區的大小由資料區塊大小和初始設定檔案的db_block_buffers參數決定。最好設為作業系統I/O的整數倍。 2 ．區 區（Extent）也稱為資料區，是一組連續的資料區塊。當一個表、復原段或臨時段建立或需要附加空間時，系統總是為之分配一個新的資料區。一個資料區不能跨越多個檔案，因為它包含連續的資料區塊。使用區的目的是用來儲存特定資料類型的資料，也是表中資料增長的基本單位。在Oracle資料庫中，分配空間就是以資料區為單位的。一個Oracle對象包含至少一個資料區。設定一個表或索引的儲存參數包含設定它的資料區大小。 3 ．段段（Segment）是由多個資料區構成的，它是為特定的資料庫物件（如表段、索引段、復原段、臨時段）分配的一系列資料區。段內包含的資料區可以不連續，並且可以跨越多個檔案。使用段的目的是用來儲存特定對象。一個Oracle資料庫有4種類型的段：·資料區段：資料區段也稱為表段，它包含資料並且與表和簇相關。當建立一個表時，系統自動建立一個以該表的名字命名的資料區段。·索引段：包含了用於提高系統效能的索引。一旦建立索引，系統自動建立一個以該索引的名字命名的索引段。·復原段：包含了復原資訊，並在資料庫恢複期間使用，以便為資料庫提供讀入一致性和復原未提交的事務，即用來復原事務的資料空間。當一個事務開始處理時，系統為之分配復原段，復原段可以動態建立和撤銷。系統有個預設的復原段，其管理方式既可以是自動的，也可以是手工的。·臨時段：它是Oracle在運行過程中自行建立的段。當一個SQL語句需要臨時工作區時，由Oracle建立臨時段。一旦語句執行完畢，臨時段的區間便退回給系統。 4 ．資料表空間Oracle資料庫（tablespace）是由若干個資料表空間構成的。任何資料庫物件在儲存時都必須儲存在某個資料表空間中。資料表空間對應於若干個磁碟檔案，即資料表空間是由一個或多個磁碟檔案構成的。資料表空間相當於作業系統中的檔案夾，也是資料庫邏輯結構與物理檔案之間的一個映射。每個資料庫至少有一個資料表空間，資料表空間的大小等於所有從屬於它的資料檔案大小的總和。在Oracle 10g中有以下幾種比較特殊的資料表空間：（1）系統資料表空間系統資料表空間（system tablespace）是每個Oracle資料庫都必須具備的。其功能是在系統資料表空間中存放諸如資料表空間名稱、資料表空間所含資料檔案等資料庫管理所需的資訊。系統資料表空間的名稱是不可更改的。系統資料表空間必須在任何時候都可以用，也是資料庫啟動並執行必要條件。因此，系統資料表空間是不能離線的。系統資料表空間包括資料字典、預存程序、觸發器和系統復原段。為避免系統資料表空間產生儲存片段以及爭用系統資源的問題，應建立一個獨立的資料表空間用來單獨儲存使用者資料。 （2）SYSAUX資料表空間SYSAUX資料表空間是隨著資料庫的建立而建立的，它充當SYSTEM的輔助資料表空間，主要儲存除資料字典以外的其他對象。SYSAUX也是許多Oracle 資料庫的預設資料表空間，它減少了由資料庫和DBA管理的資料表空間數量，降低了SYSTEM資料表空間的負荷。（3）暫存資料表空間相對於其他資料表空間而言，暫存資料表空間（temp tablespace）主要用於儲存Oracle資料庫運行期間所產生的臨時資料。資料庫可以建立多個暫存資料表空間。當資料庫關閉後，暫存資料表空間中所有資料將全部被清除。除暫存資料表空間外，其他資料表空間都屬於永久性資料表空間。（4）撤銷資料表空間用於儲存Oracle資料庫撤銷資訊，即儲存使用者復原段的資料表空間稱之為復原資料表空間（或簡稱為RBS撤銷資料表空間（undo tablespace））。在Oracle8i中是rollback tablespace，從Oracle9i開始改為undo tablespace。在Oracle 10g中初始建立的只有6個資料表空間sysaux、system、temp、undotbs1、example和users。其中temp是暫存資料表空間，undotbs1是undo撤銷資料表空間。圖2-5是資料表空間與資料庫及資料檔案之間的對應關係。圖2-5 資料表空間與資料庫及資料檔案之間的對應關係2.1.3管理資料表空間在管理資料表空間時應遵循以下原則：1）使用多重資料表空間。採用多重資料表空間可使資料庫操作更靈活。主要體現在以下方面：  ·將使用者資料與資料字典資料相分離，並將不同資料表空間的資料檔案分別儲存在不同磁碟上可以降低I/O競爭。  ·將一個應用的資料與其他應用相分離，可以避免資料表空間離線時多個應用受到影響。 ·可根據需要將單個資料表空間離線，從而獲得較好的可用性。  ·通過為不同類型的資料庫預留資料表空間，以達到最佳化資料表空間的目的，如更新較高的或唯讀，或臨時段儲存等。  ·備份單個資料表空間。2）為使用者指定資料表空間限額。要建立、管理與使用資料表空間，必須首先以sys使用者並以as sysdba身份登入資料庫。與Oracle9i不同，在Oracle 10g中，啟動SQL*Plus時的帳戶和口令不需加引號。命令格式是：sqlplus sys/<sys password>as sysdba 在Oracle 10g中，建立和管理資料表空間所使用的資料字典和許可權及語句可歸納如下。 1 ．與資料表空間有關的資料字典查詢和使用與資料表空間有關的中繼資料均可從下列資料字典中獲得。主要包括：dba_tablespaces、dba_users、dba_ts_quotas、user_tablespaces、user_ts_quotas、user_extents、user_segments、user_free_space、dba_data_files、dba_extents、dba_free_space、dba_segments、dba_temp_files、dba_undo_extents、dba_rollback_segs、dba_data_files、v_$backup_datafile、v_$database_block_corruption、v_$datafile、v_$datafile_copy、v_$datafile_header、v_$rollstat、v_$segment_statistics、v_$undostat等。以v_$開頭的資料字典均儲存為動態資訊。 2 ．與使用資料表空間有關的系統許可權與資料表空間有關的主要系統許可權有：create tablespace、alter tablespace、drop tablespace、manage tablespace和unlimited tablespace等。其中，unlimited tablespace是允許使用者無限制地訪問所有資料表空間。出於安全考慮，在授予該許可權給使用者時應謹慎。如果使用者不需要該系統許可權，最好撤銷該許可權，否則使用者會利用該許可權蓄意建立大量對象或複製資料，從而塞滿資料表空間導致資料庫伺服器崩潰。 3 ．建立永久性的資料表空間命令格式：SQL>create[undo]tablespace tablespace[datafile filespec[autoextend_clause][,filespec[autoextend_clause]]...][{minimum extent integer[ k｜m]｜blocksize integer[k]｜{logging｜nologging}｜default storage_clause｜{online｜offline}｜{permanent｜temporary}｜extent_management_clause｜segment_management_clause}[ minimum extent integer[k｜m]｜blocksize integer[k]｜{logging｜nologging}｜default storage_clause｜{online｜offline}｜{permanent｜temporary}｜extent_management_clause｜segment_management_clause]...]; 【例2-1】建立一個名為dalianren的資料表空間SQL>create tablespace dalianren nologgingdatafile′D:/oracle/product/10.2.0/oradata/dalianren/dalianren01.ora′size 50m blocksize 8192extent management local uniform size 256ksegment space management auto; 4 ．使一個資料表空間離線命令格式：SQL>alter tablespace<tablespace_name>offline; 【例2-2】將資料表空間dalianren離線SQL>alter tablespace dalianren offline; 注意 system資料表空間不能離線。 5 ．使一個資料表空間聯機命令格式：SQL>alter tablespace<tablespace_name>online; 【例2-3】將資料表空間dalianren聯機SQL>alter tablespace dalianren online; 6 ．使資料表空間唯讀命令格式：SQL>alter tablespace<tablespace_name>read only; 【例2-4】將資料表空間dalianren更改為唯讀SQL>alter tablespace dalianren read only; 7 ．使資料表空間可讀可寫命令格式：SQL>alter tablespace<tablespace_name>read write; 【例2-5】將資料表空間dalianren更改為可讀寫SQL>alter tablespace dalianren read write; 8 ．建立暫存資料表空間命令格式：SQL>create temporary tablespace<tablespace_name>tempfile′<data_file_path_and_file_name>′    size<megabytes>m autoextend<on｜off>extent management local uniform size<extent_size>; 【例2-6】建立暫存資料表空間tempSQL>create temporary tablespace temptempfile ′D:/ oracle/product/10.2.0/oradata dalian/temp01.ora′size 500m autoextend offextent management local uniform size 512k;注意雖然語句alter tablespace中帶有temporary關鍵字，但不能使用帶有temporary關鍵字的alter tablespace語句將一個本地管理的永久資料表空間轉變為本地管理的暫存資料表空間。必須使用create temporary tablespace語句直接建立本地管理的暫存資料表空間。 9 ．添加暫存資料表空間的資料檔案命令格式：SQL>alter tablespace<tablespace_name>add tempfile ′<path_and_file_name>′size<n>m; 【例2-7】為暫存資料表空間temp_ren添加資料檔案SQL>alter tablespace temp_ren add tempfile ′D:/oracle/product/10.2.0/oradata/dalian/temp_ren.dbf′size 100m; 10 ．調整暫存資料表空間的資料檔案命令格式：SQL>alter database tempfile ′<path_and_file_name>′resize<mega_bytes>m; 【例2-8】調整暫存資料表空間的資料檔案大小SQL>alter database tempfile ′D:/oracle/product/10.2.0/oradata/test /temp_ren.ora′ resize 20m; 11 ．將資料表空間的資料檔案或臨時檔案離線命令格式：SQL>alter database  datafile′<path_and_file_name>′ offline;或SQL>alter database tempfile ′<path_and_file_name>′ offline; 【例2-9】將資料表空間的資料檔案或臨時檔案離線SQL>alter database  datafile ′D:/oracle/product/10.2.0/oradata/dalian/temp_ren.ora′ offline;或SQL>alter database  tempfile ′D:/oracle/product/10.2.0/oradata/dalian/temp_ren.ora′offline; 12. 將暫存資料表空間聯機命令格式：SQL>Alter database tempfile ′<path_and_file_name>′online;【例2-10】將暫存資料表空間聯機SQL>Alter database tempfile ′D:/oracle/product/10.2.0/oradata/dalian/temp_ren.ora′ online; 13. 刪除資料表空間，但不刪除其檔案命令格式：SQL>drop tablespace<tablespace_name>; 【例2-11】刪除資料表空間dalianren，但不刪除其檔案SQL>drop tablespace dalianren; 14. 刪除包含目錄內容的資料表空間命令格式：SQL>drop tablespace<tablespace_name>including contents; 【例2-12】刪除資料表空間dalianren及其包含的內容SQL>drop tablespace dalianren including contents; 15 ．刪除包含目錄內容和資料檔案在內的資料表空間命令格式：SQL>drop tablespace<tablespace_name>including contents and datafiles;【例2-13】刪除資料表空間dalianren及其包含的內容以及資料檔案SQL>drop tablespace dalianren including contents and datafiles; 16 ．當含有參照性約束時，刪除包含目錄內容和資料檔案在內的資料表空間命令格式：SQL>drop tablespace<tablespace_name>including contents and datafiles cascade constraints; 【例2-14】將資料表空間dalianren及其包含的內容、資料檔案以及相關約束一同刪除SQL>drop tablespace dalianren including contents and datafiles cascade constraints; 17 ．資料表空間更名Oracle9i中不能直接將資料表空間更名。在Oracle 10g可直接更名永久資料表空間和暫存資料表空間。但是，system和sysaux資料表空間不能更名。命令格式：SQL>alter tablespace<old_tablespacename>rename to<new_tablespacename>; 【例2-15】將資料表空間users更改為newusersSQL>alter tablespace users rename to newusers;在Oracle 10g中，如果一個撤銷資料表空間通過使用pfile的執行個體被更名，則警告記錄檔中將寫入一個資訊，提醒使用者更改undo_tablespace的參數值。注意當使用drop tablespace誤刪除了資料表空間之後，通過查看alert檔案可以確定誤操作的時間。該檔案位於Oracle_Home/admin/<SID>/bdump目錄下，名為alert_<SID>.log，如：D:/oracle/product/10.2.0/admin/test/bdump目錄下的alert_test.log檔案。 18 ．多重暫存資料表空間在Oracle 10g中增加了一個資料表空間組的概念，通過使用資料表空間組使用者可以使用一個以上的資料表空間儲存臨時段。資料表空間組是在第一個資料表空間被指定給該組時，由系統自動隱式建立的。例如：  通過添加現有的資料表空間建立資料表空間組。SQL>alter tablespace temp tablespace group temp_ts_group;  添加一個新的資料表空間給該已經建立的資料表空間組。SQL>create temporary tablespace temp2tempfile ′D:/oracle/product/10.2.0/oradata/test/temp201.dbf ′ size 20m tablespace group temp_ts_group;被指定給組的資料表空間可在視圖中查詢得到。SQL>select*from dba_tablespace_groups;group_nametablespace_name------------------------------------------------------------temp_ts_grouptemptemp_ts_grouptemp22 rows selected.SQL>一旦建立了資料表空間組，就可以將其指定給使用者或作為預設的暫存資料表空間，就像一個資料表空間一樣。 ·將資料表空間組指定給使用者，作為暫存資料表空間。SQL>alter user scott temporary tablespace temp_ts_group; ·將資料表空間組作為預設的暫存資料表空間。SQL>alter database default temporary tablespace temp_ts_group; ·資料表空間也可以從資料表空間組中移出。SQL>alter tablespace temp2 tablespace group; ·查詢資料表空間組。SQL>select*from dba_tablespace_groups;group_nametablespace_name------------------------------------------------------------temp_ts_grouptemp1 row selected.SQL>理論上，一個資料表空間組包含多少資料表空間是沒有最大限制的，但必須至少包含一個資料表空間。當最後一個資料表空間被刪除後，該資料表空間組也被隱式地刪除。若該資料表空間組仍然被指定做暫存資料表空間，則不可以刪除該組中的最後一個資料表空間成員。同時，資料表空間組不能與資料表空間同名。2.1.4資料表空間的相關查詢列出資料表空間、資料表空間的檔案、分配的空間、空閑空間以及下一個空閑分區，如下所示。set linesize 132set pagesize 60col tablespace_name format a12col file_name format a38col tablespace_kb heading ′TABLESPACE｜TOTAL KB′col kbytes_free heading ′TOTAL FREE｜KBYTES′select ddf.tablespace_name tablespace_name，ddf.file_name file_name，ddf.bytes/1024 tablespace_kb,sum(fs.bytes)/1024 kbytes_free,max(fs.bytes)/1024 next_freefrom sys.dba_free_space fs,sys.dba_data_files ddfwhere ddf.tablespace_name=fs.tablespace_namegroup by ddf.tablespace_name,ddf.file_name,ddf.bytes/1024order by ddf.tablespace_name,ddf.file_name;列出資料檔案，資料表空間名以及大小，如下所示。col file_name format a50col tablespace_name format a10select file_name,tablespace_name,round(bytes/1024000) MBfrom dba_data_filesorder by file_name;列出資料表空間、大小、空閑空間以及空閑空間的百分比，如下所示。select ddf.tablespace_name,sum(ddf.bytes) total_space,sum(dfs.bytes) free_space,round(((nvl(sum(dfs.bytes),0)/sum(ddf.bytes))*100),2) pct_freefrom dba_free_space dfs,dba_data_files ddf where ddf.tablespace_name=dfs.tablespace_name (+) group by ddf.tablespace_nameorder by ddf.tablespace_name;計算資料表空間每個資料檔案實際的最小空間以及對應的檔案名稱，其大小與磁碟作業系統中顯示的不同，如下所示。該語句運行需要較長時間。Select substr(df.file_name,1,70) filename,max(de.block_id*(de.bytes/de.blocks)+de.bytes)/1024 min_sizefrom dba_extents de,dba_data_files dfwhere de.file_id=df.file_idgroup by df.file_name;