數(shù)據(jù)庫(kù)oracle學(xué)習(xí)之路

數(shù)據(jù)庫(kù)oracle學(xué)習(xí)之路

1.數(shù)據(jù)庫(kù)基礎(chǔ)

2.數(shù)據(jù)庫(kù)主鍵生成

Select

Substr(To_char(dbms_random.value),2,22)||getUUID.Nextval

Fromdual;

3.數(shù)據(jù)庫(kù)空間查詢(xún)

--表空間在什么空間下

selectdf.tablespace_name”表空間名”,totalspace”總空間M”,freespace”剩余空間

M",round((l-freespace/totalspace)*100z2)

from

(selecttablespace_name,round(sum(bytes)/1024/1024)totalspace

fromdba_data_files

groupbytablespace_name)df,

(selecttablespace_namezround(sum(bytes)/1024/1024)freespace

fromdba_free_space

groupbytablespace_name)fs

wheredf.tablespace_name=fs.tablespace_name;

--查用戶(hù)下所用空間

SELECTowner,tablespace_name,ROUND(SUM(BYTES)/1024/1024,2)HUSED(M)"

FROMdba_segments

GROUPBYowner,tablespace_name

ORDERBYSUM(BYTES)DESC；

一查用戶(hù)下所有表所占空間

selectOWNER,t.segment_name,t.segment_typezsum(t.bytes/1024/1024)mmm

fromdba_segmentst

wheret.owner="JIANGSU'

andt.segment_type='TABLE'

groupbyOWNER,t.segment_name,t.segment_type

orderbymmmdesc;

4.oracle之刪除重復(fù)數(shù)據(jù)

selecta.rowid,a.*from表名a

wherea.rowid!=

(

selectmax(b.rowid)from表名b

wherea.字段1=b.字段1and

a.字段2=b.字段2

)

一刪除

deletefrom表名a

wherea.rowid!=

(

selectmax(b.rowid)from表名b

wherea.字段1=b.字段1and

a.字段2=b.字段2

)

5.oracle之查詢(xún)數(shù)據(jù)第一條記錄

select*fromtabrownum<2

6.oracle之存儲(chǔ)過(guò)程/函數(shù)等書(shū)寫(xiě)規(guī)則

7.oracle之正則表達(dá)式函數(shù)：regexp」ike、regexp_substr>

regexp_instr>regexp_replace

Oracle使用正則表達(dá)式離不開(kāi)這4個(gè)函數(shù)：

1。regexpjike

2。regexp_substr

3?regexpjnstr

4。regexp_replace

看函數(shù)名稱(chēng)大概就能猜到有什么用了。

regexpjike只能用于條件表達(dá)式，與like類(lèi)似，但是使用的正則表達(dá)式進(jìn)行匹配，語(yǔ)法

很簡(jiǎn)單：

match_paramete)^

—)REGEXP_LIKE

regexp_substr函數(shù)，與substr類(lèi)似，用于拾取合符正則表達(dá)式描述的字符子串，語(yǔ)法如

下：

tREGEXPSUBSTR{fy)^sour8char)^7)^網(wǎng)

regexpjnstr函數(shù)，與instr類(lèi)似，用于標(biāo)定符合正則表達(dá)式的字符子串的開(kāi)始位置，語(yǔ)法

如下：

tREGEXPINSTRXD^sour8chaX7)^pattem>>

|regexpjnstr|mateh_paramete

—3rreturn_optionY>

oocurrenoe^>^----------------------

