2016年數(shù)據(jù)庫(kù)設(shè)計(jì)規(guī)范(初稿)v10

數(shù)據(jù)庫(kù)設(shè)計(jì)文檔

李偉

2010-12-25

目錄

1.數(shù)據(jù)庫(kù)物理設(shè)計(jì)原則........................................................5

1.1.數(shù)據(jù)庫(kù)環(huán)境配置原則....................................................5

1.1.1.操作系統(tǒng)環(huán)境....................................................5

1.1.2.內(nèi)存要求........................................................5

1.1.3.交換區(qū)設(shè)計(jì)......................................................5

1.1.4.其他............................................................6

1.2.數(shù)據(jù)庫(kù)設(shè)計(jì)原則........................................................6

1.2.1.數(shù)據(jù)庫(kù)SID......................................................................................................................................6

1.2.2.數(shù)據(jù)庫(kù)全局名....................................................6

1.2.3.數(shù)據(jù)庫(kù)類型選擇..................................................6

1.2.4.數(shù)據(jù)庫(kù)連接類型選擇..............................................7

1.2.5.數(shù)據(jù)庫(kù)SGA配置.................................................7

1.2.6.數(shù)據(jù)庫(kù)字符集選擇................................................8

1.2.7.數(shù)據(jù)庫(kù)其他參數(shù)配置..............................................9

1.2.8.數(shù)據(jù)庫(kù)控制文件配置..............................................9

1.2.9.數(shù)據(jù)庫(kù)日志文件配置.............................................10

1.2.10.數(shù)據(jù)庫(kù)回滾段配置...............................................10

1.2.11.數(shù)據(jù)庫(kù)臨時(shí)段表空間配置........................................11

1.2.12.數(shù)據(jù)庫(kù)系統(tǒng)表空間配置..........................................11

1.3.數(shù)據(jù)庫(kù)表空間設(shè)計(jì)原則.................................................11

1.3.1.表空間大小定義原則.............................................11

1.3.2.表空間擴(kuò)展性設(shè)計(jì)原則...........................................12

1.4.裸設(shè)備的使用.........................................................12

2.數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)原則.......................................................13

2.1.命名規(guī)范.............................................................13

2.1.1.表屬性規(guī)范.....................................................13

2.1.2.索引...........................................................14

2.1.3.視圖...........................................................15

2.1.4.實(shí)體化視圖.....................................................15

2.1.5.存儲(chǔ)過(guò)程.......................................................15

2.1.6.觸發(fā)器.........................................................15

2.1.7.函數(shù)............................................................16

2.1.8.數(shù)據(jù)包.........................................................16

2.1.9.序歹IJ..................................................................................................................................................16

2.1.10.表空間.........................................................16

2.1.11.數(shù)據(jù)文件.......................................................16

2.1.12.普通變量.......................................................16

2.1.13.游標(biāo)變量.......................................................17

2.1.14.記錄型變量.....................................................17

2.1.15.表類型變量.....................................................17

2.1.16.數(shù)據(jù)庫(kù)鏈.......................................................17

2.2.命名..................................................................17

2.2.1.語(yǔ)言...........................................................17

2.2.2.大小寫(xiě).........................................................18

2.2.3.單詞分隔.......................................................18

2.2.4.保留字.........................................................18

2.2.5.命名長(zhǎng)度.......................................................18

2.2.6.字段名稱.......................................................18

2.3.數(shù)據(jù)類型.............................................................18

2.3.1.字符型.........................................................18

2.3.2.數(shù)字型.........................................................19

2.3.3.日期和時(shí)間.....................................................19

2.3.4.大字段.........................................................19

2.3.5.唯一鍵.........................................................19

2.4.設(shè)計(jì)..................................................................20

2.4.1,范式...........................................................20

2.4.2.表設(shè)計(jì).........................................................20

2.4.3.索引設(shè)計(jì).......................................................23

2.4.4.視圖設(shè)計(jì).......................................................24

2.4.5.包設(shè)計(jì).........................................................24

2.4.6.安全性設(shè)計(jì).....................................................25

2.5.SQL編寫(xiě).............................................................26

2.5.1,字符類型數(shù)據(jù)...................................................26

2.5.2.復(fù)雜sql.................................................................................................................27

2.5.3.高效性.........................................................27

2.5.4.健壯性.........................................................28

2.5.5.安全性.........................................................29

2.5.6.完整性.........................................................30

3.備份恢復(fù)設(shè)計(jì)原則...........................................................30

3.1.數(shù)據(jù)庫(kù)exp/imp備份恢復(fù)...............................................30

3.1.1,數(shù)據(jù)庫(kù)級(jí)備份原則...............................................30

3.1.2.用戶級(jí)備份原則.................................................30

3.1.3.表級(jí)備份原則...................................................31

3.2.數(shù)據(jù)庫(kù)冷備份原則.....................................................31

3.3.Rman備份恢復(fù)原則...................................................31

3.3.1.Catalog數(shù)據(jù)庫(kù)..................................................31

3.3.2.ArchiveLog...........................................................................................................33

3.3.3,全備份策略.....................................................33

3.3.4.增量備份策略...................................................33

3.3.5.恢復(fù)原則.......................................................33

3.4.備用數(shù)據(jù)庫(kù)原則.......................................................34

3.5.一些小經(jīng)驗(yàn)............................................................34

3.6.系統(tǒng)調(diào)優(yōu)知識(shí).........................................................35

3.6.1.生成狀態(tài)報(bào)表（statspack的使用）................................35

3.6.2.sql追蹤........................................................36

3.6.3.內(nèi)存調(diào)整........................................................37

3.6.4.排序的優(yōu)化.....................................................40

3.6.5.統(tǒng)計(jì)信息.......................................................41

4.設(shè)計(jì)工具....................................................................42

1.數(shù)據(jù)庫(kù)物理設(shè)計(jì)原則

1.1.數(shù)據(jù)庫(kù)環(huán)境配置原則

1.1.1.操作系統(tǒng)環(huán)境

對(duì)于中小型數(shù)據(jù)庫(kù)系統(tǒng)，采用linux操作系統(tǒng)比較合適，對(duì)于數(shù)

據(jù)庫(kù)冗余要求負(fù)載均衡能力要求較高的系統(tǒng)，可以采用OraclelOgR^C

