數據庫oracle學習之路

數據庫oracle學習之路

1.數據庫基礎

2.數據庫主鍵生成

Select

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

Fromdual;

3.數據庫空間查詢

--表空間在什么空間下

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;

--查用戶下所用空間

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

FROMdba_segments

GROUPBYowner,tablespace_name

ORDERBYSUM(BYTES)DESC；

一查用戶下所有表所占空間

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之刪除重復數據

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之查詢數據第一條記錄

select*fromtabrownum<2

6.oracle之存儲過程/函數等書寫規(guī)則

7.oracle之正則表達式函數：regexp」ike、regexp_substr>

regexp_instr>regexp_replace

Oracle使用正則表達式離不開這4個函數：

1。regexpjike

2。regexp_substr

3?regexpjnstr

4。regexp_replace

看函數名稱大概就能猜到有什么用了。

regexpjike只能用于條件表達式，與like類似，但是使用的正則表達式進行匹配，語法

很簡單：

match_paramete)^

—)REGEXP_LIKE

regexp_substr函數，與substr類似，用于拾取合符正則表達式描述的字符子串，語法如

下：

tREGEXPSUBSTR{fy)^sour8char)^7)^網

regexpjnstr函數，與instr類似，用于標定符合正則表達式的字符子串的開始位置，語法

如下：

tREGEXPINSTRXD^sour8chaX7)^pattem>>

|regexpjnstr|mateh_paramete

—3rreturn_optionY>

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

廣(^(position》

1o

regexp_replace函數，與replace類似，用于替換符合正則表達式的字符串，語法如下:

tREGEXP_REPLACE

_______廠match_parameter

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

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

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

\)>

這里解析一下幾個參數的含義:

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

(2opattern,正則表達式。

3omatch_parameter,匹配選項。

取值范圍：i：大小寫不敏感；c：大小寫敏感；n：點號.不匹配換行符號；m：多行模

式；x：擴展模式，忽略正則表達式中的空白字符。

4oposition,標識從第幾個字符開始正則表達式匹配。

5ooccurrence,標識第幾個匹配組。

6。replace_string,替換的字符串。

8.oracle之decode函數

decode函數=java中的true?A:B

decode(參數，1,2,3)參數==1時結果為2否則結果為3

9.oracle之linux下啟動腳本

1)啟動數據庫

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)關閉數據庫：

oracle@suse92:~>sqlplus/nolog

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

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

SQL>connect/assysdba

Connectedtoanidleinstance.

SQL>shutdwonabort;

3）啟動監(jiān)聽器

oracle@suse92:~>Isnrctlstart

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

oracle@suse92:~>Isnrctlstop

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

oracle@suse92:~>Isnrctlstatus

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

10.oracle之備份工作

背景：Oracle10g服務器，Oracle10g客戶端,windowsXP操作平臺

要求：Oracle數據庫服務器對數據庫ytcn每天自動備份一次。

解決方案：利用任務計劃、批處理文件與Oracle的exp導出功能，根據日期自動生成

Oracle備份文件。全面步驟：

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

ytcn.bat中全面內容如下：

@echooff

echo正在備份Oracle數據庫，請稍等……

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任務完成！

其中紅色部分是根據需要進行變動的地方，比如作者的項目名“銀通網”，數據庫ytcn用

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

與"ytcn20090711.log"的備份與日志文件，全表導出。

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

的設定也會改變％date%的值。請先在命令行中測試echo%date%的返回值。%date:~4,10%

是返回日期函數，~后的第一個參數是要截取的起始位置（從0開始），第二個參數是要截取

的長度，如沒有則是截取到最后，參數可酌情修改。如需要準確的時間做為文件名，請

用％time%函數，參數同上。

2）添加一個任務計劃ytcn

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

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

始時間下午12:00,起始日期2009-7-11,下一步>輸入用戶名及密碼，用戶名要求是管理員權

限用戶名,下一步>完成