廣(^(position》

1o

regexp_replace函數(shù)，與replace類(lèi)似，用于替換符合正則表達(dá)式的字符串，語(yǔ)法如下:

tREGEXP_REPLACE

_______廠match_parameter

^H(^^occurr?8^~>^-------------------------

_^-9^y^positbn^-(regexp_replace---------------------------------------------------

x^7})(iepkoestring^?^------------------------------------------------------------------------------------------------------------------

\)>

這里解析一下幾個(gè)參數(shù)的含義:

(1osource_char,輸入的字符串，能夠是列名或者者字符串常量、變量。

(2opattern,正則表達(dá)式。

3omatch_parameter,匹配選項(xiàng)。

取值范圍：i：大小寫(xiě)不敏感；c：大小寫(xiě)敏感；n：點(diǎn)號(hào).不匹配換行符號(hào)；m：多行模

式；x：擴(kuò)展模式，忽略正則表達(dá)式中的空白字符。

4oposition,標(biāo)識(shí)從第幾個(gè)字符開(kāi)始正則表達(dá)式匹配。

5ooccurrence,標(biāo)識(shí)第幾個(gè)匹配組。

6。replace_string,替換的字符串。

8.oracle之decode函數(shù)

decode函數(shù)=java中的true?A:B

decode(參數(shù)，1,2,3)參數(shù)==1時(shí)結(jié)果為2否則結(jié)果為3

9.oracle之linux下啟動(dòng)腳本

1)啟動(dòng)數(shù)據(jù)庫(kù)

oracle@suse92:sqlplus/nolog

SQL*Plus:Release.0-ProductiononFriJan2002:29:372006

Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.

SQL>connect/assysdba

Connectedtoanidleinstance.

SQL>startup

ORACLEinstancestarted.

TotalSystemGlobalArea135352820bytes

FixedSize455156bytes

VariableSize109051904bytes

DatabaseBuffers25165824bytes

RedoBuffers679936bytes

Databasemounted.

2)關(guān)閉數(shù)據(jù)庫(kù)：

oracle@suse92:~>sqlplus/nolog

SQL*Plus:Release.0-ProductiononFriJan2002:29:372006

Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.

SQL>connect/assysdba

Connectedtoanidleinstance.

SQL>shutdwonabort;

3）啟動(dòng)監(jiān)聽(tīng)器

oracle@suse92:~>Isnrctlstart

4）停止監(jiān)聽(tīng)器

oracle@suse92:~>Isnrctlstop

5）查看監(jiān)聽(tīng)器狀態(tài)

oracle@suse92:~>Isnrctlstatus

或者Isnrctl命令進(jìn)入監(jiān)聽(tīng)程序后再用status

10.oracle之備份工作

背景：Oracle10g服務(wù)器，Oracle10g客戶(hù)端,windowsXP操作平臺(tái)

要求：Oracle數(shù)據(jù)庫(kù)服務(wù)器對(duì)數(shù)據(jù)庫(kù)ytcn每天自動(dòng)備份一次。

解決方案：利用任務(wù)計(jì)劃、批處理文件與Oracle的exp導(dǎo)出功能，根據(jù)日期自動(dòng)生成

Oracle備份文件。全面步驟：

1）創(chuàng)建批處理文件ytcn.bat

ytcn.bat中全面內(nèi)容如下：

@echooff

echo正在備份Oracle數(shù)據(jù)庫(kù)，請(qǐng)稍等……

expuserid='|cn/ytcn（ctassysdba'

file=miHmiim%date:~0,4%%date:~5,2%%date:~8,2%,dmp

log=^^^^^^^^|^m^|^odate:~0,4%%date:~5,2%%date:~8,2%,log加

echo任務(wù)完成！

其中紅色部分是根據(jù)需要進(jìn)行變動(dòng)的地方，比如作者的項(xiàng)目名“銀通網(wǎng)”，數(shù)據(jù)庫(kù)ytcn用

戶(hù)名ytcn,密碼ytcn,要在目錄"e:/bak/ytcn/oracle/ytcn"下生成形如"ytcn20090711.dmp"

與"ytcn20090711.log"的備份與日志文件，全表導(dǎo)出。

另外：％date%的值在不一致的系統(tǒng)、語(yǔ)言版本下可能是不一樣的，操縱面板里面區(qū)域選項(xiàng)

的設(shè)定也會(huì)改變％date%的值。請(qǐng)先在命令行中測(cè)試echo%date%的返回值。%date:~4,10%

是返回日期函數(shù)，~后的第一個(gè)參數(shù)是要截取的起始位置（從0開(kāi)始），第二個(gè)參數(shù)是要截取

的長(zhǎng)度，如沒(méi)有則是截取到最后，參數(shù)可酌情修改。如需要準(zhǔn)確的時(shí)間做為文件名，請(qǐng)

用％time%函數(shù)，參數(shù)同上。

2）添加一個(gè)任務(wù)計(jì)劃ytcn

開(kāi)始>所有程序>附件>系統(tǒng)工具>任務(wù)計(jì)劃>添加任務(wù)計(jì)劃>下一步>在瀏覽

中查找剛剛寫(xiě)好的ytcn.bat文件>任務(wù)名輸入ytcn,執(zhí)行這個(gè)任務(wù)選擇每天,下一步>起

始時(shí)間下午12:00,起始日期2009-7-11,下一步>輸入用戶(hù)名及密碼，用戶(hù)名要求是管理員權(quán)

限用戶(hù)名,下一步>完成

點(diǎn)擊"完成”之后，會(huì)在任務(wù)計(jì)劃?rùn)谀肯滦略鲆粋€(gè)名為"ytcn"的任務(wù)計(jì)劃，說(shuō)明已經(jīng)配置完畢。

備注：有的時(shí)候點(diǎn)擊"完成"之后，系統(tǒng)警告

0x80（141315：任務(wù)

這是由于電腦的任務(wù)計(jì)劃程序服務(wù)沒(méi)有啟動(dòng)起來(lái)。開(kāi)始>所有程序>管理工具>服務(wù)，

找到"TaskScheduler”服務(wù)，發(fā)現(xiàn)啟動(dòng)類(lèi)型為“已禁用"，右鍵單擊更換為“自動(dòng)"，并把它啟動(dòng)起

來(lái)，然后重新添加一次任務(wù)計(jì)劃ytcn就能夠了。

11.oracle之命中率查詢(xún)

Buffercache由數(shù)據(jù)塊構(gòu)成。

1.Buffercache的工作原理

LRU列表：MRU................。LRU.（全表掃描FTS放在LRU端。）

緩沖區(qū)塊的狀態(tài):Free、Pinned>Clean>Dirty.

DirtyList或者WriteList（寫(xiě)列表）。

數(shù)據(jù)庫(kù)寫(xiě)進(jìn)程DBW0將緩沖區(qū)高速緩存中的數(shù)據(jù)寫(xiě)到數(shù)據(jù)文件中。

2.測(cè)量Buffercache的性能

測(cè)量Buffercache的命中率：

SQL>select1-((physical.value-direct.value-lobs.value)/

logical.value)"BufferCacheHitRatio"

fromV$SYSSTATphysical,

V$SYSSTATdirect,

V$SYSSTATlobs,

V$SYSSTATlogical

where=*physicalreads

And=1physicalreadsdirect,

and='physicalreadsdirect(lob)

And=1sessionlogicalreads,;

''BufferCacheHitRatio”的值要>90%.

使用STATSPACK來(lái)監(jiān)視Buffercache.

使用REPORT.TXT來(lái)監(jiān)視Buffercache.

非命中率指標(biāo)：FreeBufferInspected、FreeBufferWaits,BufferBusyWaits.

(V$sysstat)

使用PerformanceManager(數(shù)據(jù)庫(kù)例程)來(lái)監(jiān)視BufferCache.

3.提高緩沖區(qū)高速緩存性能的方法

加大BufferCache的大?。篿nit.ora參數(shù)DB_CACHE_SIZE(動(dòng)態(tài)參數(shù))。

使用BufferCacheAdvisory功能決定BufferCache的大小：

首先將init.ora參數(shù)DB_CACHE_ADVICE設(shè)成ON,然后查詢(xún)V$DB_CACHE_ADVICE.

使用多個(gè)緩沖區(qū)池：

KeepPool：DB_KEEP_CACHE_SIZE

RecyclePool：DB_RECYCLE_CACHE_SIZE

DefaultPool：DBCACHESIZE

在內(nèi)存中緩存表：表的CACHE選項(xiàng)，對(duì)優(yōu)化小表的全表掃描。

正確創(chuàng)建索引。

4.調(diào)整LargePool與JAVAPOOL

LargePool用于共享服務(wù)器、RMAN、并行查詢(xún)、DBWR的從屬進(jìn)程。

LargePool的大小通過(guò)init.ora參數(shù)Large_pool_size設(shè)置。默認(rèn)為8M.

從V$sgastat中監(jiān)視freememory的值：

SQL>SELECTname,bytesFROMV$sgastatWHEREpool='largepool'；

JAVA_POOL池的默認(rèn)大小為32M,關(guān)于大型Java應(yīng)用程序，JAVA_POOL池的大小應(yīng)大

于50M.

init.ora參數(shù)java_pool_size

從V$sgastat中監(jiān)視freememory的值。

SQL>SELECTname,bytes/1024/1025FROMV$sgastatWHEREpool='java

pool1;

調(diào)整重做有關(guān)的性能

Oracle重做有關(guān)的組件包含：RedoLogBuffer>OnlineRedoLog、LGWR、ArchiveLog、

Checkpoint、ArchO.

1.監(jiān)視RedoLogBuffer的性能

RedoLogBuffer不使用LRU（LeastRecentlyUsed）算法管理。

當(dāng)下列事件發(fā)生時(shí)，RedoLogBuffer的內(nèi)容存盤(pán)：

Commit時(shí)、每3秒、空間使用1/3、達(dá)到1M、檢查點(diǎn)。

假如寫(xiě)入RedoLogBuffer的速度超過(guò)LGWR存盤(pán)的速度，就會(huì)因等待而降低性能。

監(jiān)視RedoLogBuffer的重試率（<1%）。

Selectretries.value/entries.value"RedoLogBufferRetryRatio"

FromV$sysstatretrieszV$sysstatentries

Where=1redobufferallocationretries,

And=1redoentries1

"RedoLogBufferRetryRatio”的值要<1%.

Selectname,valuefromV$sysstatwherename=1redologspacerequests*

假如該值大，需要增加RedoLogBuffer.

2.提高RedoLogBuffer的性能

增力口RedoLogBuffer的大小：init.ora參數(shù)log_buffer.

減小重做日志的生成量。（假如設(shè)置表的NOLOGGING屬性，下列操作不記錄在Online

RedoLog中：用SQL*Loader的直接路徑加載。

NOLOGGING屬性還可用于下列SQL語(yǔ)句：CREATETABLEASSELECT.CREATEINDEX、

ALTERINDEXREBUILD.CREATETABELSPACE）。

3.調(diào)整檢查點(diǎn)進(jìn)程的性能

測(cè)量檢查點(diǎn)進(jìn)程的性能：沒(méi)有完成的檢查點(diǎn)進(jìn)程的次數(shù)。

select*fromV$system_event；

兩個(gè)事件：checkpointcompleted、logfileswitch(checkpointincomplete)。

Select*fromV$sysstat.

backgroundcheckpointsstarted與backgroundcheckpointscompleted.

使用Alert11志來(lái)記錄檢查點(diǎn)進(jìn)程：init.ora參數(shù)log_checkpoint_to_alert.

使用PerformanceManager來(lái)測(cè)量檢查點(diǎn)進(jìn)程的性能：I/O中的平均灰數(shù)據(jù)隊(duì)列長(zhǎng)度(假

如為0,說(shuō)明檢查點(diǎn)太頻繁)。

建議調(diào)整onlineredolog的大小，使檢查點(diǎn)進(jìn)程每20-30分鐘執(zhí)行一次。

4.調(diào)整聯(lián)機(jī)重做日志文件

使用V$system_event來(lái)監(jiān)視聯(lián)機(jī)重做日志文件的性能：

logfileparallelwrite、logfileswitchcompleted.

調(diào)整聯(lián)機(jī)重做日志文件的方法：與數(shù)據(jù)文件、操縱文件、歸檔日志文件分開(kāi)，放在原始

設(shè)備上。

5.調(diào)整歸檔性能

檢查歸檔進(jìn)程的性能：通過(guò)V$system_event中的logfileswitch(archivingneeded)

事件。

檢查每個(gè)歸檔進(jìn)程的狀態(tài)：V$archive_processes.

創(chuàng)建多個(gè)歸檔進(jìn)程：initora參數(shù)LOG_ARCHIVE_MAX_PROCESSES(默認(rèn)為2

12.oracle之?dāng)?shù)據(jù)庫(kù)空間限額

revokeunlimitedtablespacefromccicjy;

alteruserccicjyquota0onJINGYOUTBS;

alteruserccicjyquotaunlimitedonJYSPACE;

空間轉(zhuǎn)換

13.oracle之查詢(xún)表空間文件所在路徑

select*fromdba_data_fil.es;

14.oracle之物化視圖學(xué)習(xí)

一、環(huán)境

數(shù)據(jù)庫(kù)1：

數(shù)據(jù)庫(kù)：oralceORACLE.0RAC

基表用戶(hù)：WEB_USER

查詢(xún)用戶(hù)：MVLOG_USER

MVLOGJJSER權(quán)限如下：

grantcommentanytabletoMVLOG_USER;

grantcreateanytabletoMVLOG_USER;

grantcreatematerializedviewtoMVLOG_USER;

數(shù)據(jù)庫(kù)2

數(shù)據(jù)庫(kù)：oralceORACLE.0RAC

物化視圖用戶(hù)：QUERY_USER

網(wǎng)絡(luò)環(huán)境：10M光纖

目的：將數(shù)據(jù)庫(kù)1中WEB_USER用戶(hù)下的表，同步到數(shù)據(jù)庫(kù)2的QUERYJJSER用戶(hù)下，為確保

數(shù)據(jù)庫(kù)1的WEB.USER中數(shù)據(jù)安全性，通過(guò)MVLOG.USER創(chuàng)建物化視圖。

二、建立物化視圖日志(數(shù)據(jù)庫(kù)1WEB_USER)

CREATEMATERIALIZEDVIEWLOG

ONWEB_USER.ZFZZB

TABLESPACETS_MVLOG；

--將對(duì)表及表日志查詢(xún)權(quán)限給mvlog_userfflpo

grantselectonWEB_USER.ZFZZBtomvlog_user;

grantselectonWEB_USER.mlog$_ZFZZBtomvlog_user;

三、創(chuàng)建物化視圖(數(shù)據(jù)庫(kù)2QUERY_USER)

1、創(chuàng)建DBIink

CREATEpublicDATABASELINKTJSB_LINKCONNECTTOmvlog_userIDENTIFIEDBY

mvlog.userUSINGTJDB';

2、創(chuàng)建表

createtableZFZZBasselect*fromWEB_USER.ZFZZB@TJSB_LINKwhere1=2;

3、建立物化視圖

CREATEMATERIALIZEDVIEWZFZZB

onprebuilttable

REFRESHFORCEWITHprimarykey

ONDEMAND

AS

(

SELECTAAB001,AAC001,AAC002,AAC003,AAE002,AKC087,AKC087S,CAB004,

CKC202,CKC205,CKC435,CKC438,CKC442,ZZ

fromWEB_USER.ZFZZB@TJSB_LINK

)；

4、全量刷新

execdbms_mview.refresh('ZFZZB','Complete');

6、創(chuàng)建主鍵及索引

altertableZFZZB

addconstraintPK_ZFZZBprimarykey(AAC001,CKC442)

usingindex

tablespaceTJSB_TS_INDEX;

7、增量刷新

execdbms_mview.refresh('ZFZZB');

15.oracle學(xué)習(xí)之位置查詢(xún)

select(length(t.a2)-length(replace(t.a2,,'')))/length()

fromzwj_lt

select(Iength('oracle11g,oraclelOgoracle9i|oracle8i')-

Iength(replace('oracle11g,oraclelOgoracle9i|oracle8i',

'oracle',

")))/length('oracle')string_countfromdual;

16.oracle學(xué)習(xí)物化視圖

物化視圖，它是用于預(yù)先計(jì)算并儲(chǔ)存表連接或者聚集等耗時(shí)較多的操作的結(jié)果，這樣，在執(zhí)行查詢(xún)時(shí)，

就能夠避免進(jìn)行這些耗時(shí)的操作，從而快速的得到結(jié)果。物化視圖有很多方面與索引很相似：使用物化視

圖的目的是為了提高查詢(xún)性能：物化視圖對(duì)應(yīng)用透明，增加與刪除物化視圖不可能影響應(yīng)用程序中SQL語(yǔ)

句的正確性與有效性；物化視圖需要占用存儲(chǔ)空間；當(dāng)基表發(fā)生變化時(shí)，物化視圖也應(yīng)當(dāng)刷新。

其中物化視圖有三種：聚集物化視圖、包含連接物化視圖、嵌套物化視圖。但三種物化視圖的快速刷新的

限制條件有很大區(qū)別，而其他方面則區(qū)別不大。創(chuàng)建物化視圖創(chuàng)建物化視圖時(shí)能夠指定多種選項(xiàng)，要

緊選項(xiàng)如下：

1、創(chuàng)建方式(BuildMethods)：BuildImmediate與BuildDeferred兩種。BuildImmediate是在創(chuàng)建物

化視圖的時(shí)候就生成數(shù)據(jù)，而B(niǎo)uildDeferred則在創(chuàng)建時(shí)不生成數(shù)據(jù)，以后根據(jù)需要在生成數(shù)據(jù)。默認(rèn)為

BuildImmediate.

2、查詢(xún)重寫(xiě)(QueryRewrite)：EnableQueryRewrite與DisableQueryRewrite兩種。分別指出創(chuàng)建的

物化視圖是否支持查詢(xún)重寫(xiě)。查詢(xún)重寫(xiě)是指當(dāng)對(duì)物化視圖的基表進(jìn)行查詢(xún)時(shí)，Oracle會(huì)自動(dòng)推斷能否通過(guò)

查詢(xún)物化視圖來(lái)得到結(jié)果，假如能夠，則避免了聚集或者連接操作，而直接從已經(jīng)計(jì)算好的物化視圖中讀

取數(shù)據(jù)。默認(rèn)為DisableQueryRewrite。

3、刷新(Refresh)：指當(dāng)基表發(fā)生了DML操作后，物化視圖何時(shí)使用哪種方式與基表進(jìn)行同步。刷新

的模式有兩種：OnDemand與OnCommit。OnDemand指物化視圖在用戶(hù)需要的時(shí)候進(jìn)行刷新，能夠手

工通過(guò)DBMS_MVIEW.REFRESH等方法來(lái)進(jìn)行刷新，也能夠通過(guò)JOB定時(shí)進(jìn)行刷新。OnCommit指物

化視圖在對(duì)基表的DML操作提交的同時(shí)進(jìn)行刷新。刷新的方法有四種:Fast,Complete>Force與Never,

Fast刷新使用增量刷新，只刷新自上次刷新以后進(jìn)行的修改。Complete刷新對(duì)整個(gè)物化視圖進(jìn)行完全的

刷新。假如選擇Force方式，則Oracle在刷新時(shí)會(huì)去推斷是否能夠進(jìn)行快速刷新，假如能夠則使用FAST

方式，否則使用Complete的方式。Never指物化視圖不進(jìn)行任何刷新。默認(rèn)值是ForceOnDemand。

在建立物化視圖的時(shí)候能夠指定。rderby語(yǔ)句，使生成的數(shù)據(jù)按照一定的順序進(jìn)行儲(chǔ)存。只是這個(gè)語(yǔ)句不

可能寫(xiě)入物化視圖的定義中，而且對(duì)以后的刷新也無(wú)效。

4、物化視圖日志：假如需要進(jìn)行快速刷新，則需要建立物化視圖日志。物化視圖日志根據(jù)不一致物化視圖

的快速刷新的需要，能夠建立為RowID或者PrimaryKey類(lèi)型的。還能夠選擇是否包含Sequence、Including

NewValues與指定列的列表。能夠指明OnPreBuildTable語(yǔ)句將物化視圖建立在一個(gè)已經(jīng)存在的

表上。這種情況下，物化視圖與表務(wù)必同名。當(dāng)刪除物化視圖時(shí)，不可能刪除同名的表。這種物化視圖的

查詢(xún)重寫(xiě)要求參數(shù)Query_Rewrite_integerity務(wù)必設(shè)置為trusted或者者stale_toleratedo

5、物化視圖能夠進(jìn)行分區(qū)。而且基于分區(qū)的物化視圖能夠支持分區(qū)變化跟蹤（PCT）o具有這種特性的物

化視圖，當(dāng)基表進(jìn)行了分區(qū)保護(hù)操作后，仍然能夠進(jìn)行快速刷新操作。關(guān)于聚集物化視圖，能夠在Groupby

列表中使用Cube或者RollUp,來(lái)建立不?致等級(jí)的聚集物化視圖。

示例1:creatematerializedviewMV_DOP_TESTrefreshforceondemandasselect

dsso.dopjd,soo.work_center_no,soo.operation_descriptionfrom

SHOP_ORDER_OPERATIONSOO,DOP_SUPPLY_SHOP_ORDDSSOwhere

soo.order_no=dsso.order_noandsoo.release_no=dsso.release_noand

soo.sequence_no=dsso.sequence_no;一其中創(chuàng)建與刪除物化視圖與其表或者視圖DDL一樣：

DROPmaterializedviewlogonmaterialized_view_log_name;創(chuàng)建物化視圖時(shí)創(chuàng)建存儲(chǔ)的日志空間

（存儲(chǔ)物化視圖的對(duì)象的改變信息）CREATEMATERIALIZEDVIEWLOGON

Dop_Supply_Shop_Ord_Tab--（基表名）tablespaceifsapp_DATA一日志儲(chǔ)存在特定的表空間

WITHROWID;dropmaterializedviewMV_materialized_view_name;

示例2:creatematerializedviewMV_DOP_TESTTABLESPACEifsapp_DATA--儲(chǔ)存表空間

BUILDDEFERRED-延遲刷新不立即刷新,此建立初始視圖通常沒(méi)數(shù)據(jù)refreshforce-假如能夠快速

刷新則進(jìn)行快速刷新，否則完全刷新withrowid-根據(jù)rowid刷新（默認(rèn)是主鍵）Ondemand-

按照指定方式刷新startwithto_date（'2008-12-1113:20:51','YYYY-MM-DDHH24:MI:SS,）next

sysdate+1/48asselectdsso.dopjd,soo.work_center_no,

soo.operation_descriptionfromSHOP_ORDER_OPERATIONSOO,

DOP_SUPPLY_SHOP_ORDDSSOwheresoo.order_no=dsso.order_noand

soo.release_no=dsso.release_noandsoo.sequence_no=dsso.sequence_no

其他

一、關(guān)于物化視圖日志：查詢(xún)物化視圖日志文件格式：descmlog$」zwmvtest;創(chuàng)建物化視圖時(shí)默認(rèn)指定

物化視圖中存在主鍵，假如不指定，那么創(chuàng)建的物化視圖日志文件的基表務(wù)必存在主鍵，否則會(huì)報(bào)錯(cuò)Demo:

對(duì)一個(gè)表test創(chuàng)建日志：creatematerializedviewlogontest;那么會(huì)報(bào)：表'LZWMVTEST不包含主鍵約束

條件這種情況下，就務(wù)必指定日志文件結(jié)構(gòu)比如：creatematerializedviewlogontestwithrowid（具體的

針對(duì)日志內(nèi)容方面的在另外一個(gè)專(zhuān)題里說(shuō)明，這里就簡(jiǎn)述到此）

二、關(guān)于生成數(shù)據(jù)與刷新：1＞生成數(shù)據(jù)兩大選項(xiàng)：buildimmediatebuilddeferredBuildimmediate:在創(chuàng)建

物化視圖的同時(shí)根據(jù)主表生成數(shù)據(jù)Buliddeferred:在創(chuàng)建物化視圖的同時(shí)，在物化視圖內(nèi)不生成數(shù)據(jù)，假

如如今沒(méi)有生成數(shù)據(jù)，以后能夠采?。篍XECDBMS_MVIEW.Refresh（＜MV_name,/C）,注意務(wù)必使用全

量刷新，默認(rèn)是增量刷新，因此這里參數(shù)務(wù)必是C,由于之前都沒(méi)有生成數(shù)據(jù)，因此務(wù)必全量。2＞關(guān)于刷

新2刷新方式：completefastforceComplete:完全刷新整個(gè)物化視圖，相當(dāng)于重新生成物化視圖，如今即

時(shí)增量刷新可用也全量刷新0Fast:當(dāng)有數(shù)據(jù)更新時(shí)依照相應(yīng)的規(guī)則對(duì)物化視圖進(jìn)行更新（如今務(wù)必創(chuàng)建物

化視圖日志（物化視圖日志記錄了數(shù)據(jù)更新的日志），關(guān)于日志的說(shuō)明，參照“物化視圖日志文件介紹”）0

Force:當(dāng)增量刷新可用則增量刷新，當(dāng)增量刷新不可用，則全量刷新（此項(xiàng)為默認(rèn)選項(xiàng)）只是從實(shí)際情況

出發(fā)，應(yīng)該盡量不使用默認(rèn)選項(xiàng)，能夠考慮使用增量刷新，對(duì)大表特別有效，大表全量更新速度是非常慢

的，特別是在存在索引的情況下（在創(chuàng)建物化視圖語(yǔ)句中，可能某些限制查詢(xún)的條件，導(dǎo)致了增量刷新無(wú)

法使用，這個(gè)是需要注意的，具體是哪類(lèi)語(yǔ)句導(dǎo)致fast刷新不可用，有待總結(jié)…尹刷新時(shí)間：ondemand

oncommitstartwith/nextOndemand:在需要刷新時(shí)進(jìn)行刷新（人工推斷）0ncommit:在基表上有提交操

作時(shí)，進(jìn)行更新Startwith:指定首次刷新的時(shí)間（通常指定的是當(dāng)前時(shí)間，只是也能夠在創(chuàng)建物化視圖時(shí)

不生成數(shù)據(jù)，則能夠考慮在指定的時(shí)間刷新，從而生成數(shù)據(jù)）Next:刷新的周期時(shí)間

三、基于主鍵的物化視圖與ROWID的物化視圖的說(shuō)明創(chuàng)建物化視圖日志時(shí)，指定了記錄更新的原則即

with后面的primary或者者rowid或者者objectid等等，后面，默認(rèn)是以primarykey為記錄更新，在物

化視圖內(nèi)也是以此為更新的原則。比如：1、假如日志內(nèi)使用的是primarykey則在創(chuàng)建物化視圖時(shí)指定

rowid來(lái)更新，則會(huì)報(bào)ORA-12032:不能使用"TEST"上實(shí)體化視圖日志中的rowid歹42、如過(guò)日志內(nèi)使

用的是rowid則在創(chuàng)建物化視圖時(shí)指定primary或者者默認(rèn)指定，則會(huì)報(bào)ORA-23415:"GIS"."LZWMV"的

實(shí)體化視圖日志不記錄主鍵

四、關(guān)于物化視圖存放的的表空間直接在創(chuàng)建物化視圖時(shí)指定日志存放的表空間與物化視圖的表空間。例

子：creatematerializedviewMV_TESTtablespacetest--表空間名稱(chēng)

五、關(guān)于查詢(xún)重寫(xiě)與更新在創(chuàng)建查詢(xún)重寫(xiě)時(shí)，基表中務(wù)必有主鍵約束，視圖里是無(wú)法創(chuàng)建主鍵的，只是其

繼承了基表的主鍵約束。（關(guān)于視圖的創(chuàng)建的一些技巧有待總結(jié)……）下面給個(gè)例子Creatematerialized

viewMV_TE8TRefreshfast--前提是務(wù)必創(chuàng)建基表日志,能夠忽略該項(xiàng)Enablequeryrewrite--前提是基

表上務(wù)必存在主鍵約束AsSelect*fromTEST;--物化視圖數(shù)據(jù)生成

六、關(guān)于創(chuàng)建物化視圖的例子：1、使用增量刷新的物化視圖的寫(xiě)法創(chuàng)建物化視圖日志，務(wù)必創(chuàng)建日志

CreatematerializedviewlogonTEST--TEST為表名--注：（TEST為表名或者者視圖名，關(guān)于視圖上

建立物化視圖，見(jiàn)基于視圖的物化視圖一創(chuàng)建物化視圖語(yǔ)句：CreatematerializedviewMV_TEST

--MVTEST為物化視圖名Buildimmediate----創(chuàng)建時(shí)生成數(shù)據(jù)對(duì)應(yīng)的是builddeferredRefreshfast--增

量刷新Oncommit--在基表有更新時(shí)提交，這里該句對(duì)視圖無(wú)效Withrowid--這里創(chuàng)建基于rowid的物

化視圖，對(duì)應(yīng)的是primarykeyAsSelect*fromTEST;一生成物化視圖數(shù)據(jù)語(yǔ)句

17.oracle學(xué)習(xí)之linux下啟動(dòng)停止服務(wù)

linux下的oracle從10g以后其啟動(dòng)與關(guān)閉與往常版本是完全不一致的

9i之后已經(jīng)沒(méi)有svrmgrl了，所有的管理工作都通過(guò)sqlplus來(lái)完成

啟動(dòng)數(shù)據(jù)庫(kù)步驟如下：

注：$ORACLEHOME為oracle的安裝路徑

1,以oracle用戶(hù)登錄

suoracle

2,啟動(dòng)TNS監(jiān)聽(tīng)器

$ORACLE_HOME/bin/Isnrctlstart

3,用sqlplus啟動(dòng)數(shù)據(jù)庫(kù)

$0RACLE_HOME/bin/sqlplus/nolog

SQL>connectsystem/changeoninstallassysdba

SQL>startup

出現(xiàn)如下顯示，表示Oracle己經(jīng)成功啟動(dòng)

ORACLEinstancestarted.

TotalSystemGlobalArea205520896bytes

FixedSize778392bytes

VariableSize74456936bytes

DatabaseBuffers130023424bytes

RedoBuffers262144bytes

Databasemounted.

Databaseopened.

4,用sqlplus停止數(shù)據(jù)庫(kù)

$ORACLE_IIOME/bin/sqlplus/nolog

SQL>connectsystem/changeoninstallassysdba

SQL>shutdown

注：shutdown可加關(guān)閉選項(xiàng)，從最溫與到最粗暴的行為選項(xiàng)為(shutdown、shutdown

transactional>shutdownimmediate>shutdownabort)

命令解釋如下

shutdown:關(guān)閉，等待每個(gè)用戶(hù)退出系統(tǒng)或被取消后退出關(guān)閉數(shù)據(jù)庫(kù)。

shutdowntransactional:事務(wù)性關(guān)閉，等待每個(gè)用戶(hù)提交或回退當(dāng)前的事務(wù)，然后oracle

取消對(duì)話，在所有用戶(hù)退出系統(tǒng)后執(zhí)行關(guān)閉。

shutdownimmediate:直接關(guān)閉，取消所有用戶(hù)對(duì)話(促使回退)，執(zhí)行正常的關(guān)閉程序。

shutdownabort:終止關(guān)閉，關(guān)閉數(shù)據(jù)庫(kù)時(shí)沒(méi)有自動(dòng)檢查點(diǎn)或日志開(kāi)關(guān)。

出現(xiàn)如下顯示，表示oracle已經(jīng)停止

Databaseclosed.

Databasedismounted.

ORACLEinstanceshutdown.

18.oracle學(xué)習(xí)之rank函數(shù)

select*

From(selectrank()over(partitionbyt.車(chē)輛品牌，t.車(chē)輛型號(hào)orderbyto_number(數(shù)字)desc,

rownum)rr,

t.*

fromt_sjt)tt

wherett.rr=1

說(shuō)明：t.車(chē)輛品牌，t.車(chē)輛型號(hào)唯一篩選

說(shuō)明：to_mimber（數(shù)字）desc數(shù)值排序

說(shuō)明：tt.rr=1取num第一個(gè)

19.oracle學(xué)習(xí)之JOB

初始化有關(guān)參數(shù)job_queue_processes

altersystemsetjob_queuejDrocesses=39scope=spfile;//最大值不能超過(guò)1000;job_queue_interval=

10〃調(diào)度作業(yè)刷新頻率秒為單位

job_queue_process友示oracle能夠并發(fā)的iob的數(shù)量,能夠通過(guò)語(yǔ)句

showparameterjob_queue_process;

來(lái)查看oracle中job_queuejDrocess的值。當(dāng)job_queue_process值為0時(shí)表示全部停止oracle的job,

能夠通過(guò)語(yǔ)句

ALTERSYSTEMSETjob_queuejDrocesses=10;

來(lái)調(diào)整啟動(dòng)oracle的job。

有關(guān)視圖：

dbajobs

allJobs

userjobs

dbajobs_running包含正在運(yùn)行job有關(guān)信息

提交job語(yǔ)法：

begin

sys.dbmsJob.submitOob=>:job,

,

what=>'P_CLEAR_PACKBAL;J

next_date=>to_date('04-08-200805:44:09*,'dd-mm-yyyyhh24:mi:ss,),

interval=>'sysdate+1/360*)；

commit;

end;

創(chuàng)建JOB

variablejobnonumber;

begin

dbmsjob.submit(:jobno,,P_CRED_PLAN;',SYSDATE；SYSDATE+1/2880,,TRUE);

commit;

運(yùn)行JOB

SQL>begin

dbmsjob.run(:job1);

end;

/

刪除JOB

SQL>begin

dbmsjob.remove(:job1);

end;

DBA_JOBS

字段(列)類(lèi)型描述

JOBNUMBER任務(wù)的唯一標(biāo)示號(hào)

LOG_USERVARCHAR2(30)提交任務(wù)的用戶(hù)

PRIV_USERVARCHAR2(30)給予任務(wù)權(quán)限的用戶(hù)

SCHEMA_USERVARCHAR2(30)對(duì)任務(wù)作語(yǔ)法分析的用戶(hù)模式

LAST_DATEDATE最后一次成功運(yùn)行任務(wù)的時(shí)間

LAST_SECVARCHAR2(8)如HH24:MM:SS格式的last_date日期的小時(shí)，分鐘與秒

THIS_DATEDATE正在運(yùn)行任務(wù)的開(kāi)始時(shí)間，假如沒(méi)有運(yùn)行任務(wù)則為null

THIS_SECVARCHAR2(8)如HH24:MM:SS格式的this_date日期的小時(shí)，分鐘與秒

NEXT_DATEDATE下一次定時(shí)運(yùn)行任務(wù)的時(shí)間

NEXT_SECVARCHAR2(8)如HH24:MM:SS格式的next_date日期的小時(shí)，分鐘與秒

TOTAL_TIMENUMBER該任務(wù)運(yùn)行所需要的總時(shí)間，單位為秒

BROKENVARCHAR2⑴標(biāo)志參數(shù)，Y標(biāo)示任務(wù)中斷，以后不可能運(yùn)行

INTERVALVARCHAR2(200)用于計(jì)算下一運(yùn)行時(shí)間的表達(dá)式

FAILURESNUMBER任務(wù)運(yùn)行連續(xù)沒(méi)有成功的次數(shù)

WHATVARCHAR2(2000)執(zhí)行任務(wù)的PL/SQL塊

CURRENT_SESSION_LABELRAWMLSLABEL該任務(wù)的信任Oracle會(huì)話符

CLEARANCE_HIRAWMLSLABEL該任務(wù)可信任的Oracle最大間隙

CLEARANCE_LORAWMLSLABEL該任務(wù)可信任的Oracle最小間隙

NLS_ENVVARCHAR2(2000)任務(wù)運(yùn)行的NLS會(huì)話設(shè)置

MISC_ENVRAW(32)任務(wù)運(yùn)行的其他一些會(huì)話參數(shù)

描述INTERVAL參數(shù)值

每天午夜12點(diǎn)'TRUNC(SYSDATE+1)'

每天早上8點(diǎn)30分TRUNC(SYSDATE+1)+(8*60+30)7(24*60)'

每星期二中午12點(diǎn)'NEXT_DAV(TRUNC(SVSDATE),"TUESDAY")+12/24,

每個(gè)月第一天的午夜12點(diǎn)'TRUNC(LAST_DAY(SVSDATE)+1)'

每個(gè)季度最后「一天的晚上11點(diǎn)'TRUNC(ADD_MONTHS(SYSDATE+2/24,3),'Q')-1/24'

每星期六與日早上6點(diǎn)10分'TRUNC(LEAST(NEXT_DAY(SYSDATE,"SATURDAY"),NEXT_DAY(S

YSDATE,"SUNDAY")))+(6x60+10)/(24x60)'

1:每分鐘執(zhí)行

Interval=>TRUNC(sysdate,'mi')+1/(24*60)

2:每天定時(shí)執(zhí)行

比如：每天的凌晨1點(diǎn)執(zhí)行

Interval=>TRUNC(sysdate)+1+1/(24)

3:每周定時(shí)執(zhí)行

比如：每周?凌晨1點(diǎn)執(zhí)行

Interval=>TRUNC(next_day(sysdate,'星期一'))+1/24

4:每月定時(shí)執(zhí)行

比如：每月1日凌晨1點(diǎn)執(zhí)行

Interval=>TRUNC(LAST_DAY(SYSDATE))+1+1/24

5:每季度定時(shí)執(zhí)行

比如每季度的第一天凌晨1點(diǎn)執(zhí)行

Interval=>TRUNC(ADD_MONTHS(SYSDATE,3)；Q')+1/24

6:每半年定時(shí)執(zhí)行

比如：每年7月1日與1月1日凌晨1點(diǎn)

Interval=>ADD_MONTHS(trunc(sysdate,'yyyy,),6)+1/24

7:每年定時(shí)執(zhí)行

比如：每年1月1日凌晨1點(diǎn)執(zhí)行

Interval=>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24

20.oracle學(xué)習(xí)之路索引查詢(xún)

查找表的所有索引(包含索引名，類(lèi)型，構(gòu)成列)：

selectt.*,i.index」ypefromuser_ind_columnst,user_indexesiwheret.index_name=

i.index_nameandt.table_name=i.table_name

查找表的主鍵(包含名稱(chēng)，構(gòu)成列)：

selectcu.*fromuser_cons_columnscu,user_constraintsauwherecu.constraint_name=

au.constraint_nameandau.constraint_type='P'

排量生成創(chuàng)建腳本(唯一主鍵)：

select*altertable1IIcu.table_nameII'addconstraint1II

cu.constraint_nameII1primarykey(*||cu.column_nameI|');f

fromuser_cons_columnscu,user_constraintsau

wherecu.constraint_name=au.constraint_name

andau.constraint_type=?p?

排量生成創(chuàng)建腳本（所有）

select1createindex'IIt.index_name11on11t,table_name11'('11

t.column_name||');,

fromuser__ind_columnst,user_indexesi

wheret.index_name=i.index_name

andt.table_name=i.table_name

排量生成創(chuàng)建腳本（索引-去除主鍵）

select1createindex*IIt.index_nameII1on1IIt.table_nameII'('11

t,column_name||*);1

fromuser_ind_columnstzuser_indexesi

wheret.index_name=i.index_name

andt.table_name=i.table_name

andnotexists

(select1

fromuser_cons_columnscuzuser_constraintsau

wherecu.constraint_name=au.constraint_name

andau.constraint_type=*P'

andupper(t.index_name)=upper(cu.constraint_name))

21.oracle學(xué)習(xí)之路oracle中導(dǎo)入dmp數(shù)據(jù)庫(kù)文件

impusemame/password@SIDfile=XXX.dmpfromuser=XXXtouser=XXX

tables=(XXX,XXX)

其中，fromuser若為多個(gè)表空間的話，使用()將其括起來(lái)：fromuser=(a,b)；

touser參數(shù)仿fromuser參數(shù)；

若只導(dǎo)入一部分表，使用tables參數(shù)，用0括起要導(dǎo)入的表；假如想全部導(dǎo)入，不需要指定

tables參數(shù)

補(bǔ)充：

1.要新建一個(gè)數(shù)據(jù)庫(kù)；

2.若你的oracle安裝在Unix/Linux上，直接在shell中使用imp；假如你的oracle安裝在

Windows上，隨便在哪里開(kāi)啟一個(gè)CMD窗口就能夠執(zhí)行imp；

3.username/password指的是你的數(shù)據(jù)庫(kù)的登錄用戶(hù)名與密碼；

4.fromuser指對(duì)方數(shù)據(jù)庫(kù)用戶(hù)名，touser指你的數(shù)據(jù)庫(kù)的用戶(hù)名；

5.使用oracle的管理端在“表空間”中即可創(chuàng)建；

6.要導(dǎo)入所有的表最方便，不用寫(xiě)tables參數(shù)就成，不需要明白對(duì)方的表名。

下面介紹的是導(dǎo)入導(dǎo)出的實(shí)例。

數(shù)據(jù)的導(dǎo)入

1將D:/daochu.dmp中的數(shù)據(jù)導(dǎo)入TEST數(shù)據(jù)庫(kù)中。

impsystem/manager@TESTfile=d:/daochu.dmp

impaichannel/aichannel@HUSTfull=yfile=file=d:/data/newsmgnt.dmpignore=y

上面可能有點(diǎn)問(wèn)題，由于有的表已經(jīng)存在，然后它就報(bào)錯(cuò)，對(duì)該表就不進(jìn)行導(dǎo)入。

在后面加上ignore=y就能夠了。

2將d:/daochu.dmp中的表table1導(dǎo)入

impsystem/manager@TESTfile=d:/daochu.dmptables=(tablel)

基本上上面的導(dǎo)入導(dǎo)出夠用了。很多情況要先是將表完全刪除，然后導(dǎo)入。

注意：

操作者要有足夠的權(quán)限，權(quán)限不夠它會(huì)提示。

數(shù)據(jù)庫(kù)時(shí)能夠連上的。能夠用tnspingTEST來(lái)獲得數(shù)據(jù)庫(kù)TEST能否連上。

數(shù)據(jù)導(dǎo)出：

1將數(shù)據(jù)庫(kù)TEST完全導(dǎo)出,用戶(hù)名system密碼manager導(dǎo)出到D:/daochu.dmp中

expsystem/manager@TESTfile=d:/daochu.dmpfull=y

2將數(shù)據(jù)庫(kù)中system用戶(hù)與sys用戶(hù)的表導(dǎo)出

expsystem/manager@TESTfile=d:/daochu.dmpowner=(system,sys)

3將數(shù)據(jù)庫(kù)中的表innejnotify、notify_staff_relat導(dǎo)出

expaichannel/aichannel@TESTDB2file=d:/data/newsmgnt.dmp

tables=(inner_notify,notify_staffLrelat)

4將數(shù)據(jù)庫(kù)中的表tablel中的字段filedl以“00”打頭的數(shù)據(jù)導(dǎo)出

expsystem/manager@TESTfile=d:/daochu.dmptables=(tablel)query=/"wherefiledllike

,00%7u

上面是常用的導(dǎo)出，關(guān)于壓縮，既用winzip把dmp文件能夠很好的壓縮。

也能夠在上面命令后面加上compress=y來(lái)實(shí)現(xiàn)。

22.oracle學(xué)習(xí)之路數(shù)據(jù)導(dǎo)入導(dǎo)出expDB/impDB

創(chuàng)建邏輯目錄,該命令不可能在操作系統(tǒng)創(chuàng)建真正的目錄.最好以system等管理員創(chuàng)建.

createdirectorydpdata2asD:\oracle\backup2';

查看管理理員目錄(同時(shí)查看操作系統(tǒng)是否存在，由于Oracle并不關(guān)心該目錄是否存

在,假如不存在,則出錯(cuò).)

select*fromdba_directories;

三.給scott用戶(hù)給予在指定目錄的操作權(quán)限,最好以system等管理員給予.

grantread,writeondirectorydpdata2toaaa;

impdpaaa/aaa@orcldirectory=dpdata2dumpfile=dpdatal.dmpremap_schema=pjbj_chinalife:aaa

remap_tablespace=users:userslogfile=impdp_l11217.log