的集群數(shù)據(jù)庫(kù)的方法，集群節(jié)點(diǎn)數(shù)范圍在2-64個(gè)。對(duì)于大型數(shù)據(jù)庫(kù)

系統(tǒng)，可以采用SunSolarisSIW：64位小型機(jī)系統(tǒng)或HP9000系

列小型機(jī)系統(tǒng)。由適合只讀操作的數(shù)據(jù)庫(kù)，R&D1適合CEIP數(shù)據(jù)

庫(kù)。

1.1.2.內(nèi)存要求

對(duì)于linux操作系統(tǒng)下的數(shù)據(jù)庫(kù)，由于在正常情況下Oracle對(duì)

9c2的管理能力不超過(guò)1.7G所以總的物理內(nèi)存在4G以下。通的

大小為物理內(nèi)存的5%75%對(duì)于64位的小型系統(tǒng)，Oracle數(shù)據(jù)庫(kù)

對(duì)9c2的管理超過(guò)2G的限制，SGA設(shè)計(jì)在一個(gè)合適的范圍內(nèi)：物理

內(nèi)存的50^70%當(dāng)SCA過(guò)大的時(shí)候會(huì)導(dǎo)致內(nèi)存分頁(yè)，影響系統(tǒng)性

能。

1.1.3,交換區(qū)設(shè)計(jì)

當(dāng)物理內(nèi)存在2G以下的情況下，交換分區(qū)swap為物理內(nèi)存的3

倍，當(dāng)物理內(nèi)存＞2G的情況下，swap大小為物理內(nèi)存的1-2倍。

1.1.4.其他

其他環(huán)境變量參考Oracle相關(guān)的安裝文檔和隨機(jī)文檔。

1.2.數(shù)據(jù)庫(kù)設(shè)計(jì)原則

1.2.1,數(shù)據(jù)庫(kù)SID

數(shù)據(jù)庫(kù)SID是唯一標(biāo)志數(shù)據(jù)庫(kù)的符號(hào)，命名長(zhǎng)度不能超過(guò)5個(gè)字

符。對(duì)于單節(jié)點(diǎn)數(shù)據(jù)庫(kù)，以字符開(kāi)頭的5個(gè)長(zhǎng)度以內(nèi)字串作為SID的

命名。對(duì)于集群數(shù)據(jù)庫(kù),當(dāng)命名SID后,各節(jié)點(diǎn)SID自動(dòng)命名為S6n,

其中nn為節(jié)點(diǎn)號(hào)：1,2,-??,64例如rackrac么rac24

1.2.2.數(shù)據(jù)庫(kù)全局名

DatabaseConfigurationAssisUnt,Step4ot&:DatabaseIdentification

Specifythefollowingdatabaseinformation

AnOracleSIdatabaseHuniquelyidentifiedbyaGlobalDatabaseNam?,typkallyofthe

form"name.domain".

