??? 數(shù)據(jù)庫結(jié)構(gòu)(二).邏輯結(jié)構(gòu)
?
?
一、數(shù)據(jù)庫邏輯結(jié)構(gòu)
?
??? 
???????????? <邏輯結(jié)構(gòu)在Oracle體系中的位置>
?
邏輯結(jié)構(gòu)層次：
?
??? 數(shù)據(jù)庫(DataBase)-->表空間(TableSpace)-->段(Segment)-->區(qū)間(Extent)-->數(shù)據(jù)塊(Block)
?
?
1、表空間（TableSpace）
?
??? 表空間是數(shù)據(jù)庫中的基本邏輯結(jié)構(gòu)，任何數(shù)據(jù)庫對象在存儲時(shí)都必須存儲在某個(gè)表空間中。表空間對應(yīng)于若干個(gè)磁盤文件，即表空間是由一個(gè)或多個(gè)磁盤文件構(gòu)成的（物理上的一對多關(guān)系）。表空間相當(dāng)于操作系統(tǒng)中的文件夾，也是數(shù)據(jù)庫邏輯結(jié)構(gòu)與物理文件之間的一個(gè)映射。每個(gè)數(shù)據(jù)庫至少有一個(gè)表空間，表空間的大小等于所有從屬于它的數(shù)據(jù)文件大小的總和。
???
Oracle10g一般包括以下空間：
??? select
*
from
dba_data_files;
??? select
*
from
v$tablespace;
??? select
*
from
dba_tablespaces;
?
(一)系統(tǒng)表空間(SYSTEM TableSpace)
??? SYSTEM是每個(gè)Oracle數(shù)據(jù)庫都必須具備的。其功能是在系統(tǒng)表空間中存放諸如表空間名稱、表空間所含數(shù)據(jù)文件等數(shù)據(jù)庫管理所需的信息。系統(tǒng)表空間的名稱是不可更改的。系統(tǒng)表空間必須在任何時(shí)候都可以用，也是數(shù)據(jù)庫運(yùn)行的必要條件。因此，系統(tǒng)表空間是不能脫機(jī)的。
??? 系統(tǒng)表空間包括數(shù)據(jù)字典、存儲過程、觸發(fā)器和系統(tǒng)回滾段。為避免系統(tǒng)表空間產(chǎn)生存儲碎片以及爭用系統(tǒng)資源的問題，應(yīng)創(chuàng)建一個(gè)獨(dú)立的表空間用來單獨(dú)存儲用戶數(shù)據(jù)。
?
(二)SYSAUX表空間(SYSAUX TableSpace)
??? SYSAUX是隨著數(shù)據(jù)庫的創(chuàng)建而創(chuàng)建的，它充當(dāng)SYSTEM的輔助表空間，主要存儲除數(shù)據(jù)字典以外的其他對象。SYSAUX也是許多Oracle 數(shù)據(jù)庫的默認(rèn)表空間，它減少了由數(shù)據(jù)庫和DBA管理的表空間數(shù)量，降低了SYSTEM表空間的負(fù)荷。
?
(三)臨時(shí)表空間(TEMP TableSpace)
??? 相對于其他表空間而言，TEMP主要用于存儲Oracle數(shù)據(jù)庫運(yùn)行期間所產(chǎn)生的臨時(shí)數(shù)據(jù)（例如order by時(shí)）。數(shù)據(jù)庫可以建立多個(gè)臨時(shí)表空間。當(dāng)數(shù)據(jù)庫關(guān)閉后，臨時(shí)表空間中所有數(shù)據(jù)將全部被清除。除臨時(shí)表空間外，其他表空間都屬于永久性表空間。
?
(四)撤銷表空間(UNDOTBS1 TableSpace)
??? UNDOTBS1是用于保存Oracle數(shù)據(jù)庫撤銷信息，即保存用戶回滾段的表空間。
?
??? 另外10g在創(chuàng)建數(shù)據(jù)庫的時(shí)候還會產(chǎn)生EXAMPLE和USERS表空間，用于存放系統(tǒng)實(shí)例和用戶信息，但在實(shí)際的操作中一般會將EXAMPLE空間刪去，并且盡量少使用USERS空間。在9i中還會生成另外一些如INDEX、CWMLITE、TOOLS等表空間，但在10g中已經(jīng)整合到一起。
2、段(Segment)
?
??? 段是對象在數(shù)據(jù)庫中占用的空間，雖然段和數(shù)據(jù)庫對象是一一對應(yīng)的，但段是從數(shù)據(jù)庫存儲的角度來看的。一個(gè)段只能屬于一個(gè)表空間，當(dāng)然一個(gè)表空間可以有多個(gè)段。表空間和段是邏輯存儲上的一對多的關(guān)系，段不直接和數(shù)據(jù)文件發(fā)生關(guān)系。一個(gè)段可以屬于多個(gè)數(shù)據(jù)文件，關(guān)于段可以指定擴(kuò)展到哪個(gè)數(shù)據(jù)文件上面。
??? select
*
from
dba_segments;
?
(一)數(shù)據(jù)段(Data Segment)
??? 對于每一個(gè)非聚集的表有一數(shù)據(jù)段，表的所有數(shù)據(jù)存放在該段。每一聚集有一數(shù)據(jù)段，聚集中每一個(gè)表的數(shù)據(jù)存儲在該段中。當(dāng)創(chuàng)建一個(gè)表時(shí)，系統(tǒng)自動創(chuàng)建一個(gè)以該表的名字命名的數(shù)據(jù)段。
?
(二)索引段(Index Segment)
??? 每一個(gè)索引有一個(gè)索引段，存儲索引數(shù)據(jù)。一旦建立索引，系統(tǒng)自動創(chuàng)建一個(gè)以該索引的名字命名的索引段。
?
(三)回滾段(Rollback Segment)
??? 由DBA建立，用于臨時(shí)存儲要撤銷的信息，這些信息用于生成一致性數(shù)據(jù)庫信息、在數(shù)據(jù)庫恢復(fù)時(shí)使用，回滾未提交的事務(wù)。當(dāng)一個(gè)事務(wù)開始處理時(shí)，系統(tǒng)為之分配回滾段，回滾段可以動態(tài)創(chuàng)建和撤銷。系統(tǒng)有個(gè)默認(rèn)的回滾段，其管理方式既可以是自動的，也可以是手工的。
?
(四)臨時(shí)段(Temporary Segment)
??? 當(dāng)一個(gè)sql語句需要臨時(shí)工作區(qū)時(shí)，由oracle建立。當(dāng)語句執(zhí)行完畢，臨時(shí)段的范圍退還給系統(tǒng)。
?
?
3、數(shù)據(jù)區(qū)間(Extent)
?
??? 數(shù)據(jù)區(qū)間是一組連續(xù)的數(shù)據(jù)塊。當(dāng)一個(gè)表、回滾段或臨時(shí)段創(chuàng)建或需要附加空間時(shí)，系統(tǒng)總是為之分配一個(gè)新的數(shù)據(jù)區(qū)，一個(gè)數(shù)據(jù)區(qū)不能跨越多個(gè)文件，因?yàn)樗B續(xù)的數(shù)據(jù)塊。在一個(gè)段中可以存在多個(gè)區(qū)間，區(qū)間是為數(shù)據(jù)一次性預(yù)留的一個(gè)較大的存儲空間，直到那個(gè)區(qū)間被用滿，數(shù)據(jù)庫會繼續(xù)申請一個(gè)新的預(yù)留存儲空間，即新的區(qū)間，一直到段的最大區(qū)間數(shù)(Max Extent)或沒有可用的磁盤空間可以申請。
?
??? 使用區(qū)的目的是用來保存特定數(shù)據(jù)類型的數(shù)據(jù)，也是表中數(shù)據(jù)增長的基本單位。在Oracle數(shù)據(jù)庫中，分配空間就是以數(shù)據(jù)區(qū)為單位的。理論上一個(gè)段是可以無窮個(gè)區(qū)間的，但是多個(gè)區(qū)間對ORACLE卻是有性能影響的，ORACLE建議把數(shù)據(jù)分布在盡量少的區(qū)間上，以減少ORACLE的管理與磁頭的移動，但是在某些特殊情況下，需要把一個(gè)段分布在多個(gè)數(shù)據(jù)文件或多個(gè)設(shè)備上，適當(dāng)?shù)募佣鄥^(qū)間數(shù)也是有很大好處的。
???
select
*
from
dba_extents;
?
?
4、數(shù)據(jù)塊(Block)
?
??? 塊是Oracle最基本的存儲單位，在建立數(shù)據(jù)庫的時(shí)候指定，雖然在初始化文件中可見，但是不能修改。為了保證存取的速度，它是OS數(shù)據(jù)塊的整數(shù)倍。ORACLE的操作都是以塊為基本單位，一個(gè)區(qū)間可以包含多個(gè)塊，如果區(qū)間大小不是塊大小的整數(shù)倍，ORACLE實(shí)際也擴(kuò)展到塊的整數(shù)倍。
?
??? 塊的大小由初始參數(shù)DB_BLOCK_SIZE指定。（默認(rèn)8K-8192）決定
數(shù)據(jù)塊大小要考慮以下因素：
?
??? 1.數(shù)據(jù)庫環(huán)境類型。例如，是DSS環(huán)境還是OLTP環(huán)境？在數(shù)據(jù)倉庫環(huán)境（OLAP或DSS）下，用戶需要進(jìn)行許多運(yùn)行時(shí)間很長的查詢，所以應(yīng)當(dāng)使用大的數(shù)據(jù)塊。在OLTP系統(tǒng)中，用戶處理大量的小型事務(wù)，采用較小數(shù)據(jù)塊能夠獲得更好的效果。
??? 2.SGA的大小。
?
??? 塊的內(nèi)部結(jié)構(gòu)與數(shù)據(jù)的存取方法都是比較復(fù)雜的，以表段的塊為例，從簡單的結(jié)構(gòu)上劃分，可以把塊的內(nèi)部劃分成如下幾個(gè)部分：公用頭，表目錄，行目錄，可存取空間等。以下是一個(gè)表塊的大致結(jié)構(gòu)圖：
?
??? 
?
??? (一)塊頭(BLOCK HEADER)：包含著關(guān)于塊類型(表塊、索引塊等等)的信息、關(guān)于塊上活動和過時(shí)事務(wù)信息、磁盤上塊的地址的信息。
??? (二)表目錄(Table directory)：如果給出的話，包含著此塊中存儲各行的表的信息(多個(gè)表的數(shù)據(jù)可能保存在同一個(gè)塊中)。
??? (三)行目錄(Row directory)：包含在塊中發(fā)現(xiàn)的描述行的信息(如地址等)。
???????? 注：以上3部分為塊的開銷(Block Overhead)，剩余部分為可用存儲空間。(一般的8K(8192)的塊可用空間為8168)
??? (四)行數(shù)據(jù)：包括表或索引數(shù)據(jù)。一行可跨越多個(gè)數(shù)據(jù)塊。
??? (五)空閑空間：分配空閑空間是用于插入新的行和需要額外空間的行更新。
?
??? PCTFREE：
???
表示保留該百分比的可用空間用于以后的行更新，避免行遷移。少于此百分比時(shí)，該塊從FREE LIST上撤消下來，不再接收數(shù)據(jù)。如果pctfree設(shè)置得太高，則在全表掃描期間增加I/O，浪費(fèi)磁盤空間；如果pctfree設(shè)置得太低，則會導(dǎo)致行遷移。
?
??? PCTUSED：
???
當(dāng)數(shù)據(jù)塊的使用空間低于pctused的值時(shí)，此數(shù)據(jù)塊標(biāo)志為空閑，該空閑空間僅用于插入新的行。如果數(shù)據(jù)塊已經(jīng)達(dá)到了由pctfree所確定的上邊界時(shí)，Oracle就認(rèn)為此數(shù)據(jù)塊已經(jīng)無法再插入新的行。如果pctused設(shè)置過高，則會降低磁盤的利用率導(dǎo)致行遷移；若pctused設(shè)置過低，則浪費(fèi)磁盤空間，增加全表掃描時(shí)的I/O輸出。
?
??? 注：PCTFREE與PCTUSED默認(rèn)分別是10與40，其配置與系統(tǒng)的優(yōu)化有關(guān)，要慎重使用，PCTFREE+PCTUSED不要大于等于100，否則將導(dǎo)致塊不斷的在FREELIST移上移下，嚴(yán)重影響性能。若兩者之和低于100，則空間的利用與系統(tǒng)的I/O之間的最佳平衡點(diǎn)是：pctfree與pctused之和等于100%減去一行的大小占塊空間大小的百分比。例如，如果塊大小為2048字節(jié)，則它需要100個(gè)字節(jié)的開銷，而行大小是390字節(jié)（為可用塊的20%）。為了充分利用空間，pctfree與pctused之和最好為80%。
?
?
二、關(guān)于邏輯結(jié)構(gòu)的操作
?
??? 1、查詢表空間的文件的對應(yīng)物理文件：
??? SQL> select * from dba_data_files;
?
??? 2、查詢表空間的空閑信息：
??? SQL> select * from dba_free_space;
?
??? 3、將表空間設(shè)置為離線(offline)狀態(tài)：
??????? 設(shè)置成離線的狀態(tài)，則所有對該表空間內(nèi)對象的修改將無法同步到數(shù)據(jù)文件中
??? SQL> alter tablespace *** offline;
?
??? 4、限制某個(gè)用戶能夠使用的表空間的大小：
??? SQL>alter user *** quota ***M on ***
???
??? 5、指定用戶的臨時(shí)表空間：
??? SQL>CREATE USER *** IDENTIFIED BY **** TEMPORARY TABLESPACE temp;
?
??? 6、查詢用戶的區(qū)間信息：
??? SQL> select * from user_extents;
?
??? 7、查詢索引信息：
??? SQL> select * from user_indexes;
?
??? 8、查看表空間的具體信息：
??? SQL> select * from dba_tablespaces;
??????? BLOCK_SIZE ：數(shù)據(jù)塊的大小
??????? INITIAL_EXTENT：初始化時(shí)分配的區(qū)的大小，其值為BLOCK_SIZE的整數(shù)倍
??????? NEXT_EXTENT：當(dāng)初始化時(shí)分配的區(qū)不夠時(shí)，下一次擴(kuò)展的區(qū)的大小
??????? MIN_EXTENTS：區(qū)大小的下限
??????? MAX_EXTENTS：區(qū)大小的上限
?
??? 9、查看模式對象(Schema Object)：
??? SQL> select * from dba_source;
??????? Oracle模式對象包括了表、索引、約束、過程、函數(shù)、觸發(fā)器等等應(yīng)用結(jié)構(gòu)。
?
??? 10、與表空間有關(guān)的數(shù)據(jù)字典匯總：
??????? dba_tablespaces----------------TableSpace表
??????? dba_users----------------------User表
??????? dba_ts_quotas------------------TableSpace配額表
??????? dba_data_files-----------------TableSpace對應(yīng)File表，描述了全部數(shù)據(jù)文件的名稱、文件標(biāo)識、大小、對應(yīng)的表空間信息
??????? dba_extents--------------------Extent表
??????? dba_free_space-----------------TableSpace剩余空間表
??????? dba_segments-------------------Segment表
??????? dba_temp_files-----------------dba_data_files子表
??????? dba_undo_extents---------------dba_extents子表
??????? dba_rollback_segs--------------dba_segments子表
??????? v$backup_datafile--------------此視圖通常用來獲取每個(gè)數(shù)據(jù)文件中非空白數(shù)據(jù)塊的數(shù)量，從而幫助你創(chuàng)建出大小基本相等的備份集。
??????? v$database_block_corruption----查詢壞塊的視圖
??????? v$datafile---------------------描述了數(shù)據(jù)文件的同步信息
??????? v$datafile_copy----------------包含控制文件中數(shù)據(jù)庫文件副本的信息。
??????? v$datafile_header--------------顯示數(shù)據(jù)文件頭部的數(shù)據(jù)文件信息
??????? v$rollstat---------------------本視圖自啟動即保持并記錄各回滾段統(tǒng)計(jì)項(xiàng)。
??????? v$segment_statistics-----------實(shí)時(shí)監(jiān)測段級(segment-level)統(tǒng)計(jì)項(xiàng)
??????? v$undostat---------------------本視圖監(jiān)控當(dāng)前實(shí)例中undo 空間以及事務(wù)如何運(yùn)行
?
??? 11、創(chuàng)建一個(gè)表空間：
??? SQL> create tablespace *** nologging
????? 2? datafile 'D:\oracle\product\10.2.0\oradata\***\***01.ora' size 50m blocksize 8192
????? 3? extent management local uniform size 256k
????? 4? segment space management auto;
?
??? SQL> create temporary tablespace temp
????? 2? tempfile 'D:\ oracle\product\10.2.0\oradata\***\temp01.ora'
????? 3? size 500m autoextend off
????? 4? extent management local uniform size 512k;
?
??? 12、表空間的讀寫控制：
??? SQL>alter tablespace *** read only;
??? SQL>alter tablespace *** read write;
?
??? 13、刪除表空間：
??? SQL>drop tablespace *** including contents and datafiles;
??????? 包含空間內(nèi)的目錄內(nèi)容和數(shù)據(jù)文件
?
??? 14、表空間改名：
??? SQL>alter tablespace users rename to newusers;
?
?
??? 15、表空間組：
?
??? (一)在Oracle 10g中增加了一個(gè)表空間組的概念，通過使用表空間組用戶可以使用一個(gè)以上的表空間存儲臨時(shí)段。表空間組是在第一個(gè)表空間被指定給該組時(shí)，由系統(tǒng)自動隱式創(chuàng)建的。例如：
??? SQL> alter tablespace temp tablespace group temp_ts_group;
?
??? (二)添加一個(gè)新表空間到該表空間組：
??? SQL> create temporary tablespace temp2
????? 2? tempfile 'D:\oracle\product\10.2.0\oradata\test\temp201.dbf ' size 20m
????? 3? tablespace group temp_ts_group;
?
??? (三)可通過以下SQL查找表空間組的情況：
??? SQL> select * from dba_tablespace_groups;
?
??? (四)將整個(gè)表空間組給用戶作為臨時(shí)表空間：
??? SQL> alter user scott temporary tablespace temp_ts_group;
??? SQL> alter database default temporary tablespace temp_ts_group; --設(shè)置成默認(rèn)
?
??? (五)將表空間移出表空間組：
??? SQL> alter tablespace temp2 tablespace group; --后面不寫
?
??? 16、表空間相關(guān)查詢：
?
??? (一)查詢數(shù)據(jù)文件，表空間名以及大小
??? select
file_name,tablespace_name,round(bytes/
1024000
) MB
????? from
dba_data_files
???? order
by
file_name;
?
??? (二)查詢表空間、表空間的文件、分配的空間、空閑空間以及下一個(gè)空閑分區(qū)
??? 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_free
????? from
sys.dba_free_space fs,sys.dba_data_files ddf
???? where
ddf.tablespace_name=fs.tablespace_name
???? group
by
ddf.tablespace_name,ddf.file_name,ddf.bytes/
1024
???? order
by
ddf.tablespace_name,ddf.file_name;
?
??? (三)表空間數(shù)據(jù)文件的實(shí)際大小（與磁盤中大小不同）
??? select
substr(df.file_name,
1
,
70
) filename,
max
(de.block_id*
?????????? (de.bytes/de.blocks)+de.bytes)/
1024
min_size
????? from
dba_extents de,dba_data_files df
???? where
de.file_id=df.file_id
???? group
by
df.file_name;
?
?