點擊"完成”之后，會在任務計劃欄目下新增一個名為"ytcn"的任務計劃，說明已經配置完畢。

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

0x80（141315：任務

這是由于電腦的任務計劃程序服務沒有啟動起來。開始>所有程序>管理工具>服務，

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

來，然后重新添加一次任務計劃ytcn就能夠了。

11.oracle之命中率查詢

Buffercache由數據塊構成。

1.Buffercache的工作原理

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

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

DirtyList或者WriteList（寫列表）。

數據庫寫進程DBW0將緩沖區(qū)高速緩存中的數據寫到數據文件中。

2.測量Buffercache的性能

測量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來監(jiān)視Buffercache.

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

非命中率指標：FreeBufferInspected、FreeBufferWaits,BufferBusyWaits.

(V$sysstat)

使用PerformanceManager(數據庫例程)來監(jiān)視BufferCache.

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

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

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

首先將init.ora參數DB_CACHE_ADVICE設成ON,然后查詢V$DB_CACHE_ADVICE.

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

KeepPool：DB_KEEP_CACHE_SIZE

RecyclePool：DB_RECYCLE_CACHE_SIZE

DefaultPool：DBCACHESIZE

在內存中緩存表：表的CACHE選項，對優(yōu)化小表的全表掃描。

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

4.調整LargePool與JAVAPOOL

LargePool用于共享服務器、RMAN、并行查詢、DBWR的從屬進程。

LargePool的大小通過init.ora參數Large_pool_size設置。默認為8M.

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

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

JAVA_POOL池的默認大小為32M,關于大型Java應用程序，JAVA_POOL池的大小應大

于50M.

init.ora參數java_pool_size

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

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

pool1;

調整重做有關的性能

Oracle重做有關的組件包含：RedoLogBuffer>OnlineRedoLog、LGWR、ArchiveLog、

Checkpoint、ArchO.

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

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

當下列事件發(fā)生時，RedoLogBuffer的內容存盤：

Commit時、每3秒、空間使用1/3、達到1M、檢查點。

假如寫入RedoLogBuffer的速度超過LGWR存盤的速度，就會因等待而降低性能。