GlobalDatabaseName[

AdatabaseisreferencedbyanOracledtinstanceoneachclusterdatabasenode.Each

instanceisuniquelyidentifiedbyanOracleSystemidentifier(SID).Foreachcluster

databasein$tan<e.thesiDkcomprisedofacommonprefixforthedatabaseanda

numberforeachinstancethatisautomaticallygenerated.AsuggestedSIDprefixhas

beenenteredforyouwhichyoucanacceptorchangetoavalueyouprefer

SIDPrefix:「才

數(shù)據(jù)庫(kù)全局名稱：

<sid>.domain

1.2.3.數(shù)據(jù)庫(kù)類型選擇

對(duì)于海量數(shù)據(jù)庫(kù)系統(tǒng)，采用datawarehouse的類型。對(duì)于小型

數(shù)據(jù)庫(kù)或CLIP類型的數(shù)據(jù)庫(kù)，采用TransactionProcessing類型。

1.2.4.數(shù)據(jù)庫(kù)連接類型選擇

Oracle數(shù)據(jù)庫(kù)有專用服務(wù)器連接類型和多線程服務(wù)器MTS連接

類型。對(duì)于批處理服務(wù)，需要專用服務(wù)器連接方式，而對(duì)于CIJP服

務(wù)則MTS的連接方式比較合適。由于采用MIS后，可以通過(guò)配置網(wǎng)絡(luò)

服務(wù)實(shí)現(xiàn)某些特定批處理服務(wù)采用專用服務(wù)器連接方式，所以數(shù)據(jù)庫(kù)

設(shè)計(jì)時(shí)一般采用MTS類型。

1.2.5.數(shù)據(jù)庫(kù)SGA配置

數(shù)據(jù)庫(kù)9cx可以采用手工配置或按物理內(nèi)存比例配置，在數(shù)據(jù)庫(kù)

初始設(shè)計(jì)階段采用按比例配置方式，在實(shí)際應(yīng)用中按系統(tǒng)調(diào)優(yōu)方式修

改SQ\

1.2.6.數(shù)據(jù)庫(kù)字符集選擇

為了使數(shù)據(jù)庫(kù)能夠正確支持多國(guó)語(yǔ)言，必須配置合適的數(shù)據(jù)庫(kù)字

符集，采用UIF8字符集。

注意：如果沒(méi)有大對(duì)象，在使用過(guò)程中進(jìn)行語(yǔ)言轉(zhuǎn)換沒(méi)有什么影

響，具體過(guò)程如下（切記設(shè)定的字符集必須是ORAZLE支持，不然不

能start）

SQL>shutdownimmediate;

SQL>startupmount;

SQL>altersystemenablerestrictedsession;

SQL>altersystemsetjob_queue_processes=0;

SQL>alterdatabaseopen;

SQL>alterdatabasecharactersetinternal_use

we8iso8859pl;

SQL>shutdownimmediate;

SQL>startup

1.2.7.數(shù)據(jù)庫(kù)其他參數(shù)配置

.DB.FILES

CLJiles是數(shù)據(jù)庫(kù)能夠同時(shí)打開(kāi)的文件數(shù)量，默認(rèn)值是200個(gè)。

當(dāng)數(shù)據(jù)庫(kù)規(guī)劃時(shí)文件數(shù)量FILES接近或超過(guò)200個(gè)時(shí)候，按以下估計(jì)

值配置：

DB_FILES=FILES*1.5

.Db_block_size

一個(gè)extent要是5個(gè)blocks的倍數(shù)為好，如：一個(gè)blocks是

4096字節(jié)，那一個(gè)extent就是2M4M或8V［為好。Eb_blocl^_size

是數(shù)據(jù)庫(kù)最小物理單元，一旦數(shù)據(jù)庫(kù)創(chuàng)建完成，該參數(shù)無(wú)法修改，

db_b1oc口ize按以下規(guī)則調(diào)整：

數(shù)據(jù)倉(cāng)庫(kù)類型：dSlocOize盡可能大，采用8192或16384

CLIP類型：d"）lockjize用比較小的取值范圍：2048或

4096

Blocks推薦是系統(tǒng)操作的塊倍數(shù)（裸設(shè)備塊大小是512字節(jié)，

NIFS是做使用8K的方式在大部分系統(tǒng)上通用）。

1.2.8.數(shù)據(jù)庫(kù)控制文件配置

1.2.8.1.控制文件鏡象

多個(gè)控制文件存放在不同的物理位置。

1.2.8.2.控制文件配置

控制文件中參數(shù)設(shè)置，最大的數(shù)據(jù)文件數(shù)量不能小于數(shù)據(jù)庫(kù)參數(shù)

db_Jiles,

1.2.9.數(shù)據(jù)庫(kù)日志文件配置

1.2.9.1.日志文件大小

日志文件的大小由數(shù)據(jù)庫(kù)事務(wù)處理量決定，在設(shè)計(jì)過(guò)程中，確保

每20分鐘切換一個(gè)日志文件。所以對(duì)于批處理系統(tǒng)，日志文件大小

為幾百M(fèi)到幾Gfi勺大小。對(duì)于（XIP系統(tǒng)，日志文件大小為幾百W

內(nèi)。

1.2.9.2.日志文件組數(shù)量

對(duì)于批處理系統(tǒng)，日志文件組為計(jì)10組；對(duì)于CLIP系統(tǒng)，日

志文件組為A5組，每組日志大小保持一致；對(duì)于集群數(shù)據(jù)庫(kù)系統(tǒng),

每節(jié)點(diǎn)有各自獨(dú)立的日志組。

1.2.9.3.日志成員數(shù)量

為了確保日志能夠鏡象作用，每日志組的成員為2個(gè)。

1.2.10.數(shù)據(jù)庫(kù)回滾段配置

在Oracle9i數(shù)據(jù)庫(kù)中，設(shè)計(jì)Lhdo表空間取代以前版本的回滾段

表空間。

Undo表空間大小的設(shè)計(jì)規(guī)范由以下公式計(jì)算：

Uidospace=UR.*EPS^dbb1ock_size-F

IR表示在undo中保持的最長(zhǎng)時(shí)間數(shù)（秒），由數(shù)據(jù)庫(kù)參數(shù)

IWQ^EIENTICNf直決定。

UPS表示在undo中，每秒產(chǎn)生的數(shù)據(jù)庫(kù)塊數(shù)量。

例如：在數(shù)據(jù)庫(kù)中保留2小時(shí)的回退數(shù)據(jù)，假定每小時(shí)產(chǎn)生200

個(gè)數(shù)據(jù)庫(kù)塊。則Undospace=2*3600*200*4K=5.8G

1.2.11.數(shù)據(jù)庫(kù)臨時(shí)段表空間配置

數(shù)據(jù)庫(kù)臨時(shí)段表空間根據(jù)實(shí)際生產(chǎn)環(huán)境情況調(diào)整其大小，表空間

屬性為自動(dòng)擴(kuò)展。

1.2.12.數(shù)據(jù)庫(kù)系統(tǒng)表空間配置

系統(tǒng)表空間大小1G左右，除了存放數(shù)據(jù)庫(kù)數(shù)據(jù)字典的數(shù)據(jù)外，

其他數(shù)據(jù)不得存儲(chǔ)在系統(tǒng)表空間。

1.3.數(shù)據(jù)庫(kù)表空間設(shè)計(jì)原則

1.3.1.表空間大小定義原則

當(dāng)表空間大小小于操作系統(tǒng)對(duì)最大文件限制時(shí)，表空間由一個(gè)文

件組成。如果表空間大小大于操作系統(tǒng)對(duì)最大文件限制時(shí)，該表空間

由多個(gè)數(shù)據(jù)文件組成，表空間的總大小為估算為：

Tablespace+sum數(shù)據(jù)段琮引段）*15期

1.3.2,表空間擴(kuò)展性設(shè)計(jì)原則

表空間數(shù)據(jù)文件采用自動(dòng)擴(kuò)展的方式，擴(kuò)展容量快大小按2的整

數(shù)倍（IM3W縱1而33v[6網(wǎng)進(jìn)行擴(kuò)展，創(chuàng)建表空間時(shí)

盡量采用nologing選項(xiàng)。表空間的最大限制一般采用unlimited,

除非確切知道表空間數(shù)據(jù)文件的最大使用范圍。（一般windows3

2位系統(tǒng)的文件最大2G,64位的unix系統(tǒng)系統(tǒng)文件最大128

G但也要注意文件格式設(shè)定的文件大?。?，建議最大為2G表空間采

用local管理方式，例如：

CREATETABLESPACETBSUSERINFO

DATAFILE

Voradata/tbs_userinfo.dbf'

SIZE8M

REUSE

AUTOEXTENDON

NEXT2M

MAXSIZEUNLIMITED

NOLOGGING

EXTENTMANAGEMENT

LOCAL

AUTOALLOCATE

SEGMENTSPACEMANAGEMENTAUTO;

1.4.裸設(shè)備的使用

一個(gè)scsi設(shè)備可以14個(gè)分區(qū)，Unix操作系統(tǒng)256個(gè)分區(qū)，性能

比文件系統(tǒng)方式高15%左右，空間大于要小于實(shí)際分區(qū)大小減兩個(gè)

CRAZLE的數(shù)據(jù)塊），比如10CM大于為100000K推薦在unix使用

軟連接（1⑥方式把裸設(shè)備形成文件，用加入表空間時(shí)加resue選項(xiàng),

當(dāng)然也可只接把設(shè)備加入表空間，移動(dòng)裸設(shè)備使用dd命令。

對(duì)于windows平臺(tái)，oracle提供軟連接工具，實(shí)現(xiàn)裸設(shè)備的使

用，計(jì)算一條記錄的長(zhǎng)度。

2.數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)原則

2.1.命名規(guī)范

2.1.1.表屬性規(guī)范

2.1.1.1.表名

前綴為7b1_。數(shù)據(jù)表名稱必須以有特征含義的單詞或縮寫(xiě)組成,

中間可以用分割，例如：tbl_j)stiiJetailo表名稱不能用雙引

號(hào)包含。

.表分區(qū)名

前綴為Po分區(qū)名必須有特定含義的單詞或字串。

例如：tbl_pstiLWetai1的分區(qū)p2004100101表示該分區(qū)存儲(chǔ)

2004100101時(shí)段的數(shù)據(jù)。

2.1.1.3.字段名

字段名稱必須用字母開(kāi)頭，采用有特征含義的單詞或縮寫(xiě)，不能

用雙引號(hào)包含。

.主鍵名

前綴為&主鍵名稱應(yīng)是前綴倭名附成的字段名。如果復(fù)

合主鍵的構(gòu)成字段較多，則只包含第一個(gè)字段。表名可以去掉前綴。

2.1.1.5.夕卜鍵名

前綴為EECo外鍵名稱應(yīng)是前綴+外鍵表名十主鍵表名+外

鍵表構(gòu)成的字段名。表名可以去掉前綴。

2.1.2.索引

2.1.2.1.普通索引

前綴為K索引名稱應(yīng)是前綴倭名幽成的字段名。如果復(fù)

合索引的構(gòu)成字段較多，則只包含第一個(gè)字段，并添加序號(hào)。表名可

以去掉前綴。

2.1.2.2.主鍵索引

前綴為IDLI心索引名稱應(yīng)是前綴倭名做成的主鍵字段名,

在創(chuàng)建表時(shí)候用usingindex指定主鍵索引屬性。

.唯一索引

前綴為KXLKo索引名稱應(yīng)是前綴倭名附成的字段名。

2.1.2.4.外鍵索引

前綴為IE區(qū)足索引名稱應(yīng)是前綴裱名胸成的外鍵字段名。

2.1.2.5.函數(shù)索引

前綴為ITXJunc^索引名稱應(yīng)是前綴倭名制成的特征表達(dá)

字符。

2.1.2.6.族索引

前綴為nxciu^索引名稱應(yīng)是前綴鍍名胸成的簇字段。

2.1.3.視圖

前綴為按業(yè)務(wù)操作命名視圖。

2.1.4.實(shí)體化視圖

前綴為ML按業(yè)務(wù)操作命名實(shí)體化視圖。

2.1.5.存儲(chǔ)過(guò)程

前綴為Proc_o按業(yè)務(wù)操作命名存儲(chǔ)過(guò)程。

2.1.6.觸發(fā)器

前綴為T(mén)rig_o觸發(fā)器名應(yīng)是前綴+表名+觸發(fā)器名。

2.1.7.函數(shù)

前綴為Funj。按業(yè)務(wù)操作命名函數(shù)。

2.1.8.數(shù)據(jù)包

前綴為Pk-。按業(yè)務(wù)操作集合命名數(shù)據(jù)包。

2.1.9.序列

前綴為Seq_o按業(yè)務(wù)屬性命名。

2.1.10.表空間

.公用表空間

前綴為T(mén)bs_o根據(jù)存儲(chǔ)的特性命名，例如：tbs_paramster。

,專用表空間

lb4表名稱二nn該表空間專門(mén)存儲(chǔ)指定的某一個(gè)表，或某一

表的若干個(gè)分區(qū)的數(shù)據(jù)。

2.1.11.數(shù)據(jù)文件

礁空間名/indbf。nn=1,2,3,4…等。

2.1.12.普通變量

前綴為Va存放字符、數(shù)字、日期型變量。

2.1.13.游標(biāo)變量

前綴為Cu存放游標(biāo)記錄集。

2.1.14.記錄型變量

前綴為Re存放記錄型數(shù)據(jù)。

2.1.15.表類型變量

前綴為T(mén)aj。存放表類型數(shù)據(jù)。

2.1.16.數(shù)據(jù)庫(kù)鏈

前綴為dbl_o表示分布式數(shù)據(jù)庫(kù)外部鏈接關(guān)系。

2.2.命名

2.2.1.語(yǔ)百

命名應(yīng)該使用英文單詞，避免使用拼音，特別不應(yīng)該使用拼音簡(jiǎn)

寫(xiě)。命名不允許使用中文或者特殊字符。

英文單詞使用用對(duì)象本身意義相對(duì)或相近的單詞。選擇最簡(jiǎn)單或

最通用的單詞。不能使用毫不相干的單詞來(lái)命名。

當(dāng)一個(gè)單詞不能表達(dá)對(duì)象含義時(shí),用詞組組合，如果組合太長(zhǎng)時(shí),

采用用簡(jiǎn)或縮寫(xiě)，縮寫(xiě)要基本能表達(dá)原單詞的意義。

當(dāng)出現(xiàn)對(duì)象名重名時(shí)，是不同類型對(duì)象時(shí)，加類型前綴或后綴以

示區(qū)別。

2.2.2.大小寫(xiě)

名稱一律大寫(xiě)，以方便不同數(shù)據(jù)庫(kù)移植，以及避免程序調(diào)用問(wèn)題。

2.2.3.單詞分隔

命名的各單詞之間可以使用下劃線進(jìn)行分隔。

2.2.4.保留字

命名不允許使用4保留字。

2.2.5.命名長(zhǎng)度

表名、字段名、視圖名長(zhǎng)度應(yīng)限制在20個(gè)字符內(nèi)含前綴）。

2.2.6.字段名稱

同一個(gè)字段名在一個(gè)數(shù)據(jù)庫(kù)中只能代表一個(gè)意思。比如

telephone在一個(gè)表中代表“電話號(hào)碼”的意思，在另外一個(gè)表中就

不能代表“手機(jī)號(hào)碼”的意思。

不同的表用于相同內(nèi)容的字段應(yīng)該采用同樣的名稱，字段類型定

義。

2.3.數(shù)據(jù)類型

2.3.1.字符型

固定長(zhǎng)度的字串類型采用char,長(zhǎng)度不固定的字串類型采用

varcharo避免在長(zhǎng)度不固定的情況下采用char類型。如果在數(shù)據(jù)遷