監(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參數log_buffer.

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

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

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

ALTERINDEXREBUILD.CREATETABELSPACE）。

3.調整檢查點進程的性能

測量檢查點進程的性能：沒有完成的檢查點進程的次數。

select*fromV$system_event；

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

Select*fromV$sysstat.

backgroundcheckpointsstarted與backgroundcheckpointscompleted.

使用Alert11志來記錄檢查點進程：init.ora參數log_checkpoint_to_alert.

使用PerformanceManager來測量檢查點進程的性能：I/O中的平均灰數據隊列長度(假

如為0,說明檢查點太頻繁)。

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

4.調整聯(lián)機重做日志文件

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

logfileparallelwrite、logfileswitchcompleted.

調整聯(lián)機重做日志文件的方法：與數據文件、操縱文件、歸檔日志文件分開，放在原始

設備上。

5.調整歸檔性能

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

事件。

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

創(chuàng)建多個歸檔進程：initora參數LOG_ARCHIVE_MAX_PROCESSES(默認為2

12.oracle之數據庫空間限額

revokeunlimitedtablespacefromccicjy;

alteruserccicjyquota0onJINGYOUTBS;

alteruserccicjyquotaunlimitedonJYSPACE;

空間轉換

13.oracle之查詢表空間文件所在路徑

select*fromdba_data_fil.es;

14.oracle之物化視圖學習

一、環(huán)境

數據庫1：

數據庫：oralceORACLE.0RAC

基表用戶：WEB_USER

查詢用戶：MVLOG_USER

MVLOGJJSER權限如下：

grantcommentanytabletoMVLOG_USER;

grantcreateanytabletoMVLOG_USER;

grantcreatematerializedviewtoMVLOG_USER;

數據庫2

數據庫：oralceORACLE.0RAC

物化視圖用戶：QUERY_USER

網絡環(huán)境：10M光纖

目的：將數據庫1中WEB_USER用戶下的表，同步到數據庫2的QUERYJJSER用戶下，為確保

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

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

CREATEMATERIALIZEDVIEWLOG

ONWEB_USER.ZFZZB

TABLESPACETS_MVLOG；

--將對表及表日志查詢權限給mvlog_userfflpo

grantselectonWEB_USER.ZFZZBtomvlog_user;

grantselectonWEB_USER.mlog$_ZFZZBtomvlog_user;

三、創(chuàng)建物化視圖(數據庫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學習之位置查詢

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學習物化視圖

物化視圖，它是用于預先計算并儲存表連接或者聚集等耗時較多的操作的結果，這樣，在執(zhí)行查詢時，

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

圖的目的是為了提高查詢性能：物化視圖對應用透明，增加與刪除物化視圖不可能影響應用程序中SQL語

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

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

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

緊選項如下：

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

化視圖的時候就生成數據，而BuildDeferred則在創(chuàng)建時不生成數據，以后根據需要在生成數據。默認為

BuildImmediate.

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

物化視圖是否支持查詢重寫。查詢重寫是指當對物化視圖的基表進行查詢時，Oracle會自動推斷能否通過

查詢物化視圖來得到結果，假如能夠，則避免了聚集或者連接操作，而直接從已經計算好的物化視圖中讀

取數據。默認為DisableQueryRewrite。

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

的模式有兩種：OnDemand與OnCommit。OnDemand指物化視圖在用戶需要的時候進行刷新，能夠手

工通過DBMS_MVIEW.REFRESH等方法來進行刷新，也能夠通過JOB定時進行刷新。OnCommit指物

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

Fast刷新使用增量刷新，只刷新自上次刷新以后進行的修改。Complete刷新對整個物化視圖進行完全的

刷新。假如選擇Force方式，則Oracle在刷新時會去推斷是否能夠進行快速刷新，假如能夠則使用FAST

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

在建立物化視圖的時候能夠指定。rderby語句，使生成的數據按照一定的順序進行儲存。只是這個語句不

可能寫入物化視圖的定義中，而且對以后的刷新也無效。

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

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

NewValues與指定列的列表。能夠指明OnPreBuildTable語句將物化視圖建立在一個已經存在的

表上。這種情況下，物化視圖與表務必同名。當刪除物化視圖時，不可能刪除同名的表。這種物化視圖的

查詢重寫要求參數Query_Rewrite_integerity務必設置為trusted或者者stale_toleratedo

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

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

列表中使用Cube或者RollUp,來建立不?致等級的聚集物化視圖。

示例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)建物化視圖時創(chuàng)建存儲的日志空間

（存儲物化視圖的對象的改變信息）CREATEMATERIALIZEDVIEWLOGON

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

WITHROWID;dropmaterializedviewMV_materialized_view_name;

示例2:creatematerializedviewMV_DOP_TESTTABLESPACEifsapp_DATA--儲存表空間

BUILDDEFERRED-延遲刷新不立即刷新,此建立初始視圖通常沒數據refreshforce-假如能夠快速

刷新則進行快速刷新，否則完全刷新withrowid-根據rowid刷新（默認是主鍵）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

其他

一、關于物化視圖日志：查詢物化視圖日志文件格式：descmlog$」zwmvtest;創(chuàng)建物化視圖時默認指定

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

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

條件這種情況下，就務必指定日志文件結構比如：creatematerializedviewlogontestwithrowid（具體的

針對日志內容方面的在另外一個專題里說明，這里就簡述到此）

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

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

如如今沒有生成數據，以后能夠采?。篍XECDBMS_MVIEW.Refresh（＜MV_name,/C）,注意務必使用全

量刷新，默認是增量刷新，因此這里參數務必是C,由于之前都沒有生成數據，因此務必全量。2＞關于刷

新2刷新方式：completefastforceComplete:完全刷新整個物化視圖，相當于重新生成物化視圖，如今即

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

化視圖日志（物化視圖日志記錄了數據更新的日志），關于日志的說明，參照“物化視圖日志文件介紹”）0

Force:當增量刷新可用則增量刷新，當增量刷新不可用，則全量刷新（此項為默認選項）只是從實際情況

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

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

法使用，這個是需要注意的，具體是哪類語句導致fast刷新不可用，有待總結…尹刷新時間：ondemand

oncommitstartwith/nextOndemand:在需要刷新時進行刷新（人工推斷）0ncommit:在基表上有提交操

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

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

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

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

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

rowid來更新，則會報ORA-12032:不能使用"TEST"上實體化視圖日志中的rowid歹42、如過日志內使

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

實體化視圖日志不記錄主鍵

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

子：creatematerializedviewMV_TESTtablespacetest--表空間名稱

五、關于查詢重寫與更新在創(chuàng)建查詢重寫時，基表中務必有主鍵約束，視圖里是無法創(chuàng)建主鍵的，只是其

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

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

表上務必存在主鍵約束AsSelect*fromTEST;--物化視圖數據生成

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

CreatematerializedviewlogonTEST--TEST為表名--注：（TEST為表名或者者視圖名，關于視圖上

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

--MVTEST為物化視圖名Buildimmediate----創(chuàng)建時生成數據對應的是builddeferredRefreshfast--增

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

化視圖，對應的是primarykeyAsSelect*fromTEST;一生成物化視圖數據語句

17.oracle學習之linux下啟動停止服務

linux下的oracle從10g以后其啟動與關閉與往常版本是完全不一致的

9i之后已經沒有svrmgrl了，所有的管理工作都通過sqlplus來完成

啟動數據庫步驟如下：

注：$ORACLEHOME為oracle的安裝路徑

1,以oracle用戶登錄

suoracle

2,啟動TNS監(jiān)聽器

$ORACLE_HOME/bin/Isnrctlstart

3,用sqlplus啟動數據庫

$0RACLE_HOME/bin/sqlplus/nolog

SQL>connectsystem/changeoninstallassysdba

SQL>startup

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

ORACLEinstancestarted.

TotalSystemGlobalArea205520896bytes

FixedSize778392bytes

VariableSize74456936bytes

DatabaseBuffers130023424bytes

RedoBuffers262144bytes

Databasemounted.

Databaseopened.

4,用sqlplus停止數據庫

$ORACLE_IIOME/bin/sqlplus/nolog

SQL>connectsystem/changeoninstallassysdba

SQL>shutdown

注：shutdown可加關閉選項，從最溫與到最粗暴的行為選項為(shutdown、shutdown

transactional>shutdownimmediate>shutdownabort)

命令解釋如下

shutdown:關閉，等待每個用戶退出系統(tǒng)或被取消后退出關閉數據庫。

shutdowntransactional:事務性關閉，等待每個用戶提交或回退當前的事務，然后oracle

取消對話，在所有用戶退出系統(tǒng)后執(zhí)行關閉。

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

shutdownabort:終止關閉，關閉數據庫時沒有自動檢查點或日志開關。

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

Databaseclosed.

Databasedismounted.

ORACLEinstanceshutdown.

18.oracle學習之rank函數

select*

From(selectrank()over(partitionbyt.車輛品牌，t.車輛型號orderbyto_number(數字)desc,

rownum)rr,

t.*

fromt_sjt)tt

wherett.rr=1

說明：t.車輛品牌，t.車輛型號唯一篩選

說明：to_mimber（數字）desc數值排序

說明：tt.rr=1取num第一個

19.oracle學習之JOB

初始化有關參數job_queue_processes

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

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

job_queue_process友示oracle能夠并發(fā)的iob的數量,能夠通過語句

showparameterjob_queue_process;

來查看oracle中job_queuejDrocess的值。當job_queue_process值為0時表示全部停止oracle的job,

能夠通過語句

ALTERSYSTEMSETjob_queuejDrocesses=10;

來調整啟動oracle的job。

有關視圖：

dbajobs

allJobs

userjobs

dbajobs_running包含正在運行job有關信息

提交job語法：

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;

運行JOB

SQL>begin

dbmsjob.run(:job1);

end;

/

刪除JOB

SQL>begin

dbmsjob.remove(:job1);

end;

DBA_JOBS

字段(列)類型描述

JOBNUMBER任務的唯一標示號

LOG_USERVARCHAR2(30)提交任務的用戶

PRIV_USERVARCHAR2(30)給予任務權限的用戶

SCHEMA_USERVARCHAR2(30)對任務作語法分析的用戶模式

LAST_DATEDATE最后一次成功運行任務的時間

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

THIS_DATEDATE正在運行任務的開始時間，假如沒有運行任務則為null

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

NEXT_DATEDATE下一次定時運行任務的時間

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

TOTAL_TIMENUMBER該任務運行所需要的總時間，單位為秒

BROKENVARCHAR2⑴標志參數，Y標示任務中斷，以后不可能運行

INTERVALVARCHAR2(200)用于計算下一運行時間的表達式

FAILURESNUMBER任務運行連續(xù)沒有成功的次數

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

CURRENT_SESSION_LABELRAWMLSLABEL該任務的信任Oracle會話符

CLEARANCE_HIRAWMLSLABEL該任務可信任的Oracle最大間隙

CLEARANCE_LORAWMLSLABEL該任務可信任的Oracle最小間隙

NLS_ENVVARCHAR2(2000)任務運行的NLS會話設置

MISC_ENVRAW(32)任務運行的其他一些會話參數

描述INTERVAL參數值

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

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

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

每個月第一天的午夜12點'TRUNC(LAST_DAY(SVSDATE)+1)'

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

每星期六與日早上6點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:每天定時執(zhí)行

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

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

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

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

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

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

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

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

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

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

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

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

比如：每年7月1日與1月1日凌晨1點

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

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

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

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

20.oracle學習之路索引查詢

查找表的所有索引(包含索引名，類型，構成列)：

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

i.index_nameandt.table_name=i.table_name

查找表的主鍵(包含名稱，構成列)：

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學習之路oracle中導入dmp數據庫文件

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

tables=(XXX,XXX)

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

touser參數仿fromuser參數；

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

tables參數

補充：

1.要新建一個數據庫；

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

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

3.username/password指的是你的數據庫的登錄用戶名與密碼；

4.fromuser指對方數據庫用戶名，touser指你的數據庫的用戶名；

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

6.要導入所有的表最方便，不用寫tables參數就成，不需要明白對方的表名。

下面介紹的是導入導出的實例。

數據的導入

1將D:/daochu.dmp中的數據導入TEST數據庫中。

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

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

上面可能有點問題，由于有的表已經存在，然后它就報錯，對該表就不進行導入。

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

2將d:/daochu.dmp中的表table1導入

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

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

注意：

操作者要有足夠的權限，權限不夠它會提示。

數據庫時能夠連上的。能夠用tnspingTEST來獲得數據庫TEST能否連上。

數據導出：

1將數據庫TEST完全導出,用戶名system密碼manager導出到D:/daochu.dmp中

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

2將數據庫中system用戶與sys用戶的表導出

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

3將數據庫中的表innejnotify、notify_staff_relat導出

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

tables=(inner_notify,notify_staffLrelat)

4將數據庫中的表tablel中的字段filedl以“00”打頭的數據導出

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

,00%7u

上面是常用的導出，關于壓縮，既用winzip把dmp文件能夠很好的壓縮。

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

22.oracle學習之路數據導入導出expDB/impDB

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

createdirectorydpdata2asD:\oracle\backup2';

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

在,假如不存在,則出錯.)

select*fromdba_directories;

三.給scott用戶給予在指定目錄的操作權限,最好以system等管理員給予.

grantread,writeondirectorydpdata2toaaa;

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

remap_tablespace=users:userslogfile=impdp_l11217.log