移等出現(xiàn)以上情況，則必須使用trimO函數(shù)截去字串后的空格。

2.3.2.數(shù)字型

數(shù)字型字段盡量采用number類型，要注意精度。

2.3.3.日期和時(shí)間

2.3.3.1.系統(tǒng)時(shí)間

由數(shù)據(jù)庫(kù)產(chǎn)生的系統(tǒng)時(shí)間首選數(shù)據(jù)庫(kù)的日期型，如的E類型。

.外部時(shí)間

由數(shù)據(jù)導(dǎo)入或外部應(yīng)用程序產(chǎn)生的日期時(shí)間類型采用varchar

類型，數(shù)據(jù)格式采用：YYYWCEffi24V[ISS

2.3.4.大字段

如無(wú)特別需要，避免使用大字段(blob,clob,longtext,image

等)。

2.3.5,唯一鍵

對(duì)于數(shù)字型唯一鍵值，盡可能用系列sequence產(chǎn)生。

2.4.設(shè)計(jì)

2.4.1.范式

如無(wú)性能上的必須原因，應(yīng)該使用關(guān)系數(shù)據(jù)庫(kù)理論，達(dá)到較高的

范式，避免數(shù)據(jù)冗余，但是如果在數(shù)據(jù)量上與性能上無(wú)特別要求，考

慮到實(shí)現(xiàn)的方便性可以有適當(dāng)?shù)臄?shù)據(jù)冗余，但基本上要達(dá)到3NF.如

非確實(shí)必要，避免一個(gè)字段中存儲(chǔ)多個(gè)標(biāo)志的做法。如11101表示5

個(gè)標(biāo)志的一種取值。這往往是增加復(fù)雜度，降低性能的地方。

2.4.2,表設(shè)計(jì)

2.4.2.1.邏輯段設(shè)計(jì)原則

.1.Tablespace

每個(gè)表在創(chuàng)建時(shí)候，必須指定所在的表空間，不要采用默認(rèn)表空

間以防止表建立在系統(tǒng)表空間上導(dǎo)致性能問(wèn)題。對(duì)于事務(wù)比較繁忙的

數(shù)據(jù)表，必須存放在該表的專用表空間中。

.2.Pctused

默認(rèn)pctused導(dǎo)致數(shù)據(jù)庫(kù)物理空間利用率非常低4疇右；對(duì)于

update比較少或updat壞導(dǎo)致行增大的表，pctuse阿設(shè)置在60—85

之間；對(duì)于update能夠?qū)е滦性龃蟮谋?，update設(shè)置在40—70之

間。

3.Initrans

對(duì)于需要并行查詢或者在R9數(shù)據(jù)庫(kù)中需要并行處理的表，

initrans設(shè)置為2的倍數(shù)，否則，不設(shè)該值。

.4.Storage

1)Initial

盡量減少表數(shù)據(jù)段的extents數(shù)量，initial的大小盡量接近數(shù)

據(jù)段的大小6做128K…，1M刎鋼時(shí)1面，…，等按2

的倍數(shù)進(jìn)行圓整。例如表或分區(qū)數(shù)據(jù)段大小為2綱則initial取32M

2)Next

表或分區(qū)擴(kuò)展extents的大小，按上述方法進(jìn)行圓整。當(dāng)表或分

區(qū)數(shù)據(jù)段無(wú)法按Initial接近值進(jìn)行圓整的情況下，其大小可以按

InitialMext進(jìn)行圓整。此時(shí)，必須設(shè)置Minextents=2=例如：表

或分區(qū)數(shù)據(jù)段大小為15CM,則Initial=128V[;Next=62M,

Minextent5=2^

3)Minextents

該參數(shù)表示表創(chuàng)建時(shí)候Extents的初始數(shù)量，一般取一2

4)Pctincrease

表示每個(gè)擴(kuò)展Extents的增長(zhǎng)率，設(shè)置pctincrease=O能夠獲得

較好的存儲(chǔ)性能。

2.4.2.Z.特殊表設(shè)計(jì)原則

.1.分區(qū)表

對(duì)于數(shù)據(jù)量比較大的表，根據(jù)表數(shù)據(jù)的屬性進(jìn)行分區(qū)，以得到較

好的性能。如果表按某些字段進(jìn)行增長(zhǎng)，則采用按字段值范圍進(jìn)行范

圍分區(qū);如果表按某個(gè)字段的幾個(gè)關(guān)鍵值進(jìn)行分布，則采用列表分區(qū);

對(duì)于靜態(tài)表，則采用hash分區(qū)或列表分區(qū)；在范圍分區(qū)中，如果數(shù)

據(jù)按某關(guān)鍵字段均衡分布，則采用子分區(qū)的復(fù)合分區(qū)方法。

2.4.Z.2.2.聚族表

如果某幾個(gè)靜態(tài)表關(guān)系比較密切，則可以采用聚族表的方法。

2A2.3.完整性設(shè)計(jì)原則

.1.主鍵約束

關(guān)聯(lián)表的父表要求有主健，主健字段或組合字段必須滿足非空屬

性和唯一性要求。對(duì)于數(shù)據(jù)量比較大的父表，要求指定索引段。

.夕卜鍵關(guān)聯(lián)

對(duì)于關(guān)聯(lián)兩個(gè)表的字段，一般應(yīng)該分別建立主鍵、外鍵。實(shí)際是

否建立外鍵，根據(jù)對(duì)數(shù)據(jù)完整性的要求決定。為了提高性能，對(duì)于數(shù)

據(jù)量比較大的標(biāo)要求對(duì)外健建立索引。對(duì)于有要求級(jí)聯(lián)刪除屬性的外

鍵，必須指定ondeletecascade。

.NULL值

對(duì)于字段能否null,應(yīng)該在sql建表腳本中明確指明，不應(yīng)使

用缺省。由于NIL值在參加任何運(yùn)算中，結(jié)果均為NULL所以在應(yīng)

用程序中必須利用nvl0函數(shù)把可能為NIL值得字段或變量轉(zhuǎn)換為

非NIL的默認(rèn)值。例如：NVL(sale,Q。

.Check條件

對(duì)于字段有檢查性約束，要求指定check規(guī)則。

2.4.23.5.觸發(fā)器

觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，通過(guò)數(shù)據(jù)表的IM操作而觸發(fā)執(zhí)

行，起作用是為確保數(shù)據(jù)的完整性和一致性不被破壞而創(chuàng)建，實(shí)現(xiàn)數(shù)

據(jù)的完整約束。

觸發(fā)器的before或after事務(wù)屬性的選擇時(shí)候，對(duì)表操作的事

務(wù)屬性必須與應(yīng)用程序事務(wù)屬性保持一致，以避免死鎖發(fā)生。在大型

導(dǎo)入表中，盡量避免使用觸發(fā)器。

2.4.Z.4.注釋

表、字段等應(yīng)該有中文名稱注釋，以及需要說(shuō)明的內(nèi)容。

2.4.3,索引設(shè)計(jì)

對(duì)于查詢中需要作為查詢條件的字段，可以考慮建立索引。最終

根據(jù)性能的需要決定是否建立索引。對(duì)于復(fù)合索引，索引字段順序比

較關(guān)鍵，把查詢頻率比較高的字段排在索引組合的最前面。在分區(qū)表

中，盡量采用local分區(qū)索引以方便分區(qū)維護(hù)。

除非時(shí)分區(qū)local索引，否則在創(chuàng)建索引段時(shí)候必須指定指定索

引段的tablespace.storage屬性,具體參考2.4.21內(nèi)容。

2.4.4.視圖設(shè)計(jì)

視圖是虛擬的數(shù)據(jù)庫(kù)表，在使用時(shí)要遵循以下原則：

1）從一個(gè)或多個(gè)庫(kù)表中查詢部分?jǐn)?shù)據(jù)項(xiàng)；

2）為簡(jiǎn)化查詢，將復(fù)雜的檢索或字查詢通過(guò)視圖實(shí)現(xiàn)；

3）提高數(shù)據(jù)的安全性，只將需要查看的數(shù)據(jù)信息顯示給權(quán)限有

限的人員；

視圖中如果嵌套使用視圖，級(jí)數(shù)不得超過(guò)3級(jí)；

5）由于視圖中只能固定條件或沒(méi)有條件，所以對(duì)于數(shù)據(jù)量較大

或隨時(shí)間的推移逐漸增多的庫(kù)表，不宜使用視圖；可以采用

實(shí)體化視圖代替；

⑥除特殊需要，避免類似Select*frcm[TableNaire]而沒(méi)有

檢索條件的視圖；

7）視圖中盡量避免出現(xiàn)數(shù)據(jù)排序的玨語(yǔ)句。

2.4.5.包設(shè)計(jì)

存儲(chǔ)過(guò)程、函數(shù)、外部游標(biāo)必須在指定的數(shù)據(jù)包對(duì)象聆WGE中

實(shí)現(xiàn)。存儲(chǔ)過(guò)程、函數(shù)的建立如同其它語(yǔ)言形式的編程過(guò)程，適合采

用模塊化設(shè)計(jì)方法；當(dāng)具體算法改變時(shí)，只需要修改需要存儲(chǔ)過(guò)程即

可，不需要修改其它語(yǔ)言的源程序。當(dāng)和數(shù)據(jù)庫(kù)頻繁交換數(shù)據(jù)是通過(guò)

存儲(chǔ)過(guò)程可以提高運(yùn)行速度，由于只有被授權(quán)的用戶才能執(zhí)行存儲(chǔ)過(guò)

程，所以存儲(chǔ)過(guò)程有利于提高系統(tǒng)的安全性。

存儲(chǔ)過(guò)程、函數(shù)必須檢索數(shù)據(jù)庫(kù)表記錄或數(shù)據(jù)庫(kù)其他對(duì)象，甚至

修改（執(zhí)行InsertDslete.Iodate.DropCreate等操作）數(shù)據(jù)

庫(kù)信息。如果某項(xiàng)功能不需要和數(shù)據(jù)庫(kù)打交道，則不得通過(guò)數(shù)據(jù)庫(kù)存

儲(chǔ)過(guò)程或函數(shù)的方式實(shí)現(xiàn)。在函數(shù)中避免采用DML或DX語(yǔ)句。

在數(shù)據(jù)包采用存儲(chǔ)過(guò)程、函數(shù)重載的方法，簡(jiǎn)化數(shù)據(jù)包設(shè)計(jì)，提

高代碼效率。存儲(chǔ)過(guò)程、函數(shù)必須有相應(yīng)的出錯(cuò)處理功能。

2.4.6.安全性設(shè)計(jì)

2.4.6.1,管理默認(rèn)用戶

在生產(chǎn)環(huán)境中，必須嚴(yán)格管理sys和system用戶，必須修改其

默認(rèn)密碼，禁止用該用戶建立數(shù)據(jù)庫(kù)應(yīng)用對(duì)象。刪除或鎖定數(shù)據(jù)庫(kù)測(cè)

試用戶Scotto

2.4.6.Z.數(shù)據(jù)庫(kù)級(jí)用戶權(quán)限設(shè)計(jì)

必須按照應(yīng)用需求，設(shè)計(jì)不同的用戶訪問(wèn)權(quán)限。包括應(yīng)用系統(tǒng)管

理用戶，普通用戶等，按照業(yè)務(wù)需求建立不同的應(yīng)用角色。

用戶訪問(wèn)另外的用戶對(duì)象時(shí)，應(yīng)該通過(guò)創(chuàng)建同義詞對(duì)象synonym

進(jìn)行訪問(wèn)。

.角色與權(quán)限

確定每個(gè)角色對(duì)數(shù)據(jù)庫(kù)表的操作權(quán)限，如創(chuàng)建、檢索、更新、刪

除等。每個(gè)角色擁有剛好能夠完成任務(wù)的權(quán)限，不多也不少。在應(yīng)用

時(shí)再為用戶分配角色，則每個(gè)用戶的權(quán)限等于他所兼角色的權(quán)限之

和。

2A.6.4.應(yīng)用級(jí)用戶設(shè)計(jì)

應(yīng)用級(jí)的用戶帳號(hào)密碼不能與數(shù)據(jù)庫(kù)相同，防止用戶直接操作數(shù)

據(jù)庫(kù)。用戶只能用帳號(hào)登陸到應(yīng)用軟件，通過(guò)應(yīng)用軟件訪問(wèn)數(shù)據(jù)庫(kù)，

而沒(méi)有其它途徑操作數(shù)據(jù)庫(kù)。

2A.6.5.用戶密碼管理

用戶帳號(hào)的密碼必須進(jìn)行加密處理，確保在任何地方的查詢都不

會(huì)出現(xiàn)密碼的明文。

2.5.SQL編寫(xiě)

2.5.1.字符類型數(shù)據(jù)

處中的字符類型數(shù)據(jù)應(yīng)該統(tǒng)一使用單引號(hào)。特別對(duì)純數(shù)字的字

串，必須用單引號(hào)，否則會(huì)導(dǎo)致內(nèi)部轉(zhuǎn)換而引起性能問(wèn)題或索引失效

問(wèn)題。利用trim。，1。怔r0等函數(shù)格式化匹配條件。

2.5.2.復(fù)雜sql

對(duì)于非常復(fù)雜的sql特別是有多層嵌套，帶子句或相關(guān)查詢的），

應(yīng)該先考慮是否設(shè)計(jì)不當(dāng)引起的。對(duì)于一些復(fù)雜雙可以考慮使用程

序?qū)崿F(xiàn)。

US巡儂刨I(mǎi)S數(shù)據(jù)字典

Garmenton可加注解

2.5.3.高效性

.避免In子句

使用In或notIn子句時(shí)，特別是當(dāng)子句中有多個(gè)值時(shí)，且查

詢數(shù)據(jù)表數(shù)據(jù)較多時(shí)，速度會(huì)明顯下降?？梢圆捎眠B接查詢或外連接

查詢來(lái)提高性能。

Char比varchar查詢時(shí)高效。在進(jìn)行查詢及建立索引時(shí)，char

比varchar的效率要高，當(dāng)然varchar在存儲(chǔ)上比char要好。

2.S.3.2.避免嵌套的Select子句

這個(gè)實(shí)際上是In子句的特例。

,避免使用Select*語(yǔ)句

如果不是必要取出所有數(shù)據(jù)，不要用竦代替，應(yīng)給出字段列表，

注：不含selectcount6。

2.5.3.4.避免不必要的排序

不必要的數(shù)據(jù)排序大大的降低系統(tǒng)性能。

2.5.4.健壯性

.Insert語(yǔ)句

使用Insert語(yǔ)句一定要給出要插入值的字段列表，這樣即使更

改了表結(jié)構(gòu)加了字段也不會(huì)影響現(xiàn)有系統(tǒng)的運(yùn)行。

.Count(*)>Count(id)>count(distinctid)的區(qū)別

Selectcount矽fromtesttab

得到表testtab的記錄數(shù)

selectcount(icj)frantesttab

得到表testtabid字段非空記錄數(shù)

selectcount^distincti(j)fremtesttab

得到表testtabid字段值非相同記錄數(shù)

.Notnull為字段類型性質(zhì)的約束

本約束功能在后期無(wú)語(yǔ)法使期失效，可使用修改字段類型方式。

altertablemodify字段名類型notnul1

altertablemodify字段名類型

2.5.4.4.外鍵值可用null的問(wèn)題

外鍵列如沒(méi)有明確說(shuō)明notnull,可插入null記錄（而null

是在外部表的記錄中沒(méi)有的），如無(wú)可插null記錄的想法，要對(duì)外

鍵字段加notnull約束。

2.5.4.5.序列sequence跳號(hào)的問(wèn)題

sequence因回滾，系統(tǒng)崩潰（使用cache內(nèi)的值將認(rèn)為已用），

多表引用都將使其跳號(hào)，所以不能用于為連續(xù)序號(hào)

ut1_rcwcastto_rcw

.unicn\intersect\minus使用ordeyby的注意事項(xiàng)

以上語(yǔ)句進(jìn)行連表操作，而表同表的字段順序的類型相同但字段

標(biāo)題名可不同，使用ordeyby時(shí)后面如果是字段名，要求所有的表

的字段標(biāo)題名相同，否則用字段的順序號(hào)

selectid,name,yearfromuserl

union

selectno,name,tonumber（null）yearfromuser2

orderby1,name,year

2.5.5.安全性

無(wú)論在使用Select,還是使用破壞力極大的Update和Delete語(yǔ)

句時(shí)，一定要檢查Where條件判斷的完整性，不要在運(yùn)行時(shí)出現(xiàn)數(shù)據(jù)

的重大丟失。如果不確定，最好先用Select語(yǔ)句帶上相同條件來(lái)驗(yàn)

證一下結(jié)果集，來(lái)檢驗(yàn)條件是否正確。

2.5.6.完整性

有依賴關(guān)系的表，例如主外鍵關(guān)系表，在刪除父表時(shí)必須級(jí)聯(lián)刪

除其子表相應(yīng)數(shù)據(jù)，或則按照某種業(yè)務(wù)規(guī)則轉(zhuǎn)移該數(shù)據(jù)。91中表中

字段縮小及變類型，字段為空或表空，varchar和char長(zhǎng)度不變可

任意改，字段名和表名可字段可用ALTERWLEtableSETIHEED

Column）設(shè)定為不可用，注意無(wú)命令再設(shè)為可用。

3.備份恢復(fù)設(shè)計(jì)原則

3.1.數(shù)據(jù)庫(kù)exp/imp備份恢復(fù)

Oracle數(shù)據(jù)庫(kù)的ExpImp提供了數(shù)據(jù)快速的備份和恢復(fù)手段，

提供了數(shù)據(jù)庫(kù)級(jí)、用戶級(jí)和表級(jí)的數(shù)據(jù)備份恢復(fù)方式。這種方法一般

作為數(shù)據(jù)庫(kù)輔助備份手段。

3.1.1.數(shù)據(jù)庫(kù)級(jí)備份原則

在數(shù)據(jù)庫(kù)的數(shù)據(jù)量比較小，或數(shù)據(jù)庫(kù)初始建立的情況下采用。不

適合7*24的在線生產(chǎn)環(huán)境數(shù)據(jù)庫(kù)備份。

3.1.2.用戶級(jí)備份原則

在用戶對(duì)象表數(shù)據(jù)容量比較小、或則用戶對(duì)象初始建立的情況下

使用。

3.1.3.表級(jí)備份原則

主要在以下場(chǎng)合采用的備份方式：參數(shù)表備份、靜態(tài)表備份、分

區(qū)表的分區(qū)備份。

3.2.數(shù)據(jù)庫(kù)冷備份原則

數(shù)據(jù)庫(kù)冷備份必須符合以下原則：

1）數(shù)據(jù)庫(kù)容量比較小。

2）數(shù)據(jù)庫(kù)允許關(guān)閉的情況。

3.3.Rman備份恢復(fù)原則

這種方式適用于7*24環(huán)境下的聯(lián)機(jī)熱備份情形。

3.3.1.Catalog數(shù)據(jù)庫(kù)

單獨(dú)建立備份恢復(fù)用的數(shù)據(jù)庫(kù)實(shí)例，盡可能與生產(chǎn)環(huán)境的數(shù)據(jù)庫(kù)

分開(kāi)，確保catalog與生產(chǎn)數(shù)據(jù)庫(kù)的網(wǎng)絡(luò)連接良好。在91系統(tǒng)使用

良好的備份策略以可，支持完全使用控制文件保存catalog信息，備

份策略如下：

backupspfileformat

'/data/backup/%d_SPFILE_%T_%s_%p.bak?;

sql“altersystemarchivelogcurrent”;

backuparchivelogallformat

J/data/backup/%d_ARC_%T_%s%p.bak，deleteall

input;

backupcurrentcontrolfileformat

'/data/backup/%d_CTL_%T_%s_%p.bak，;

在spfile、控制文件、數(shù)據(jù)庫(kù)全丟的情況下可通過(guò)下面

的方式恢復(fù)

RMAN>connecttarget

connectedtotargetdatabase(notstarted)

RMAN>startup

RMAN>restorespfilefrom

'/data/backup/COMMDB_SPFILE_20030411_9_l.bak，;

SQL>startup

0RA-00205:errorinidentifyingcontrolfile,check

alertlogformoreinfo

RMAN>restorecontrolfilefrom

Jd:\DB92_CTL_20031113_9_l.BAK*;

Moutdatabase:

RMAN>recoverdatabase;

RMAN>alterdatabaseopenresetlogs;

注意：對(duì)數(shù)據(jù)庫(kù)設(shè)定控制文件保存?zhèn)浞菪畔?65天，具體語(yǔ)句

如下。

altersystemsetcontrol_file_record_keep_time=365

SCOPE=BOTH;

3.3.2.ArchiveLog

設(shè)置ArchiveLog的位置，確保存儲(chǔ)介質(zhì)有足夠的空間來(lái)保留

指定時(shí)間內(nèi)archivelog的總量。建設(shè)定期對(duì)進(jìn)行全備份，刪

除冗余歸檔日志文件。

3.3.3,全備份策略

對(duì)于小容量數(shù)據(jù)庫(kù)，可以采用全備份策略。對(duì)于大容量數(shù)據(jù)庫(kù)，

必須制定全備份策略方案，備份時(shí)對(duì)archivelog進(jìn)行轉(zhuǎn)儲(chǔ)，同時(shí)冷

備份catalog數(shù)據(jù)庫(kù)。

3.3.4,增量備份策略

對(duì)于大容量數(shù)據(jù)庫(kù)，必須制定增量備份、累積備份和全備份的周

期，備份時(shí)對(duì)archivelog進(jìn)行轉(zhuǎn)儲(chǔ)，同時(shí)冷備份catalog數(shù)據(jù)庫(kù)。

3.3.5.恢復(fù)原則

采用Rman腳本進(jìn)行數(shù)據(jù)庫(kù)恢復(fù)。數(shù)據(jù)庫(kù)恢復(fù)有以下幾種：

3.3.5.1.局部恢復(fù)

主要用于恢復(fù)表空間、數(shù)據(jù)文件，一般不影響數(shù)據(jù)庫(kù)其他操作。

3.352.完全恢復(fù)

數(shù)據(jù)庫(kù)恢復(fù)到故障點(diǎn)，由catalog當(dāng)前數(shù)據(jù)庫(kù)決定。

3.3.5.3.不完全恢復(fù)

恢復(fù)到數(shù)據(jù)庫(kù)的某一時(shí)間點(diǎn)或備份點(diǎn)。

恢復(fù)catalog數(shù)據(jù)庫(kù)。

恢復(fù)數(shù)據(jù)庫(kù)controlfile□

恢復(fù)到數(shù)據(jù)庫(kù)某一時(shí)間點(diǎn)。

重設(shè)日志序列。

3.4.備用數(shù)據(jù)庫(kù)原則

數(shù)據(jù)庫(kù)系統(tǒng)在以下情況下可以考慮采用備用數(shù)據(jù)庫(kù)dataguard

原則：

數(shù)據(jù)庫(kù)容量適中。

數(shù)據(jù)庫(kù)嚴(yán)格要求7*24不間斷，或間斷時(shí)間要求控制在最小范圍

內(nèi)。

數(shù)據(jù)庫(kù)要求有異地備份冗余。

3.5.一些小經(jīng)驗(yàn)

使用oems的cms時(shí)，首選項(xiàng)要求是節(jié)點(diǎn)和數(shù)據(jù)庫(kù)分別加入系統(tǒng)

用戶（如：adninistratoi）和數(shù)據(jù)庫(kù)用戶（systen）。節(jié)點(diǎn)的系

統(tǒng)用戶必須有批處理作業(yè)登錄的權(quán)限。

agent不能啟動(dòng)，lisnter修改后都要手動(dòng)刪除

oracle\pra9\petwork\ggent中的*.q文件。

orac1^\gdmiri\ny9i\t）dunp中是用戶的出錯(cuò)日志。

改變表的空間的方式altertablehr.ssssmoveTABLESPACE

example（要重建索引）；或用imp導(dǎo)入時(shí)，設(shè)定導(dǎo)入用戶只有某一

表空間的使用權(quán)，無(wú)RESCLRCE角色和3LJMTIED1ABLESDCE權(quán)限。

aletersystemsetlogcheckpointtoalteEruq后可報(bào)警

文件發(fā)現(xiàn)checkpoint的起動(dòng)和結(jié)束時(shí)間。

3.6.系統(tǒng)調(diào)優(yōu)知識(shí)

3.6.1.生成狀態(tài)報(bào)表（statspack的使用）

使用（存放位置@\rdbmsYdmiriSl的文件生成報(bào)表用戶。

@\rdbns\3，dniri\5pcreate.sql建表。

將timsUtatistics設(shè)定true,

使用生成的perf