亚洲色偷偷偷鲁综合,久久亚洲精品成人av无码网站,亚洲精品无码国产片

解決ORA-12560: TNS: 協(xié)議適配器錯(cuò)誤

造成ORA-12560: TNS: 協(xié)議適配器錯(cuò)誤的問(wèn)題的原因可能有如下三個(gè)：
1.監(jiān)聽服務(wù)沒(méi)有起起來(lái)。windows平臺(tái)個(gè)一如下操作：開始---程序---管理工具---服務(wù)，打開服務(wù)面板，

啟動(dòng)oraclehome92TNSlistener服務(wù)。
2.database instance沒(méi)有起起來(lái)。windows平臺(tái)如下操作：開始---程序---管理工具---服務(wù)，打開服務(wù)

面板，啟動(dòng)oracleserviceXXXX,XXXX就是你的database SID.
3.注冊(cè)表問(wèn)題。regedit，然后進(jìn)入HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0將該環(huán)境變量ORACLE_SI

D設(shè)置為XXXX,XXXX就是你的database SID.或者右?guī)孜业碾娔X，屬性--高級(jí)--環(huán)境變量---系統(tǒng)變量--新建

，變量名=oracle_sid,變量值=XXXX,XXXX就是你的database SID.或者進(jìn)入sqlplus前，在command line下

輸set oracle_sid=XXXX,XXXX就是你的database SID.
經(jīng)過(guò)以上步驟，就可以解決問(wèn)題。

posted @ 2014-05-05 10:46 鴻雁閱讀(171) | 評(píng)論 (0) | 編輯收藏

Oracle優(yōu)化全攻略一（Oracle SQL Hint）

其實(shí)Oracle的優(yōu)化器有兩種優(yōu)化方式,
基于規(guī)則的優(yōu)化方式(Rule-Based Optimization，簡(jiǎn)稱為RBO)
基于代價(jià)的優(yōu)化方式(Cost-Based Optimization，簡(jiǎn)稱為CBO)
所以hint也不例外，除了/*+rule*/其他的都是CBO優(yōu)化方式
RBO方式：
優(yōu)化器在分析SQL語(yǔ)句時(shí),所遵循的是Oracle內(nèi)部預(yù)定的一些規(guī)則。比如我們常見(jiàn)的，當(dāng)一個(gè)where子句中的一列有索引時(shí)去走索引。
CBO方式：
   它是看語(yǔ)句的代價(jià)(Cost),這里的代價(jià)主要指Cpu和內(nèi)存。優(yōu)化器在判斷是否用這種方式時(shí),主要參照的是表及索引的統(tǒng)計(jì)信息。統(tǒng)計(jì)信息給出表的大小、有少行、每行的長(zhǎng)度等信息。這些統(tǒng)計(jì)信息起初在庫(kù)內(nèi)是沒(méi)有的，是做analyze后才出現(xiàn)的，很多的時(shí)侯過(guò)期統(tǒng)計(jì)信息會(huì)令優(yōu)化器做出一個(gè)錯(cuò)誤的執(zhí)行計(jì)劃,因些應(yīng)及時(shí)更新這些信息。

優(yōu)化模式包括Rule、Choose、First rows、All rows四種方式：

    Rule：基于規(guī)則的方式。

    Choolse：默認(rèn)的情況下Oracle用的便是這種方式。指的是當(dāng)一個(gè)表或或索引有統(tǒng)計(jì)信息，則走CBO的方式，如果表或索引沒(méi)統(tǒng)計(jì)信息，表又不是特別的小，而且相應(yīng)的列有索引時(shí)，那么就走索引，走RBO的方式。

    First Rows：它與Choose方式是類似的，所不同的是當(dāng)一個(gè)表有統(tǒng)計(jì)信息時(shí)，它將是以最快的方式返回查詢的最先的幾行，從總體上減少了響應(yīng)時(shí)間。

    All Rows:也就是我們所說(shuō)的Cost的方式，當(dāng)一個(gè)表有統(tǒng)計(jì)信息時(shí)，它將以最快的方式返回表的所有的行，從總體上提高查詢的吞吐量。沒(méi)有統(tǒng)計(jì)信息則走RBO的方式

Oracle在那配置默認(rèn)的優(yōu)化規(guī)則
    A、Instance級(jí)別我們可以通過(guò)在initSID.ora文件中設(shè)定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS如果沒(méi)設(shè)定OPTIMIZER_MODE參數(shù)則默認(rèn)用的是Choose方式。
    B、Sessions級(jí)別通過(guò)ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS來(lái)設(shè)定。
    C、語(yǔ)句級(jí)別用Hint（/*+ ... */）來(lái)設(shè)定
為什么表的某個(gè)字段明明有索引，但執(zhí)行計(jì)劃卻不走索引？
    1、優(yōu)化模式是all_rows的方式
    2、表作過(guò)analyze，有統(tǒng)計(jì)信息
    3、表很小，Oracle的優(yōu)化器認(rèn)為不值得走索引。
提示
   不區(qū)分大小寫, 多個(gè)提示用空格分開
如：select /*+ hint1(tab1) hint2(TAB1 idx1) */ col1, col2 from tab1 where col1='xxx';
如果表使用了別名, 那么提示里也必須使用別名
如：select /*+ hint1(t1) */ col1, col2 from tab1 t1 where col1='xxx';
如果使用同一個(gè)表的多個(gè)用,號(hào)分開
如： select /*+ index(t1.A,t1.B) */ col1, col2
    from   tab1 t1
    where col1='xxx';

oracle 10g hints知識(shí),
    10g數(shù)據(jù)庫(kù)可以使用更多新的optimizer hints來(lái)控制優(yōu)化行為?，F(xiàn)在讓我們快速解析一下這些強(qiáng)大的新hints：

1、spread_min_analysis

   使用這一hint，你可以忽略一些關(guān)于如詳細(xì)的關(guān)系依賴圖分析等電子表格的編譯時(shí)間優(yōu)化規(guī)則。其他的一些優(yōu)化，如創(chuàng)建過(guò)濾以有選擇性的定位電子表格訪問(wèn)結(jié)構(gòu)并限制修訂規(guī)則等，得到了繼續(xù)使用。

   由于在規(guī)則數(shù)非常大的情況下，電子表格分析會(huì)很長(zhǎng)。這一提示可以幫助我們減少由此產(chǎn)生的數(shù)以百小時(shí)計(jì)的編譯時(shí)間。

例：
    SELECT /*+ SPREAD_MIN_ANALYSIS */ ...

2、spread_no_analysis

   通過(guò)這一hint，可以使無(wú)電子表格分析成為可能。同樣，使用這一hint可以忽略修訂規(guī)則和過(guò)濾產(chǎn)生。如果存在一電子表格分析，編譯時(shí)間可以被減少到最低程度。

例：
    SELECT /*+ SPREAD_NO_ANALYSIS */ ...

3、use_nl_with_index

   這項(xiàng)hint使CBO通過(guò)嵌套循環(huán)把特定的表格加入到另一原始行。只有在以下情況中，它才使用特定表格作為內(nèi)部表格：如果沒(méi)有指定標(biāo)簽，CBO必須可以使用一些標(biāo)簽，且這些標(biāo)簽至少有一個(gè)作為索引鍵值加入判斷；反之，CBO必須能夠使用至少有一個(gè)作為索引鍵值加入判斷的標(biāo)簽。

例：
SELECT /*+ USE_NL_WITH_INDEX (polrecpolrind) */ ...

4、CARDINALITY

此hint定義了對(duì)由查詢或查詢部分返回的基數(shù)的評(píng)價(jià)。注意如果沒(méi)有定義表格，基數(shù)是由整個(gè)查詢所返回的總行數(shù)。

例：
SELECT /*+ CARDINALITY ( [tablespec] card ) */

5、SELECTIVITY

此hint定義了對(duì)查詢或查詢部分選擇性的評(píng)價(jià)。如果只定義了一個(gè)表格，選擇性是在所定義表格里滿足所有單一表格判斷的行部分。如果定義了一系列表格，選擇性是指在合并以任何順序滿足所有可用判斷的全部表格后，所得結(jié)果中的行部分。

例：
   SELECT /*+ SELECTIVITY ( [tablespec] sel ) */

然而，注意如果hints CARDINALITY 和 SELECTIVITY都定義在同樣的一批表格，二者都會(huì)被忽略。

6、no_use_nl

Hint no_use_nl使CBO執(zhí)行循環(huán)嵌套，通過(guò)把指定表格作為內(nèi)部表格，把每個(gè)指定表格連接到另一原始行。通過(guò)這一hint，只有hash join和sort-merge joins會(huì)為指定表格所考慮。

例：
   SELECT /*+ NO_USE_NL ( employees ) */ ...

7、no_use_merge

此hint使CBO通過(guò)把指定表格作為內(nèi)部表格的方式，拒絕sort-merge把每個(gè)指定表格加入到另一原始行。

例：
SELECT /*+ NO_USE_MERGE ( employees dept ) */ ...

8、no_use_hash

此hint使CBO通過(guò)把指定表格作為內(nèi)部表格的方式，拒絕hash joins把每個(gè)指定表格加入到另一原始行。

例：
SELECT /*+ NO_USE_HASH ( employees dept ) */ ...

9、no_index_ffs

此hint使CBO拒絕對(duì)指定表格的指定標(biāo)簽進(jìn)行fast full-index scan。
Syntax: /*+ NO_INDEX_FFS ( tablespecindexspec ) */

在SQL優(yōu)化過(guò)程中常見(jiàn)HINT的用法(前10個(gè)比較常用, 前3個(gè)最常用)：

1. /*+ INDEX */ 和 /*+ INDEX(TABLE INDEX1, index2) */ 和 /*+ INDEX(tab1.col1 tab2.col2) */ 和 /*+ NO_INDEX */ 和 /*+ NO_INDEX(TABLE INDEX1, index2) */

表明對(duì)表選擇索引的掃描方法. 第一種不指定索引名是讓oracle對(duì)表中可用索引比較并選擇某個(gè)最佳索引; 第二種是指定索引名且可指定多個(gè)索引; 第三種是10g開始有的, 指定列名, 且表名可不用別名; 第四種即全表掃描; 第五種表示禁用某個(gè)索引, 特別適合于準(zhǔn)備刪除某個(gè)索引前的評(píng)估操作. 如果同時(shí)使用了INDEX和NO_INDEX則兩個(gè)提示都會(huì)被忽略掉.
例如:SELECT /*+ INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

2. /*+ ORDERED */
FROM子句中默認(rèn)最后一個(gè)表是驅(qū)動(dòng)表,ORDERED將from子句中第一個(gè)表作為驅(qū)動(dòng)表. 特別適合于多表連接非常慢時(shí)嘗試.
例如:SELECT /*+ ORDERED */ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

3. /*+ PARALLEL(table1,DEGREE) */ 和 /*+ NO_PARALLEL(table1) */
該提示會(huì)將需要執(zhí)行全表掃描的查詢分成多個(gè)部分（并行度）執(zhí)行, 然后在不同的操作系統(tǒng)進(jìn)程中處理每個(gè)部分. 該提示還可用于DML語(yǔ)句. 如果SQL里還有排序操作, 進(jìn)程數(shù)會(huì)翻倍,此外還有一個(gè)一個(gè)負(fù)責(zé)組合這些部分的進(jìn)程,如下面的例子會(huì)產(chǎn)生9個(gè)進(jìn)程. 如果在提示中沒(méi)有指定DEGREE, 那么就會(huì)使用創(chuàng)建表時(shí)的默認(rèn)值. 該提示在默認(rèn)情況下會(huì)使用APPEND提示. NO_PARALLEL是禁止并行操作,否則語(yǔ)句會(huì)使用由于定義了并行對(duì)象而產(chǎn)生的并行處理.
例如:select /*+ PARALLEL(tab_test,4) */ col1, col2 from tab_test order by col2;

4. /*+ FIRST_ROWS */ 和 /*+ FIRST_ROWS(n) */
表示用最快速度獲得第1/n行, 獲得最佳響應(yīng)時(shí)間, 使資源消耗最小化.
在update和delete語(yǔ)句里會(huì)被忽略, 使用分組語(yǔ)句如group by/distinct/intersect/minus/union時(shí)也會(huì)被忽略.
例如:SELECT /*+ FIRST_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

5. /*+ RULE */
表明對(duì)語(yǔ)句塊選擇基于規(guī)則的優(yōu)化方法.
例如:SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

6. /*+ FULL(TABLE) */
表明對(duì)表選擇全局掃描的方法.
例如:SELECT /*+ FULL(A) */ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

7. /*+ LEADING(TABLE) */
類似于ORDERED提示, 將指定的表作為連接次序中的驅(qū)動(dòng)表.

8. /*+ USE_NL(TABLE1,TABLE2) */
將指定表與嵌套的連接的行源進(jìn)行連接,以最快速度返回第一行再連接,與USE_MERGE剛好相反.
例如:SELECT /*+ ORDERED USE_NL(BSEMPMS) */ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

9. /*+ APPEND */ 和 /*+ NOAPPEND */
直接插入到表的最后,該提示不會(huì)檢查當(dāng)前是否有插入操作所需的塊空間而是直接添加到新塊中, 所以可以提高速度. 當(dāng)然也會(huì)浪費(fèi)些空間, 因?yàn)樗粫?huì)使用那些做了delete操作的塊空間. NOAPPEND提示則相反,所以會(huì)取消PARALLEL提示的默認(rèn)APPEND提示.
例如:insert /*+ append */ into test1 select * from test4;
insert /*+ parallel(test1) noappend */ into test1 select * from test4;

10. /*+ USE_HASH(TABLE1,table2) */
將指定的表與其它行源通過(guò)哈希連接方式連接起來(lái).為較大的結(jié)果集提供最佳響應(yīng)時(shí)間. 類似于在連接表的結(jié)果中遍歷每個(gè)表上每個(gè)結(jié)果的嵌套循環(huán), 指定的hash表將被放入內(nèi)存, 所以需要有足夠的內(nèi)存（hash_area_size或pga_aggregate_target）才能保證語(yǔ)句正確執(zhí)行, 否則將在磁盤里進(jìn)行.
例如:SELECT /*+ USE_HASH(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

---------------------------------------------------------------------

11. /*+ USE_MERGE(TABLE) */
將指定的表與其它行源通過(guò)合并排序連接方式連接起來(lái).特別適合于那種在多個(gè)表大量行上進(jìn)行集合操作的查詢, 它會(huì)將指定表檢索到的的所有行排序后再被合并, 與USE_NL剛好相反.
例如:SELECT /*+ USE_MERGE(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

12. /*+ ALL_ROWS */
表明對(duì)語(yǔ)句塊選擇基于開銷的優(yōu)化方法,并獲得最佳吞吐量,使資源消耗最小化. 可能會(huì)限制某些索引的使用.
例如:SELECT /*+ ALL+_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

13. /*+ CLUSTER(TABLE) */
提示明確表明對(duì)指定表選擇簇掃描的訪問(wèn)方法. 如果經(jīng)常訪問(wèn)連接表但很少修改它, 那就使用集群提示.
例如:SELECT /*+ CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

14. /*+ INDEX_ASC(TABLE INDEX1, INDEX2) */
表明對(duì)表選擇索引升序的掃描方法. 從8i開始, 這個(gè)提示和INDEX提示功能一樣, 因?yàn)槟J(rèn)oracle就是按照升序掃描索引的, 除非未來(lái)oracle還推出降序掃描索引.
例如:SELECT /*+ INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

15. /*+ INDEX_COMBINE(TABLE INDEX1, INDEX2) */
指定多個(gè)位圖索引, 對(duì)于B樹索引則使用INDEX這個(gè)提示,如果INDEX_COMBINE中沒(méi)有提供作為參數(shù)的索引,將選擇出位圖索引的布爾組合方式.
例如:SELECT /*+ INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI) */ * FROM BSEMPMS WHERE SAL<5000000 AND HIREDATE<SYSDATE;

16. /*+ INDEX_JOIN(TABLE INDEX1, INDEX2) */
合并索引, 所有數(shù)據(jù)都已經(jīng)包含在這兩個(gè)索引里, 不會(huì)再去訪問(wèn)表, 比使用索引并通過(guò)rowid去掃描表要快5倍.
例如:SELECT /*+ INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI) */ SAL,HIREDATE FROM BSEMPMS WHERE SAL<60000;

17. /*+ INDEX_DESC(TABLE INDEX1, INDEX2) */
表明對(duì)表選擇索引降序的掃描方法.
例如:SELECT /*+ INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

18. /*+ INDEX_FFS(TABLE INDEX_NAME) */
對(duì)指定的表執(zhí)行快速全索引掃描,而不是全表掃描的辦法.要求要檢索的列都在索引里, 如果表有很多列時(shí)特別適用該提示.
例如:SELECT /*+ INDEX_FFS(BSEMPMS IN_EMPNAM) */ * FROM BSEMPMS WHERE DPT_NO='TEC305';

19. /*+ NO_EXPAND */
對(duì)于WHERE后面的OR 或者IN-LIST的查詢語(yǔ)句,NO_EXPAND將阻止其基于優(yōu)化器對(duì)其進(jìn)行擴(kuò)展, 縮短解析時(shí)間.
例如:SELECT /*+ NO_EXPAND */ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

20. /*+ DRIVING_SITE(TABLE) */
強(qiáng)制與ORACLE所選擇的位置不同的表進(jìn)行查詢執(zhí)行.特別適用于通過(guò)dblink連接的遠(yuǎn)程表.
例如:SELECT /*+ DRIVING_SITE(DEPT) */ * FROM BSEMPMS,DEPT@BSDPTMS DEPT WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

21. /*+ CACHE(TABLE) */ 和 /*+ NOCACHE(TABLE) */
當(dāng)進(jìn)行全表掃描時(shí),CACHE提示能夠?qū)⒈砣烤彺娴絻?nèi)存中,這樣訪問(wèn)同一個(gè)表的用戶可直接在內(nèi)存中查找數(shù)據(jù). 比較適合數(shù)據(jù)量小但常被訪問(wèn)的表, 也可以建表時(shí)指定cache選項(xiàng)這樣在第一次訪問(wèn)時(shí)就可以對(duì)其緩存. NOCACHE則表示對(duì)已經(jīng)指定了CACHE選項(xiàng)的表不進(jìn)行緩存.
例如:SELECT /*+ FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

22. /*+ PUSH_SUBQ */
當(dāng)SQL里用到了子查詢且返回相對(duì)少的行時(shí), 該提示可以盡可能早對(duì)子查詢進(jìn)行評(píng)估從而改善性能, 不適用于合并連接或帶遠(yuǎn)程表的連接.
例如:select /*+ PUSH_SUBQ */ emp.empno, emp.ename, itemno from emp, orders where emp.empno = orders.empno and emp.deptno = (select deptno from dept where loc='XXX');
遠(yuǎn)程連接其他數(shù)據(jù)庫(kù)，注意判斷數(shù)據(jù)庫(kù)是否啟動(dòng)，或者是否有需要的表，否則會(huì)出錯(cuò)

23. /*+ INDEX_SS(TABLE INDEX1,INDEX2) */
指示對(duì)特定表的索引使用跳躍掃描, 即當(dāng)組合索引的第一列不在where子句中時(shí), 讓其使用該索引
參考資料
Oracle SQL hints
/*+ hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */
All hints except /*+ rule */ cause the CBO to be used. Therefore, it is good practise to analyze the underlying tables if hints are used (or the query is fully hinted. There should be no schema names in hints. Hints must use aliases if alias names are used for table names. So the following is wrong:
select /*+ index(scott.emp ix_emp) */ from scott.emp emp_alias
better:
select /*+ index(emp_alias ix_emp) */ ... from scott.emp emp_alias
Why using hints
It is a perfect valid question to ask why hints should be used. Oracle comes with an optimizer that promises to optimize a query's execution plan. When this optimizer is really doing a good job, no hints should be required at all. Sometimes, however, the characteristics of the data in the database are changing rapidly, so that the optimizer (or more accuratly, its statistics) are out of date. In this case, a hint could help. It must also be noted, that Oracle allows to lock the statistics when they look ideal which should make the hints meaningless again.
Hint categories
Hints can be categorized as follows:
Hints for Optimization Approaches and Goals,
Hints for Access Paths, Hints for Query Transformations,
Hints for Join Orders,
Hints for Join Operations,
Hints for Parallel Execution,
Additional Hints

Documented Hints
Hints for Optimization Approaches and Goals
ALL_ROWS
One of the hints that 'invokes' the Cost based optimizer
ALL_ROWS is usually used for batch processing or data warehousing systems.
FIRST_ROWS
One of the hints that 'invokes' the Cost based optimizer
FIRST_ROWS is usually used for OLTP systems.
CHOOSE
One of the hints that 'invokes' the Cost based optimizer
This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered.
RULE
The RULE hint should be considered deprecated as it is dropped from Oracle9i2.
See also the following initialization parameters: optimizer_mode, optimizer_max_permutations, optimizer_index_cost_adj, optimizer_index_caching and
Hints for Access Paths
CLUSTER
Performs a nested loop by the cluster index of one of the tables.
FULL
Performs full table scan.
HASH
Hashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.
ROWID
Retrieves the row by rowid
INDEX
Specifying that index index_name should be used on table tab_name: /*+ index (tab_name index_name) */
Specifying that the index should be used the the CBO thinks is most suitable. (Not always a good choice).
Starting with Oracle 10g, the index hint can be described: /*+ index(my_tab my_tab(col_1, col_2)) */. Using the index on my_tab that starts with the columns col_1 and col_2.
INDEX_ASC
INDEX_COMBINE
INDEX_DESC
INDEX_FFS
INDEX_JOIN
NO_INDEX
AND_EQUAL
The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes

Hints for Query Transformations
FACT
The FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should be considered as a fact table.
MERGE
NO_EXPAND
NO_EXPAND_GSET_TO_UNION
NO_FACT
NO_MERGE
NOREWRITE
REWRITE
STAR_TRANSFORMATION
USE_CONCAT

Hints for Join Operations
DRIVING_SITE
HASH_AJ
HASH_SJ
LEADING
MERGE_AJ
MERGE_SJ
NL_AJ
NL_SJ
USE_HASH
USE_MERGE
USE_NL

Hints for Parallel Execution
NOPARALLEL
PARALLEL
NOPARALLEL_INDEX
PARALLEL_INDEX
PQ_DISTRIBUTE

Additional Hints
ANTIJOIN
APPEND
If a table or an index is specified with nologging, this hint applied with an insert statement produces a direct path insert which reduces generation of redo.
BITMAP
BUFFER
CACHE
CARDINALITY
CPU_COSTING
DYNAMIC_SAMPLING
INLINE
MATERIALIZE
NO_ACCESS
NO_BUFFER
NO_MONITORING
NO_PUSH_PRED
NO_PUSH_SUBQ
NO_QKN_BUFF
NO_SEMIJOIN
NOAPPEND
NOCACHE
OR_EXPAND
ORDERED
ORDERED_PREDICATES
PUSH_PRED
PUSH_SUBQ
QB_NAME
RESULT_CACHE (Oracle 11g)
SELECTIVITY
SEMIJOIN
SEMIJOIN_DRIVER
STAR
The STAR hint forces a star query plan to be used, if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least three tables, the large table's concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.
SWAP_JOIN_INPUTS
USE_ANTI
USE_SEMI

Undocumented hints:
BYPASS_RECURSIVE_CHECK
Workaraound for bug 1816154
BYPASS_UJVC
CACHE_CB
CACHE_TEMP_TABLE
CIV_GB
COLLECTIONS_GET_REFS
CUBE_GB
CURSOR_SHARING_EXACT
DEREF_NO_REWRITE
DML_UPDATE
DOMAIN_INDEX_NO_SORT
DOMAIN_INDEX_SORT
DYNAMIC_SAMPLING
DYNAMIC_SAMPLING_EST_CDN
EXPAND_GSET_TO_UNION
FORCE_SAMPLE_BLOCK
GBY_CONC_ROLLUP
GLOBAL_TABLE_HINTS
HWM_BROKERED
IGNORE_ON_CLAUSE
IGNORE_WHERE_CLAUSE
INDEX_RRS
INDEX_SS
INDEX_SS_ASC
INDEX_SS_DESC
LIKE_EXPAND
LOCAL_INDEXES
MV_MERGE
NESTED_TABLE_GET_REFS
NESTED_TABLE_SET_REFS
NESTED_TABLE_SET_SETID
NO_FILTERING
NO_ORDER_ROLLUPS
NO_PRUNE_GSETS
NO_STATS_GSETS
NO_UNNEST
NOCPU_COSTING
OVERFLOW_NOMOVE
PIV_GB
PIV_SSF
PQ_MAP
PQ_NOMAP
REMOTE_MAPPED
RESTORE_AS_INTERVALS
SAVE_AS_INTERVALS
SCN_ASCENDING
SKIP_EXT_OPTIMIZER
SQLLDR
SYS_DL_CURSOR
SYS_PARALLEL_TXN
SYS_RID_ORDER
TIV_GB
TIV_SSF
UNNEST
USE_TTT_FOR_GSETS

posted @ 2014-05-04 21:21 鴻雁閱讀(216) | 評(píng)論 (0) | 編輯收藏

Oracle 執(zhí)行計(jì)劃（Explain Plan）說(shuō)明

摘要: 如果要分析某條SQL的性能問(wèn)題，通常我們要先看SQL的執(zhí)行計(jì)劃，看看SQL的每一步執(zhí)行是否存在問(wèn)題。如果一條SQL平時(shí)執(zhí)行的好好的，卻有一天突然性能很差，如果排除了系統(tǒng)資源和阻塞的原因，那么基本可以斷定是執(zhí)行計(jì)劃出了問(wèn)題。 &nb... 閱讀全文

posted @ 2014-05-03 11:52 鴻雁閱讀(267) | 評(píng)論 (0) | 編輯收藏

使用EXPLAIN PLAN獲取SQL語(yǔ)句執(zhí)行計(jì)劃

摘要: SQL查詢語(yǔ)句的性能從一定程度上影響整個(gè)數(shù)據(jù)庫(kù)的性能。很多情況下，數(shù)據(jù)庫(kù)性能的低下差不多都是不良SQL語(yǔ)句所引起。而SQL語(yǔ)句的執(zhí)行計(jì)劃則決定了SQL語(yǔ)句將會(huì)采用何種方式從數(shù)據(jù)庫(kù)提取數(shù)據(jù)并返回給客戶端，本文描述的將是如何通過(guò)EXPLAIN PLAN 獲取SQL語(yǔ)句執(zhí)行計(jì)劃來(lái)獲取SQL語(yǔ)句的執(zhí)行計(jì)劃。一、獲取SQL語(yǔ)句執(zhí)行計(jì)劃的方式 1. 使用expl... 閱讀全文

posted @ 2014-05-03 11:44 鴻雁閱讀(169) | 評(píng)論 (0) | 編輯收藏

執(zhí)行計(jì)劃的使用(EXPLAIN)

對(duì)于sql執(zhí)行的小量高低。我們可以通過(guò)執(zhí)行計(jì)劃的信息基本上可以進(jìn)行分析查看該SQL語(yǔ)句執(zhí)行的時(shí)間。連接順序及浪費(fèi)的數(shù)據(jù)庫(kù)資源等信息，從而判斷該SQL語(yǔ)句執(zhí)行的效率如何，下面就簡(jiǎn)單的介紹一下執(zhí)行計(jì)劃的使用

　　2. Explain使用

　　Oracle RDBMS執(zhí)行每一條SQL語(yǔ)句，都必須經(jīng)過(guò)Oracle優(yōu)化器的評(píng)估。所以，了解優(yōu)化器是如何選擇（搜索）路徑以及索引是如何被使用的，對(duì)優(yōu)化SQL語(yǔ)句有很大的幫助。Explain可以用來(lái)迅速方便地查出對(duì)于給定SQL語(yǔ)句中的查詢數(shù)據(jù)是如何得到的即搜索路徑（我們通常稱為Access Path）。從而使我們選擇最優(yōu)的查詢方式達(dá)到最大的優(yōu)化效果。

　　2.1. 安裝

　　要使用執(zhí)行計(jì)劃首先需要執(zhí)行相應(yīng)的腳本。

　　使用Explain工具需要?jiǎng)?chuàng)建Explain_plan表，這必須先進(jìn)入相關(guān)應(yīng)用表、視圖和索引的所有者的帳戶內(nèi)。Oracle的介質(zhì)中包含有執(zhí)行此項(xiàng)工作的SQL源程序，例如：

　　ORA_RDBMS： XPLAINPL.SQL （VMS）

　　$ORACLE_HOME/rdbms/admin/utlxplan.sql （UNIX）

　　該腳本后會(huì)生成一個(gè)表這個(gè)程序會(huì)創(chuàng)建一個(gè)名為plan_table的表，表結(jié)構(gòu)如下：

　　我們簡(jiǎn)單的介紹一下主要的字段含義：

字段名              字段類型            含義
STATEMENT_ID    VARCHAR2(30)        explain PLAN 語(yǔ)句中所指定的最優(yōu)STATEMENT_ID 參數(shù)值, 如果在EXPLAN PLAN語(yǔ)句中沒(méi)有使用SET STATEMENT_ID，那么此值會(huì)被設(shè)為NULL。
REMARKS         VARCHAR2(80)        與被解釋規(guī)劃的各步驟相關(guān)聯(lián)的注釋最長(zhǎng)可達(dá)80 字節(jié)
OPERATION       VARCHAR2(30)        各步驟所執(zhí)行內(nèi)部操作的名稱在某條語(yǔ)句所產(chǎn)生的第一行中該列的可能取值如下DELETE STATEMENT INSERT STATEMENT SELECT STATEMENT UPDATE STATEMENT
OPTIONS         VARCHAR2(30)        對(duì)OPERATION 列中所描述操作的變種
OBJECT_NODE     VARCHAR2(128)       用于訪問(wèn)對(duì)象的數(shù)據(jù)庫(kù)鏈接database link 的名稱對(duì)于使用并行執(zhí)行的本地查詢?cè)摿心軌蛎枋霾僮髦休敵龅拇涡?br />OBJECT_OWNER    VARCHAR2(30)        對(duì)于包含有表或索引的架構(gòu)schema 給出其所有者的名稱
OBJECT_NAME     VARCHAR2(30)        表或索引的名稱
OBJECT_INSTANCE INTEGER            根據(jù)對(duì)象出現(xiàn)在原始o(jì)riginal 語(yǔ)句中的次序所給出的相應(yīng)次序編號(hào)就原始的語(yǔ)句文本而論其處理順序?yàn)樽宰笾劣易酝庀騼?nèi)景象擴(kuò)張view
OBJECT_TYPE     VARCHAR2(30)        用于提供對(duì)象描述性信息的修飾符例如索引的NON-UNIQUE
OPTIMIZER       VARCHAR2(255)       當(dāng)前優(yōu)化程序的模式
ID              INTEGER             分配給執(zhí)行規(guī)劃各步驟的編號(hào)
PARENT_ID       INTEGER             對(duì)ID 步驟的輸出進(jìn)行操作的下一個(gè)執(zhí)行步驟的ID
POSITION        INTEGER             對(duì)于具有相同PARENT_ID 的步驟其相應(yīng)的處理次序
COST            INTEGER             根據(jù)優(yōu)化程序的基于開銷的方法所估計(jì)出的操作開銷值對(duì)于使用基于規(guī)則方法的語(yǔ)句該列為空該列值沒(méi)有特定的測(cè)量單位它只是一個(gè)用于比較執(zhí)行規(guī)劃開銷大小的權(quán)重值
CARDINALITY     INTEGER             根據(jù)基于開銷的方法對(duì)操作所訪問(wèn)行數(shù)的估計(jì)值
BYTES           INTEGER             根據(jù)基于開銷的方法對(duì)操作所訪問(wèn)字節(jié)的估計(jì)

　　2.2. 使用

　　2.2.1. 常規(guī)使用

　　常規(guī)使用語(yǔ)法：

explain PLAN [ SET STATEMENT_ID [=] < string literal > ]
[ INTO < table_name > ]
FOR < sql_statement >
其中：
STATEMENT_ID是一個(gè)唯一的字符串，把當(dāng)前執(zhí)行計(jì)劃與存儲(chǔ)在同一PLAN表中的其它執(zhí)行計(jì)劃區(qū)別開來(lái)。
TABLE_NAME是plan表名，它結(jié)構(gòu)如前所示，你可以任意設(shè)定這個(gè)名稱。
SQL_STATEMENT是真正的SQL語(yǔ)句。
如：
SQL> explain plan set statement_id='test1' for
2     SELECT a.soctermbegin,
3            a.soctermend,
4            a.dealserialno,
5            a.levydataid,
6            a.dealtotal,
7            e.categoryitemcode,
8            row_number() over(PARTITION BY a.levydataid ORDER BY 1) AS theRow
9       FROM tb_soc_packdealdata   a,
10            tb_Lvy_TaxDataBillMap c,
11            Tb_lvy_BillData       d,
12            tb_soc_levydetaildata e
13      WHERE a.levydataid = c.datafrompointer(+)
14        AND c.billdataid = d.billdataid(+)
15        AND a.levydataid = e.levydataid
16        AND a.packdealstatuscode = '10'
17        AND (a.datastatus <> '9' OR a.datastatus is NULL)
18        AND (d.billstatus IS NULL OR
19            (d.billstatus <> '2' AND d.billstatus <> '8'))
20        AND a.Insurcode = '6010952'
21 ;
Explained

　　執(zhí)行下面語(yǔ)句就可以查看該語(yǔ)句執(zhí)行的執(zhí)行計(jì)劃:

SQL> SELECT A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID
2 FROM PLAN_TABLE a
3 WHERE STATEMENT_ID='test1'
4 ORDER BY Id;
OPERATION        OPTIONS        OBJECT_NAME                    OBJECT_TYPEID PARENT_ID
---------------- --------------------------------------------- ------------- ----------
SELECT STATEMENT                                                           0
WINDOW           SORT                                                      1          0
FILTER                                                                     2          1
NESTED LOOPS     OUTER                                                     3          2
NESTED LOOPS     OUTER                                                     4          3
NESTED LOOPS                                                               5          4
TABLE ACCESS     FULL           TB_SOC_PACKDEALDATA                        6          5
TABLE ACCESS     BY INDEX ROWID TB_SOC_LEVYDETAILDATA                      7          5
INDEX            RANGE SCAN     IND_DATAID_LEVSOC              NON-UNIQUE 8          7
TABLE ACCESS     BY INDEX ROWID TB_LVY_TAXDATABILLMAP                      9          4
INDEX            RANGE SCAN     TBLVYTAXDATABIL_DATAFROMPOINTE NON-UNIQUE 10          9
TABLE ACCESS     BY INDEX ROWID TB_LVY_BILLDATA                           11          3
INDEX            UNIQUE SCAN    TBLVYBILLDATA_BILLDATAID       UNIQUE

　　2.2.2.        自動(dòng)顯示使用

　　在SQLPLUS中自動(dòng)跟蹤顯示執(zhí)行計(jì)劃及相關(guān)信息
　　SQL>set timing on --顯示執(zhí)行時(shí)間
　　SQL>set autorace on ?C顯示執(zhí)行計(jì)劃
　　SQL>set autorace on ?C顯示執(zhí)行計(jì)劃
　　SQL>set autotrace traceonly ?C只顯示執(zhí)行計(jì)劃即不顯示查詢出來(lái)的數(shù)據(jù)

　　設(shè)置完畢后執(zhí)行SQL語(yǔ)句就會(huì)顯示執(zhí)行計(jì)劃信息及相應(yīng)的統(tǒng)計(jì)信息(需要設(shè)置顯示該選項(xiàng))

SQL> select nvl(sum(t.taxdue), 0)
2             from tb_lvy_sbzs100 t, tb_lvy_declaredoc a, tb_lvy_declaredoc b
3            where a.dossiercode = 'SB02041108'
4              and a.pages = 123
5              and a.remarkid = b.remarkid
6              AND A.REMARKID IS NOT NULL
7              and b.declaredocid = t.declaredocid;
NVL(SUM(T.TAXDUE),0)
--------------------
                   0

　　已用時(shí)間: 00: 00: 04.07
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=110)
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=6 Card=1 Bytes=110)
   3    2       MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=74)
   4    3         TABLE ACCESS (FULL) OF 'TB_LVY_SBZS100' (Cost=2 Card =1 Bytes=31)
   5    3         BUFFER (SORT) (Cost=2 Card=1 Bytes=43)
   6    5           TABLE ACCESS (FULL) OF 'TB_LVY_DECLAREDOC' (Cost=2 Card=1 Bytes=43)
   7    2       TABLE ACCESS (BY INDEX ROWID) OF 'TB_LVY_DECLAREDOC' (Cost=2 Card=1 Bytes=36)
   8    7         INDEX (UNIQUE SCAN) OF 'TBLVYDECLAREDOC_DECLAREDOCID' (UNIQUE)
Statistics
----------------------------------------------------------
          0 recursive calls --循環(huán)遞歸次數(shù)
          0 db block gets―請(qǐng)求的數(shù)據(jù)塊在buffer能滿足的個(gè)數(shù)
       6675 consistent gets --邏輯IO用于讀表并計(jì)算行數(shù), 數(shù)據(jù)請(qǐng)求總數(shù)在回滾段Buffer中
         45 physical reads ?C從磁盤讀到Buffer Cache數(shù)據(jù)塊數(shù)量
          0 redo size ?C產(chǎn)生的redo日志大小
        217 bytes sent via SQL*Net to client
        276 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed
SQL>

　　如果6675 consistent gets --邏輯IO用于讀表并計(jì)算行數(shù), 數(shù)據(jù)請(qǐng)求總數(shù)在回滾段Buffer中

　　45 physical reads ?C從磁盤讀到Buffer Cache數(shù)據(jù)塊數(shù)量的數(shù)值比較小則該語(yǔ)句對(duì)對(duì)數(shù)據(jù)庫(kù)的性能比較高。

　　2.2.3. PL/SQL和TOAD中使用

　　如果在PL/SQL中使用選擇要查詢語(yǔ)句顯示執(zhí)行計(jì)劃，則只需要SQL WINDOWS 窗口里面輸入要查詢的SQL語(yǔ)句，然后選擇按鍵F5或者在菜單TOOLS?D?D>Explain Plan 菜單按鍵就可以在執(zhí)行計(jì)劃窗口查看該語(yǔ)句的執(zhí)行計(jì)劃。

　　在TOAD語(yǔ)句中在執(zhí)行當(dāng)前的SQL窗口中選擇下方的Explain PlanTAB頁(yè)即可以查看要執(zhí)行語(yǔ)句的執(zhí)行計(jì)劃信息。

　　2.3. 限制

　　雖然任何SQL語(yǔ)句都可以用explain解釋，但對(duì)于沒(méi)有查詢的INSERT，UPDATE，DELETE操作來(lái)說(shuō)，這個(gè)工具并沒(méi)有太大的用處。沒(méi)有子查詢的INSERT操作不會(huì)創(chuàng)建執(zhí)行計(jì)劃，但沒(méi)有WHERE子句或子查詢的UPDATE和DELETE操作會(huì)創(chuàng)建執(zhí)行計(jì)劃，因?yàn)檫@些操作必須先找出所要的記錄。

　　另外，如果你在SQL語(yǔ)句中使用其它類型如sequence等，explain也能揭示它的用法。

　　explain真正的唯一的限制是用戶不能去解釋其它用戶的表，視圖，索引或其它類型，用戶必須是所有被解釋事物的所有者，如果不是所有者而只有select權(quán)限，explain會(huì)返回一個(gè)錯(cuò)誤。

posted @ 2014-05-03 11:36 鴻雁閱讀(147) | 評(píng)論 (0) | 編輯收藏

《Oracle物化視圖實(shí)戰(zhàn)手冊(cè)》

《Oracle物化視圖實(shí)戰(zhàn)手冊(cè)》

場(chǎng)合：數(shù)據(jù)變化小，查詢出數(shù)據(jù)還要2次利用，需要數(shù)據(jù)雙向同步的場(chǎng)合

視圖：就是一條sql語(yǔ)句，每次查詢時(shí)都要重新生成執(zhí)行計(jì)劃，重新執(zhí)行，非常消耗時(shí)間，放在內(nèi)存中一次性的

物化視圖：執(zhí)行sql并保留結(jié)果，直接放在數(shù)據(jù)文件中，不放在內(nèi)存中方便重用【空間換時(shí)間】，不受開關(guān)機(jī)的影響

1.創(chuàng)建基表并插入數(shù)據(jù)

create table sino_person_address

(

iid NUMBER(16) not null,

ipersonid NUMBER(16),

spin NUMBER(16),

dgettime DATE,

sorgcode VARCHAR2(20),

smsgfilename VARCHAR2(20),

ilineno NUMBER(8),

saddress VARCHAR2(60),

szip CHAR(6),

scondition CHAR(1),

itrust NUMBER(1),

stoporgcode VARCHAR2(14),

istate NUMBER(1),

constraint PK_SINO_PERSON_ADDRESS primary key (iid)

);

插入數(shù)據(jù)(插入自動(dòng)增長(zhǎng)序列號(hào)字段的方法)

INSERT 語(yǔ)句插入這個(gè)字段值為: 序列號(hào)的名稱.NEXTVAL,seq_sino_person_address.nextval

insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-08 12:12:12','yyyy-mm-dd hh24:mi:ss'),'110','test_report',111,'beijing

xicheng','100100','1',123,1,'1000',0);

insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-09 12:12:12','yyyy-mm-dd hh24:mi:ss'),'120','test_report2',121,'beijing

xicheng','100200','2',123,1,'1002',2);

insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-10 12:12:12','yyyy-mm-dd hh24:mi:ss'),'130','test_report3',131,'beijing

xicheng','100300','3',123,1,'1003',3);

commit

###################################################################################################

2.創(chuàng)建物化視圖日志

意義：記錄基表DML操作的變化，實(shí)時(shí)刷新物化視圖

注：包含所有字段

刪除物化視圖日志

drop materialized view log on t

創(chuàng)建物化視圖日志

create materialized view log on t with sequence,rowid (x,y,z) including new values;

參數(shù)說(shuō)明：

with sequence：以序號(hào)增1的方式進(jìn)行變化記錄

rowid (x,y,z)：定位哪些數(shù)據(jù)發(fā)生了變化，日志記錄rowid指向的數(shù)據(jù)塊的位置和變化

刪除物化視圖日志

drop materialized view log on sino_person_address;

基于主鍵方式的刷新，創(chuàng)建物化視圖日志

CREATE MATERIALIZED VIEW LOG ON sino_person_address

WITH PRIMARY KEY

INCLUDING NEW VALUES

【TABLESPACE sinojfs2】; 可選項(xiàng)

3.創(chuàng)建物化視圖

創(chuàng)建物化視圖

create materialized view mv_t build immediate refresh fast on commit enable query rewrite as select x,y,z,count(*) from t group by x,y,z;

刪除物化視圖

drop materialized view mv_sino_person_address;

create materialized view mv_sino_person_address

tablespace SINOJFS2

build immediate 創(chuàng)建物化視圖時(shí)，立即刷新基表

refresh fast with primary key 支持基于主鍵的快速刷新（增量刷新），基表必須有主鍵

on commit 支持commit動(dòng)作自動(dòng)刷新

enable query rewrite

as select * from sino_person_address;

create materialized view mv_sino_person_address

tablespace SINOJFS2

build immediate

refresh fast with primary key refresh complete全部刷新【全表刷新】可選項(xiàng)

on demand 支持需求時(shí)手工刷新

enable query rewrite

as select * from sino_person_address;

########################################################################################

參數(shù)說(shuō)明：

build immediate：創(chuàng)建物化視圖時(shí)，立即刷新基表

refresh fast with primary key：支持基于主鍵的快速刷新（增量刷新），基表必須有主鍵

on commit：基于commit動(dòng)作的自動(dòng)刷新 on demand：基于需求時(shí)的手工刷新

enable query rewrite：支持查詢重新（使用物化視圖代替基表，查詢必須重寫，查詢重寫是透明的并且不需要對(duì)物化視圖有任何權(quán)限，物化視圖可以啟用和禁用查詢重寫）

查詢重寫：select * from t基表，執(zhí)行計(jì)劃走的是mv_t物化視圖，禁用后，執(zhí)行計(jì)劃走的就是t基表了

tablespace SINOJFS2 創(chuàng)建于SINOJFS2表空間

(1)創(chuàng)建方式：BUILD IMMEDIATE（立即生成數(shù)據(jù)）， BUILD DEFERRED（下一次刷新時(shí)生新數(shù)據(jù)）， ON PREBUILD TABLE（不創(chuàng)建新的數(shù)據(jù)段，用已存在的含有當(dāng)前物化視圖數(shù)據(jù)的表來(lái)代替）；
(2)ENABLE | DISABLE QUERY REWRITE指定是否啟用當(dāng)前物化視圖用于查詢重寫，啟用該選項(xiàng)時(shí)，系統(tǒng)會(huì)檢查以保證查詢的可確定性（不允許有如序列數(shù)，USER， DATE等不確定的返回值），DISABLE時(shí)物化視圖照樣可以被刷新；
與物化視圖生效相關(guān)的設(shè)置
(1)初始化參數(shù)JOB_QUEUE_PROCESSES設(shè)置大于零，物化的自動(dòng)刷新操作需要JOB QUEUE進(jìn)程來(lái)執(zhí)行；
(2)初始化參數(shù)OPTIMIZER_MODE要設(shè)成某種CBO優(yōu)化模式；
(3)用戶會(huì)話有QUERY_REWRITE（優(yōu)化器能將查詢重寫到本方案物化視圖）或GLOBAL_QUERY_REWRITE（優(yōu)化器能將查詢重寫到其它方案的物化視圖）系統(tǒng)權(quán)限；
(4)初始化參數(shù)QUERY_REWRITE_ENABLED 指示優(yōu)化器是否動(dòng)態(tài)重寫查詢來(lái)使用物化視圖，這個(gè)參數(shù)可以在四個(gè)級(jí)別上進(jìn)行設(shè)置（參數(shù)文件，ALTER SYSTEM， ALTER SESSION， HINTS）；
(5)初始化參數(shù)QUERY_REWRITE_INTEGRITY 指示優(yōu)化器在不同的數(shù)據(jù)一致性情況下決定是否使用物化視圖來(lái)重寫查詢，ENFORCED（只有在能確保數(shù)據(jù)一致的前提下才使用物化視圖）， TRUSTED（數(shù)據(jù)不一定一致，只要有用維度對(duì)象定義的關(guān)系存在，就可使用物化視圖）， STALE_TOLERATED（數(shù)據(jù)不一致，也沒(méi)有相關(guān)的維度定義時(shí)仍可使用物化視圖），這個(gè)參數(shù)可以在三個(gè)級(jí)別上進(jìn)行設(shè)置（參數(shù)文件，ALTER SYSTEM， ALTER SESSION）；

4. 物化視圖DML操作測(cè)試

（1）驗(yàn)證物化視圖是否隨記錄增加而增加

insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-11 13:13:13','yyyy-mm-dd hh24:mi:ss'),'140','test_report4',141,'beijing

xicheng','100400','4',123,1,'1004',4);

select * from sino_person_address order by dgetdate;

select * from mv_sino_person_address order by dgetdate; 隨記錄增加而木有刷新，必須commit之后才觸發(fā)物化視圖刷新，沒(méi)有問(wèn)題

exec dbms_mview.refresh('mv_sino_person_address','c'); 還可以手動(dòng)全部刷新【全表刷新】（先清除，再重裝數(shù)據(jù)）

exec dbms_mview.refresh('mv_sino_person_address','f'); 也可以快速刷新【增量刷新】借助物化視圖日志，只檢查自上次刷新后改變了的數(shù)據(jù)來(lái)進(jìn)行刷新）

（2）驗(yàn)證物化視圖是否隨記錄刪除而減少

delete from sino_person_address where iid=21;

select * from sino_person_address order by dgetdate;

select * from mv_sino_person_address order by dgetdate; 隨記錄刪除而木有刷新，必須commit之后才觸發(fā)物化視圖刷新，沒(méi)有問(wèn)題

exec dbms_mview.refresh('mv_sino_person_address','c'); 還可以手動(dòng)全部刷新【全表刷新】（先清除，再重裝數(shù)據(jù)）

（3）驗(yàn)證物化視圖是否隨記錄修改而更新

update sino_person_address set sorgcode='200' where sorgcode='120';

select * from sino_person_address order by dgetdate;

select * from mv_sino_person_address order by dgetdate; 隨記錄修改而木有刷新，必須commit之后才觸發(fā)物化視圖刷新，沒(méi)有問(wèn)題

exec dbms_mview.refresh('mv_sino_person_address','c'); 還可以手動(dòng)全部刷新【全表刷新】（先清除，再重裝數(shù)據(jù)）

exec dbms_mview.refresh('mv_sino_person_address','f'); 也可以快速刷新【增量刷新】（借助物化視圖日志，只檢查自上次刷新后改變了的數(shù)據(jù)來(lái)進(jìn)行刷新）

（4）驗(yàn)證物化視圖是否隨truncate而清空

truncate table sino_person_address;

select * from sino_person_address order by dgetdate;

select * from mv_sino_person_address order by dgetdate; 隨記錄truncate而木有清空，必須手動(dòng)truncate table mv_sino_person_address;才能清空（兩者是沒(méi)有關(guān)聯(lián)的），沒(méi)有問(wèn)題

5.物化視圖刷新

根據(jù)業(yè)務(wù)需求，每月定時(shí)刷新。根據(jù)以上條件，選擇使用ORACLE自帶工具DBMS_MVIEW工具包中REFRESH方法對(duì)物化視圖進(jìn)行刷新。該方法有兩個(gè)參數(shù)，第一個(gè)參數(shù)是需要刷新的物化視圖名稱，第二個(gè)參數(shù)是刷新方式。我們可以寫存儲(chǔ)過(guò)程，對(duì)每個(gè)物化視圖調(diào)用一次REFRESH方法，也可以使用“，”把物化視圖連接以來(lái)，一次刷新。

定義存儲(chǔ)過(guò)程

create or replace procedure pro_mview_refresh

begin

dbms_mview.refresh('mv_sino_person_address','f');

end;

執(zhí)行存儲(chǔ)過(guò)程

execute pro_mview_refresh;

還可以刷新所有物化視圖 dbms_mview.refresh_all_mviews;

創(chuàng)建存儲(chǔ)過(guò)程

drop procedure pro_refresh_all_mviews;

create or replace procedure pro_refresh_all_mviews

i number;

begin

dbms_mview.refresh_all_mviews(number_of_failures=>i);

dbms_output.put_line('number_of_failures=>'||i);

end;

執(zhí)行

execute pro_refresh_all_mviews;

set serveroutput on;不可放在存儲(chǔ)過(guò)程中，因?yàn)檫@是sqlplus命令，如果你怕忘記或者嫌麻煩可以把set serveroutput on;

寫入/opt/oracle/product/11.2.0/dbhome_1/sqlplus/admin/glogin.sql中，每次使用sqlplus時(shí)自動(dòng)加載這個(gè)文件

如果想用PL/SQL Developer工具訪問(wèn)數(shù)據(jù)庫(kù)，請(qǐng)?jiān)贑:\Program Files\PLSQL Developer\Login.sql 文件里添加

-- Autostart Command Window script

set serveroutput on;

這樣以后再使用PL/SQL Developer工具訪問(wèn)數(shù)據(jù)庫(kù)就可以自動(dòng)加載這條命令了

###############################################################################################

研發(fā)人員專用，手動(dòng)刷新，想刷就刷

set serveroutput on; 打開屏幕顯示功能，就可以看到number_of_failures=>0結(jié)果

PL/SQL 匿名塊

declare

i number;

begin

dbms_mview.refresh_all_mviews(number_of_failures=>i);

dbms_output.put_line('number_of_failures=>'||i);

end;

number_of_failures=>0

Number_of_failures 表示刷新物化視圖失敗個(gè)數(shù)

采用默認(rèn)refresh force 刷新方式：先試圖用FAST方式刷新，如果失敗再用COMPLETE方式刷新，這是默認(rèn)的刷新方式

注意:

1、如果需要同時(shí)刷新多個(gè)物化視圖，必須用逗號(hào)把各個(gè)物化視圖名稱連接起來(lái)，并對(duì)每個(gè)視圖都要指明刷新方式（f、增量刷新，c、完全刷新，?、強(qiáng)制刷新，從不刷新）。

NEVER REFRESH（不刷新）

REFREST FAST（借助物化視圖日志，只檢查自上次刷新后改變了的數(shù)據(jù)來(lái)進(jìn)行刷新）

REFRESH COMPLETE（先清除，再重裝數(shù)據(jù)）

REFRESH FORCE（先試圖用FAST方式刷新，如果失敗再用COMPLETE方式刷新，這是默認(rèn)的刷新方式）

確定刷新時(shí)機(jī)：

ON COMMIT（事務(wù)提交時(shí)刷新），

ON DEMAND（用DBMS_MVIEW.REFRESH， DBMS_MVIEW.REFRESH_DEPENDENT， DBMS_MVIEW.REFRESH_ALL_MVIEWS來(lái)手工刷新），

By Time（用START WITH 和 NEXT 子句創(chuàng)建的job來(lái)定時(shí)自動(dòng)刷新）；

[dbms_mview.refresh('mv_sino_person_address,mv_person_address_his','ff');]

2、當(dāng)日志和物化視圖創(chuàng)建好后，刪除日志，則需要重新創(chuàng)建物化視圖，否則無(wú)法增量刷新。

drop materialized view log on sino_person_address; 刪除日志

SQL> exec dbms_mview.refresh('mv_sino_person_address','c'); 刪除物化視圖日志，只可以支持物化視圖全部刷新

PL/SQL procedure successfully completed

#################################################################################

SQL> exec dbms_mview.refresh('mv_sino_person_address','f'); 無(wú)法增量刷新

begin dbms_mview.refresh('mv_sino_person_address','f'); end;

ORA-23413: 表 "SINOJFS"."SINO_PERSON_ADDRESS" 沒(méi)有實(shí)體化視圖日志

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2558

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2771

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2740

ORA-06512: 在 line 2

SQL> create materialized view log on sino_person_employment 重新創(chuàng)建物化視圖日志

2 with primary key

3 including new values;

Materialized view log created

SQL> exec dbms_mview.refresh('mv_sino_person_employment','f'); 但還是不支持增量刷新，因?yàn)槿罩緝?nèi)容和原表內(nèi)容不一致了

begin dbms_mview.refresh('mv_sino_person_employment','f'); end;

ORA-12034: "SINOJFS"."SINO_PERSON_EMPLOYMENT" 上的實(shí)體化視圖日志比上次刷新后的內(nèi)容新

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2558

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2771

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2740

ORA-06512: 在 line 2

因?yàn)椋簛G失了刪除日志那一點(diǎn)->重建日志那一點(diǎn)之間的原表DML變化，因此日志內(nèi)容和原表內(nèi)容不一致了

解決方案：重建物化視圖日志重新【增量刷新】和【全表刷新】一遍

SQL> drop materialized view log on sino_loan_compact; 刪除日志

Materialized view log dropped

SQL> create materialized view log on sino_loan_compact 重建日志

2 with primary key

3 including new values;

Materialized view log created

SQL> exec dbms_mview.refresh('mv_sino_loan_compact','c'); 必須先全表刷新

PL/SQL procedure successfully completed

SQL> exec dbms_mview.refresh('mv_sino_loan_compact','f'); 再增量刷新，否則ORA-12034: "SINOJFS"."SINO_LOAN_COMPACT" 上的實(shí)體化視圖日志比上次刷新后的內(nèi)容新

PL/SQL procedure successfully completed

小結(jié)：只要能夠增量刷新，說(shuō)明日志沒(méi)有問(wèn)題了

簡(jiǎn)述所有視圖的快速刷新和全表刷新命令（測(cè)試使用） 10張視圖

select owner,table_name,tablespace_name,status from dba_tables where table_name in ('SINO_LOAN_APPLY');

update SINO_LOAN_APPLY set sorgcode ='1000' where iid =858;

exec dbms_mview.refresh('mv_sino_loan_compact','c');

exec dbms_mview.refresh('mv_sino_loan_compact','f');

exec dbms_mview.refresh('mv_sino_loan_apply','c');

exec dbms_mview.refresh('mv_sino_loan_apply','f');

exec dbms_mview.refresh('mv_sino_loan_spec_trade','c');

exec dbms_mview.refresh('mv_sino_loan_spec_trade','f');

exec dbms_mview.refresh('mv_sino_loan','c');

exec dbms_mview.refresh('mv_sino_loan','f');

exec dbms_mview.refresh('mv_sino_loan_guarantee','c');

exec dbms_mview.refresh('mv_sino_loan_guarantee','f');

exec dbms_mview.refresh('mv_sino_loan_investor','c');

exec dbms_mview.refresh('mv_sino_loan_investor','f');

###############################################################################

exec dbms_mview.refresh('mv_sino_person_employment','c');

exec dbms_mview.refresh('mv_sino_person_employment','f');

exec dbms_mview.refresh('mv_sino_person_address','c');

exec dbms_mview.refresh('mv_sino_person_address','f');

exec dbms_mview.refresh('mv_sino_person_certification','c');

exec dbms_mview.refresh('mv_sino_person_certification','f');

exec dbms_mview.refresh('mv_sino_person','c');

exec dbms_mview.refresh('mv_sino_person','f');

3.基表增加字段后對(duì)應(yīng)物化視圖不能自動(dòng)同步結(jié)構(gòu)

業(yè)務(wù)表增加上報(bào)狀態(tài) 字段 ipbcstate number(1) 可以為空

文檔建模腳本物化視圖

sino_person_certification 完成完成完成完成

sino_person 完成完成完成完成

sino_person_address 完成完成完成完成

sino_person_employment 完成完成完成完成

sino_person_address_his 完成完成完成

sino_person_employment_his 完成完成完成

sino_person_his 完成完成完成

sino_loan 完成完成完成完成

sino_loan_compact 完成完成完成完成

sino_loan_spec_trade 完成完成完成完成

sino_loan_guarantee 完成完成完成完成

sino_loan_investor 完成完成完成完成

sino_loan_apply 完成完成完成完成

對(duì)比IPBCSTATE 字段基表有，但物化視圖沒(méi)有，需要重建物化視圖解決

select * from mv_sino_loan_compact where rownum<2;

select * from sino_loan_compact where rownum<2;

select * from mv_sino_loan where rownum < 2;

select * from sino_loan where rownum < 2;

select * from mv_sino_loan_apply where rownum < 2;

select * from sino_loan_apply where rownum < 2;

select * from mv_sino_loan_guarantee where rownum < 2;

select * from sino_loan_guarantee where rownum < 2;

select * from mv_sino_loan_guarantee where rownum < 2;

select * from sino_loan_guarantee where rownum < 2;

select * from mv_sino_loan_investor where rownum < 2;

select * from sino_loan_investor where rownum < 2;

select * from mv_sino_loan_spec_trade where rownum < 2;

select * from sino_loan_spec_trade where rownum < 2;

################################################################################

select * from mv_sino_person where rownum < 2;

select * from sino_person where rownum < 2;

select * from mv_sino_person_address where rownum < 2;

select * from sino_person_address where rownum < 2;

select * from mv_sino_person_certification where rownum < 2;

select * from sino_person_certification where rownum < 2;

select * from mv_sino_person_employment where rownum < 2;

select * from sino_person_employment where rownum < 2;

##################################################################################

4.因?yàn)樯厦鎸懙奈锘晥D是基于主鍵進(jìn)行刷新的，因此原表必須要有主鍵

6.定時(shí)刷新JOB

確定執(zhí)行時(shí)間間隔
1)、每分鐘執(zhí)行
Interval => TRUNC(sysdate,'mi') + 1 / (24*60)
2)、每天定時(shí)執(zhí)行
例如：每天下午2點(diǎn)執(zhí)行一次pro_mview_refresh存儲(chǔ)過(guò)程
Interval => TRUNC(sysdate) + 1 +14/ (24)
3)、每周定時(shí)執(zhí)行
例如：每周一凌晨2點(diǎn)執(zhí)行
Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天
4)、每月定時(shí)執(zhí)行
例如：每月1日凌晨2點(diǎn)執(zhí)行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24
5)、每季度定時(shí)執(zhí)行
例如每季度的第一天凌晨2點(diǎn)執(zhí)行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24
6)、每半年定時(shí)執(zhí)行
例如：每年7月1日和1月1日凌晨2點(diǎn)
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24
7)、每年定時(shí)執(zhí)行
例如：每年1月1日凌晨2點(diǎn)執(zhí)行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24

通過(guò)jobs的使用就能實(shí)現(xiàn)每天或每月的指定時(shí)間執(zhí)行一個(gè)函數(shù)、過(guò)程與命令

set serveroutput on 啟動(dòng)屏幕輸出功能

SQL> execute dbms_output.put_line('This is'); 已經(jīng)可以正常輸出

This is

創(chuàng)建作業(yè)

variable job_num number; 定義存儲(chǔ)job編號(hào)的變量

declare job_num number; pro_refresh_all_mviews

begin

dbms_job.submit

(job=>:job_num,

what=>'pro_refresh_all_mviews;',

next_date=>sysdate,

interval=>'sysdate+1/1440'); 每天1440分鐘，每一分鐘運(yùn)行pro_mview_refresh過(guò)程一次

dbms_output.put_line('Job Number is'||to_char(job_num));

commit;

end;

############################################################################################

綁定變量版，必須先定義變量

variable job_num number;

declare job_num number;

begin

dbms_job.submit

(job=>:job_num,

what=>'pro_refresh_all_mviews;',

next_date=>sysdate,

interval=>'trunc(SYSDATE+5/1440,''MI'')'); 每5分鐘運(yùn)行一次job

dbms_output.put_line('Job Number is'||to_char(job_num));

commit;

end;

例如：每天上午10點(diǎn)執(zhí)行一次pro_refresh_all_mviews存儲(chǔ)過(guò)程
Interval => TRUNC(sysdate) + 1 +10/ (24)
declare job_num number;

begin

dbms_job.submit

(job=>:job_num,

what=>'pro_refresh_all_mviews;',

next_date=>sysdate,

interval=>'trunc(SYSDATE)+1+10/24'); 每天上午10點(diǎn)運(yùn)行一次job

dbms_output.put_line('Job Number is'||to_char(job_num));

commit;

end;

Job Number is

PL/SQL procedure successfully completed

job_num

---------

####################################################################################

PL/SQL 匿名塊版，可以直接在塊中定義變量，比較方面現(xiàn)在采用這種

declare

job_num number;

begin

dbms_job.submit

(job=>job_num,

what=>'pro_refresh_all_mviews;',

next_date=>sysdate,

interval=>'trunc(SYSDATE)+1+10/24');

dbms_output.put_line('Job Number is '||job_num);

commit;

end;

Job Number is 4

PL/SQL procedure successfully completed

####################################################################################

dbms_job.submit( job out binary_integer,
what in varchar2,
next_date in date，
interval in varchar2,
no_parse in boolean)

●job：輸出變量，這是作業(yè)在作業(yè)隊(duì)列中的編號(hào)；
●what：執(zhí)行作業(yè)的存儲(chǔ)過(guò)程及其輸入?yún)?shù)；
●next_date：作業(yè)初次執(zhí)行的時(shí)間；
●interval：作業(yè)執(zhí)行的時(shí)間間隔。指上一次執(zhí)行結(jié)束到下一次開始執(zhí)行的時(shí)間間隔
其中Interval這個(gè)值是決定Job何時(shí)，被重新執(zhí)行的關(guān)鍵；當(dāng)interval設(shè)置為null時(shí)，該job執(zhí)行結(jié)束后，就被從隊(duì)列中刪除。假如我們需要該job周期性地執(zhí)行，則要用‘sysdate＋m’表示。如何更好地確定執(zhí)行時(shí)間的間隔需要我們掌握一個(gè)函數(shù)TRUNC。

SQL> show parameter job_queue_process 作業(yè)隊(duì)列進(jìn)程數(shù)，oracle能夠并發(fā)job數(shù)量，0~1000

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

job_queue_processes integer 1000

Oracle提供的數(shù)據(jù)字典user_jobs監(jiān)控作業(yè)狀態(tài)

SQL> select job,log_user,what,last_date,last_sec,next_date,next_sec,failures,broken from user_jobs;

Job 作業(yè)唯一編號(hào)

Log_user 提交作業(yè)的用戶

What 作業(yè)執(zhí)行的存儲(chǔ)過(guò)程

Last_date 最后一次成功運(yùn)行作業(yè)的日期

Last_sec 最后一次成功運(yùn)行作業(yè)的時(shí)間

Next_date 下一次運(yùn)行作業(yè)日期

Next_sec 下一次運(yùn)行作業(yè)時(shí)間

Failures 執(zhí)行失敗次數(shù)，當(dāng)執(zhí)行job出現(xiàn)錯(cuò)誤時(shí)，Oracle將其記錄在日志里，失敗次數(shù)每次自動(dòng)加1，加到16之后Oracle就不在執(zhí)行它了

Broken 是否是異常作業(yè)，當(dāng)執(zhí)行失敗次數(shù)達(dá)到16時(shí)，Oracle就將該job標(biāo)志為broken。此后，Oracle不再繼續(xù)執(zhí)行它，直到用戶調(diào)用過(guò)程dbms_job.broken，重新設(shè)置為not broken，或強(qiáng)制調(diào)用dbms_job.run來(lái)重新執(zhí)行它。Y標(biāo)示作業(yè)中斷，以后不會(huì)運(yùn)行，N表示作業(yè)正常，可以運(yùn)行

運(yùn)行作業(yè)
begin
dbms_job.run(:job_num); job_num是存儲(chǔ)job編號(hào)的變量
end;
查詢作業(yè)狀態(tài)
SQL> select job,log_user,what,last_date,last_sec,next_date,next_sec,failures,broken from user_jobs;

JOB LOG_USER WHAT LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC FAILURES BROKEN

---------- --------- ---------------------------------------------------- ----------- ---------------- -----------

1 SINOJFS pro_refresh_all_mviews; 2013-4-26 1 11:27:38 2013-4-27 1 10:00:00 0 N

Job 作業(yè)唯一編號(hào)

Log_user 提交作業(yè)的用戶

What 作業(yè)執(zhí)行的存儲(chǔ)過(guò)程

Last_date 最后一次成功運(yùn)行作業(yè)的日期

Last_sec 最后一次成功運(yùn)行作業(yè)的時(shí)間

Next_date 下一次運(yùn)行作業(yè)日期

Next_sec 下一次運(yùn)行作業(yè)時(shí)間

或強(qiáng)制調(diào)用dbms_job.run來(lái)重新執(zhí)行它。Y標(biāo)示作業(yè)中斷，以后不會(huì)運(yùn)行，N表示作業(yè)正常，可以運(yùn)行

刪除作業(yè)
begin
dbms_job.remove(:job_num);
end;

修改作業(yè)

dbms_job.remove(jobno); 刪除job號(hào)

例 execute dbms_job.remove(1);

######################################################################

dbms_job.what(jobno,what); 修改執(zhí)行的存儲(chǔ)過(guò)程

dbms_job.next_date(job,next_date)修改下次執(zhí)行的時(shí)間

例 exec dbms_job.next_date(46,sysdate+2/(24*60)); 46作業(yè)號(hào)

#####################################################################

dbms_job.interval(job,interval) :修改間隔時(shí)間

例 exec dbms_job.interval(46,sysdate+3/(24*60));

######################################################################
dbms_job.broken(job,true) 中斷job

例 exec dbms_job.broken(46,true); 46作業(yè)號(hào) exec dbms_job.broken(2,true) BROKEN=Y

#######################################################################

dbms_job.broken(job,false,next_date) next_date:下次執(zhí)行時(shí)間，如果不填則馬上啟動(dòng)job

例 exec dbms_job.broken(46,false); 啟動(dòng)job exec dbms_job.broken(2,false); BROKEN=N

########################################################################

dbms_job.run(jobno); 運(yùn)行作業(yè)

例子 execute dbms_job.run(1);

posted @ 2014-05-02 23:03 鴻雁閱讀(265) | 評(píng)論 (0) | 編輯收藏

Oracle 物化視圖說(shuō)明

一．物化視圖概述

Oracle的物化視圖是包括一個(gè)查詢結(jié)果的數(shù)據(jù)庫(kù)對(duì)像，它是遠(yuǎn)程數(shù)據(jù)的的本地副本，或者用來(lái)生成基于數(shù)據(jù)表求和的匯總表。物化視圖存儲(chǔ)基于遠(yuǎn)程表的數(shù)據(jù)，也可以稱為快照。

物化視圖可以用于預(yù)先計(jì)算并保存表連接或聚集等耗時(shí)較多的操作的結(jié)果，這樣，在執(zhí)行查詢時(shí)，就可以避免進(jìn)行這些耗時(shí)的操作，而從快速的得到結(jié)果。物化視圖有很多方面和索引很相似：使用物化視圖的目的是為了提高查詢性能；物化視圖對(duì)應(yīng)用透明，增加和刪除物化視圖不會(huì)影響應(yīng)用程序中SQL語(yǔ)句的正確性和有效性；物化視圖需要占用存儲(chǔ)空間；當(dāng)基表發(fā)生變化時(shí)，物化視圖也應(yīng)當(dāng)刷新。

物化視圖可以查詢表，視圖和其它的物化視圖。

通常情況下，物化視圖被稱為主表（在復(fù)制期間）或明細(xì)表（在數(shù)據(jù)倉(cāng)庫(kù)中）。

對(duì)于復(fù)制，物化視圖允許你在本地維護(hù)遠(yuǎn)程數(shù)據(jù)的副本,這些副本是只讀的。如果你想修改本地副本，必須用高級(jí)復(fù)制的功能。當(dāng)你想從一個(gè)表或視圖中抽取數(shù)據(jù)時(shí)，你可以用從物化視圖中抽取。

對(duì)于數(shù)據(jù)倉(cāng)庫(kù)，創(chuàng)建的物化視圖通常情況下是聚合視圖，單一表聚合視圖和連接視圖。

在復(fù)制環(huán)境下，創(chuàng)建的物化視圖通常情況下主鍵，rowid,和子查詢視圖。

物化視圖由于是物理真實(shí)存在的，故可以創(chuàng)建索引。

1.1 物化視圖可以分為以下三種類型

（1）包含聚集的物化視圖；

（2）只包含連接的物化視圖；

（3）嵌套物化視圖。

三種物化視圖的快速刷新的限制條件有很大區(qū)別，而對(duì)于其他方面則區(qū)別不大。創(chuàng)建物化視圖時(shí)可以指定多種選項(xiàng)，下面對(duì)幾種主要的選擇進(jìn)行簡(jiǎn)單說(shuō)明：

（1）創(chuàng)建方式（BuildMethods）：包括BUILD IMMEDIATE和BUILD DEFERRED兩種。

BUILD IMMEDIATE是在創(chuàng)建物化視圖的時(shí)候就生成數(shù)據(jù)。

BUILD DEFERRED則在創(chuàng)建時(shí)不生成數(shù)據(jù)，以后根據(jù)需要在生成數(shù)據(jù)。默認(rèn)為BUILD IMMEDIATE。

（2）查詢重寫（QueryRewrite）：包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE兩種。

分別指出創(chuàng)建的物化視圖是否支持查詢重寫。查詢重寫是指當(dāng)對(duì)物化視圖的基表進(jìn)行查詢時(shí)，Oracle會(huì)自動(dòng)判斷能否通過(guò)查詢物化視圖來(lái)得到結(jié)果，如果可以，則避免了聚集或連接操作，而直接從已經(jīng)計(jì)算好的物化視圖中讀取數(shù)據(jù)。默認(rèn)為DISABLEQUERY REWRITE。

（3）刷新（Refresh）：指當(dāng)基表發(fā)生了DML操作后，物化視圖何時(shí)采用哪種方式和基表進(jìn)行同步。刷新的模式有兩種：ON DEMAND和ON COMMIT。

ON DEMAND和ON COMMIT物化視圖的區(qū)別在于其刷新方法的不同，ON DEMAND指物化視圖在用戶需要的時(shí)候進(jìn)行刷新，可以手工通過(guò)DBMS_MVIEW.REFRESH等方法來(lái)進(jìn)行刷新，也可以通過(guò)JOB定時(shí)進(jìn)行刷新,即更新物化視圖，以保證和基表數(shù)據(jù)的一致性;而ON COMMIT是說(shuō)，一旦基表有了COMMIT，即事務(wù)提交，則立刻刷新，立刻更新物化視圖，使得數(shù)據(jù)和基表一致。

對(duì)基表，平常的COMMIT在0.01秒內(nèi)可以完成，但在有了ON COMMIT視圖后，居然要6秒。速度減低了很多倍。ON COMMIT視圖對(duì)基表的影響可見(jiàn)一斑。

1.2 物化視圖，根據(jù)不同的著重點(diǎn)可以有不同的分類:

1) 按刷新方式分：FAST/COMPLETE/FORCE

2) 按刷新時(shí)間的不同：ON DEMAND/ON COMMIT

3) 按是否可更新：UPDATABLE/READ ONLY

4) 按是否支持查詢重寫：ENABLE QUERY REWRITE/DISABLEQUERY REWRITE

默認(rèn)情況下，如果沒(méi)指定刷新方法和刷新模式，則Oracle默認(rèn)為FORCE和DEMAND。

注意：設(shè)置REFRESH ON COMMIT的物化視圖不能訪問(wèn)遠(yuǎn)端對(duì)象。

在建立物化視圖的時(shí)候可以指定ORDER BY語(yǔ)句，使生成的數(shù)據(jù)按照一定的順序進(jìn)行保存。不過(guò)這個(gè)語(yǔ)句不會(huì)寫入物化視圖的定義中，而且對(duì)以后的刷新也無(wú)效。

1.3 物化視圖有三種刷新方式：COMPLETE、FAST和 FORCE。

1) 完全刷新（COMPLETE）會(huì)刪除表中所有的記錄（如果是單表刷新，可能會(huì)采用TRUNCATE的方式），然后根據(jù)物化視圖中查詢語(yǔ)句的定義重新生成物化視圖。

2) 快速刷新（FAST）采用增量刷新的機(jī)制，只將自上次刷新以后對(duì)基表進(jìn)行的所有操作刷新到物化視圖中去。FAST必須創(chuàng)建基于主表的視圖日志。

對(duì)于增量刷新選項(xiàng)，如果在子查詢中存在分析函數(shù)，則物化視圖不起作用。

3) 采用FORCE方式，Oracle會(huì)自動(dòng)判斷是否滿足快速刷新的條件，如果滿足則進(jìn)行快速刷新，否則進(jìn)行完全刷新。

Oracle物化視圖的快速刷新機(jī)制是通過(guò)物化視圖日志完成的。Oracle通過(guò)一個(gè)物化視圖日志還可以支持多個(gè)物化視圖的快速刷新。

物化視圖日志根據(jù)不同物化視圖的快速刷新的需要，可以建立為ROWID或PRIMARY KEY類型的。還可以選擇是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

1.4 物化視圖Refresh子句的其他說(shuō)明與示例

REFRESH 子句可以包含如下部分：

[refresh [fast|complete|force]

[on demand | commit]

[start with date] [next date]

[with {primary key|rowid}]]

1.4.1 主鍵和ROWD子句：

WITH PRIMARY KEY選項(xiàng)生成主鍵物化視圖,也就是說(shuō)物化視圖是基于主表的主鍵，而不是ROWID(對(duì)應(yīng)于ROWID子句). PRIMARY KEY是默認(rèn)選項(xiàng),為了生成PRIMARY KEY子句，應(yīng)該在主表上定義主鍵，否則應(yīng)該用基于ROWID的物化視圖.

基于ROWID物化視圖只有一個(gè)單一的主表，不能包括下面任何一項(xiàng):

（1）.Distinct 或者聚合函數(shù).

（2） .Group by，子查詢，連接和SET操作

--主鍵（PrimaryKey）物化視圖示例：

在遠(yuǎn)程數(shù)據(jù)庫(kù)表emp上創(chuàng)建主鍵物化視圖：

[sql] view plain copy print ?

CREATEMATERIALIZEDVIEW mv_emp_pk
REFRESHFASTSTARTWITHSYSDATE
NEXT SYSDATE + 1/48
WITHPRIMARYKEY
ASSELECT * FROM emp@remote_db

CREATEMATERIALIZEDVIEW mv_emp_pk
REFRESHFASTSTARTWITHSYSDATE
NEXT  SYSDATE + 1/48
WITHPRIMARYKEY
ASSELECT * FROM emp@remote_db

--當(dāng)用FAST選項(xiàng)創(chuàng)建物化視圖，必須創(chuàng)建基于主表的視圖日志,如下:

[sql] view plain copy print ?

CREATEMATERIALIZEDVIEWLOGON emp;

CREATEMATERIALIZEDVIEWLOGON emp;

--Rowid物化視圖示例：

下面的語(yǔ)法在遠(yuǎn)程數(shù)據(jù)庫(kù)表emp上創(chuàng)建Rowid物化視圖

[sql] view plain copy print ?

CREATEMATERIALIZEDVIEW mv_emp_rowid
REFRESHWITHROWID
ASSELECT * FROM emp@remote_db;
Materializedviewlog created.

CREATEMATERIALIZEDVIEW mv_emp_rowid
REFRESHWITHROWID
ASSELECT * FROM emp@remote_db;
Materializedviewlog created.

--子查詢物化視圖示例：

在遠(yuǎn)程數(shù)據(jù)庫(kù)表emp上創(chuàng)建基于emp和dept表的子查詢物化視圖

[sql] view plain copy print ?

CREATEMATERIALIZEDVIEW mv_empdept
ASSELECT * FROM emp@remote_db e
WHEREEXISTS
(SELECT * FROM dept@remote_db d
WHEREe.dept_no = d.dept_no)

CREATEMATERIALIZEDVIEW  mv_empdept
ASSELECT * FROM emp@remote_db e
WHEREEXISTS
(SELECT * FROM dept@remote_db d
WHEREe.dept_no = d.dept_no)

1.4.2 刷新時(shí)間

START WITH子句通知數(shù)據(jù)庫(kù)完成從主表到本地表第一次復(fù)制的時(shí)間,應(yīng)該及時(shí)估計(jì)下一次運(yùn)行的時(shí)間點(diǎn), NEXT 子句說(shuō)明了刷新的間隔時(shí)間.

[sql] view plain copy print ?

CREATEMATERIALIZEDVIEW mv_emp_pk
REFRESHFAST
STARTWITHSYSDATE
NEXT SYSDATE + 2
WITHPRIMARYKEY
ASSELECT * FROM emp@remote_db;

CREATEMATERIALIZEDVIEW mv_emp_pk
REFRESHFAST
STARTWITHSYSDATE
NEXT  SYSDATE + 2
WITHPRIMARYKEY
ASSELECT * FROM emp@remote_db;

在上面的例子中，物化視圖數(shù)據(jù)的第一個(gè)副本在創(chuàng)建時(shí)生成，以后每?jī)商焖⑿乱淮?

[sql] view plain copy print ?

creatematerializedviewMV_LVY_LEVYDETAILDATA
TABLESPACE ZGMV_DATA --保存表空間
BUILDDEFERRED--延遲刷新不立即刷新
refreshforce--如果可以快速刷新則進(jìn)行快速刷新，否則完全刷新
ondemand--按照指定方式刷新
startwith to_date('24-11-200518:00:10', 'dd-mm-yyyyhh24:mi:ss') --第一次刷新時(shí)間
nextTRUNC(SYSDATE+1)+18/24--刷新時(shí)間間隔
as
SELECT * FROM emp@remote_db;

creatematerializedviewMV_LVY_LEVYDETAILDATA
TABLESPACE ZGMV_DATA --保存表空間
BUILDDEFERRED--延遲刷新不立即刷新
refreshforce--如果可以快速刷新則進(jìn)行快速刷新，否則完全刷新
ondemand--按照指定方式刷新
startwith to_date('24-11-200518:00:10', 'dd-mm-yyyyhh24:mi:ss') --第一次刷新時(shí)間
nextTRUNC(SYSDATE+1)+18/24--刷新時(shí)間間隔
as
SELECT * FROM emp@remote_db;

1.5 ON PREBUILD TABLE 說(shuō)明

在創(chuàng)建物化視圖時(shí)指明ON PREBUILD TABLE語(yǔ)句，可以將物化視圖建立在一個(gè)已經(jīng)存在的表上。這種情況下，物化視圖和表必須同名。當(dāng)刪除物化視圖時(shí)，不會(huì)刪除同名的表。

這種物化視圖的查詢重寫要求參數(shù)QUERY_REWRITE_INTEGERITY必須設(shè)置為trusted或者stale_tolerated。

1.6 物化視圖分區(qū)

物化視圖可以進(jìn)行分區(qū)。而且基于分區(qū)的物化視圖可以支持分區(qū)變化跟蹤（PCT）。具有這種特性的物化視圖，當(dāng)基表進(jìn)行了分區(qū)維護(hù)操作后，仍然可以進(jìn)行快速刷新操作。對(duì)于聚集物化視圖，可以在GROUP BY列表中使用CUBE或ROLLUP，來(lái)建立不同等級(jí)的聚集物化視圖。

相關(guān)內(nèi)容參考：

Oracle 物化視圖日志與快速刷新說(shuō)明

http://blog.csdn.net/tianlesoftware/article/details/7720580

Oracle 物化視圖詳細(xì)錯(cuò)誤描述查看方法

http://blog.csdn.net/tianlesoftware/article/details/7719789

Oracle 物化視圖快速刷新限制說(shuō)明

http://blog.csdn.net/tianlesoftware/article/details/7719679

二．物化視圖操作示例

1. 創(chuàng)建物化視圖需要的權(quán)限：

[sql] view plain copy print ?

GRANT CREATE MATERIALIZED VIEW TO USER_NAME;

GRANT CREATE MATERIALIZED VIEW TO USER_NAME;

2. 在源表建立物化視圖日志

[sql] view plain copy print ?

CREATE MATERIALIZED VIEW LOG ON DAVE
TABLESPACE&BISONCU_SPACE -- 日志空間
WITH PRIMARY KEY; -- 指定為主鍵類型

CREATE MATERIALIZED VIEW LOG ON DAVE
TABLESPACE&BISONCU_SPACE           -- 日志空間
WITH PRIMARY KEY;                   -- 指定為主鍵類型

3. 授權(quán)給中間用戶

[sql] view plain copy print ?

GRANT SELECT ON DAVE TO ANQING;
GRANT SELECT ON MLOG$_DAVE TO ANQING;

GRANT SELECT ON DAVE TO ANQING;
GRANT SELECT ON MLOG$_DAVE TO ANQING;

4. 在目標(biāo)數(shù)據(jù)庫(kù)上創(chuàng)建MATERIALIZED VIEW

[sql] view plain copy print ?

CREATE MATERIALIZED VIEW AICS_DAVE
TABLESPACE&BISONCS_SPACE
REFRESH FAST
ON DEMAND
--第一次刷新時(shí)間
--START WITH to_date('2012-01-01 20:00:00', 'yyyy-mm-dd hh24:mi:ss')
START WITH SYSDATE
--刷新時(shí)間間隔。每1天刷新一次，時(shí)間為凌晨2點(diǎn)
--NEXT TRUNC(SYSDATE,'dd')+1+2/24
NEXT SYSDATE+1/24/20
WITH PRIMARY KEY
--USING DEFAULT LOCAL ROLLBACKSEGMENT
DISABLE QUERY REWRITE AS
SELECT MODEL_ID, STATUS,MODEL_NAME, MANU_ID, DESCRIPTION, CREATE_TIME, UPDATE_TIME, SW_VERSION
FROM AICS_DAVE@LINK_DAVE;

CREATE MATERIALIZED VIEW AICS_DAVE
TABLESPACE&BISONCS_SPACE
REFRESH FAST
   ON DEMAND
   --第一次刷新時(shí)間
   --START WITH to_date('2012-01-01 20:00:00', 'yyyy-mm-dd hh24:mi:ss')
   START WITH SYSDATE
   --刷新時(shí)間間隔。每1天刷新一次，時(shí)間為凌晨2點(diǎn)
   --NEXT TRUNC(SYSDATE,'dd')+1+2/24
   NEXT SYSDATE+1/24/20
WITH PRIMARY KEY
--USING DEFAULT LOCAL ROLLBACKSEGMENT
DISABLE QUERY REWRITE AS
SELECT MODEL_ID, STATUS,MODEL_NAME, MANU_ID, DESCRIPTION, CREATE_TIME, UPDATE_TIME, SW_VERSION
FROM AICS_DAVE@LINK_DAVE;

5. 在目標(biāo)物化視圖上創(chuàng)建索引

[sql] view plain copy print ?

CREATE INDEX IDX_T_DV_CT
ON AICS_DEV_INFO (CREATE_TIME, UPDATE_TIME)
TABLESPACE &BISON_IDX;
CREATE INDEX IDX_T_DV_UT
ON AICS_DEV_INFO (UPDATE_TIME)
TABLESPACE &BISON_IDX;
CREATE INDEX I_T_DV_MSISDN
ON AICS_DEV_INFO (MSISDN)
TABLESPACE &BISON_IDX;

CREATE INDEX IDX_T_DV_CT
   ON AICS_DEV_INFO (CREATE_TIME, UPDATE_TIME)
   TABLESPACE &BISON_IDX;
 
CREATE INDEX IDX_T_DV_UT
   ON AICS_DEV_INFO (UPDATE_TIME)
   TABLESPACE &BISON_IDX;
 
CREATE INDEX I_T_DV_MSISDN
   ON AICS_DEV_INFO (MSISDN)
   TABLESPACE &BISON_IDX;

6. 物化視圖刷新說(shuō)明

（1）使用dbms_mview.refresh 手工刷新

如：

[sql] view plain copy print ?

EXEC DBMS_MVIEW.REFRESH('MV_DAVE');
--完全刷新
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'c');
EXEC DBMS_MVIEW.REFRESH('MV_DAVE','C');
--快速刷新
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'f');
EXEC DBMS_MVIEW.REFRESH('MV_DAVE','F');

EXEC DBMS_MVIEW.REFRESH('MV_DAVE');
 
--完全刷新
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'c');
EXEC DBMS_MVIEW.REFRESH('MV_DAVE','C');
 
--快速刷新
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'f');
EXEC DBMS_MVIEW.REFRESH('MV_DAVE','F');

（2）使用dbms_refresh.refresh 過(guò)程來(lái)批量刷新MV

如果我們?cè)趧?chuàng)建物化視圖的過(guò)程指定start 和next time的刷新時(shí)間，那么Oracle 會(huì)自動(dòng)創(chuàng)建刷新的job，并采用dbms_refresh.refresh 的方式。

使用這種方式刷新之前需要先make refresh group，然后才可以刷新。

Refreshmake 的語(yǔ)法可以參考：

http://docs.oracle.com/cd/B19306_01/server.102/b14227/rarrefreshpac.htm#i94057

示例：

假設(shè)存在物化視圖 MV_T1, MV_T2, MV_T3. 創(chuàng)建refresh group的語(yǔ)法如下：

[sql] view plain copy print ?

SQL> EXEC DBMS_REFRESH.MAKE('REP_TEST', 'MV_T1,MV_T2,MV_T3', SYSDATE, 'SYSDATE+ 1')
--刷新整個(gè)refresh group 組：
SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST')

SQL> EXEC DBMS_REFRESH.MAKE('REP_TEST', 'MV_T1,MV_T2,MV_T3', SYSDATE, 'SYSDATE+ 1')
 
--刷新整個(gè)refresh group 組：
SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST')

7. 刪除物化視圖及日志

[sql] view plain copy print ?

--刪除物化視圖日志：
DROP MATERIALIZED VIEW LOG ON DAVE;
--刪除物化視圖
DROP MATERIALIZED VIEW MV_DAVE;

--刪除物化視圖日志：
DROP MATERIALIZED VIEW LOG ON DAVE;
--刪除物化視圖
DROP MATERIALIZED VIEW MV_DAVE;

8. 查看物化視圖刷新狀態(tài)信息

[sql] view plain copy print ?

SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE, STALENESS FROMUSER_MVIEWS;
SQL> SELECT NAME, LAST_REFRESH FROM USER_MVIEW_REFRESH_TIMES;

SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE, STALENESS FROMUSER_MVIEWS;
SQL> SELECT NAME, LAST_REFRESH FROM USER_MVIEW_REFRESH_TIMES;

9. 查詢物化視圖日志：

[sql] view plain copy print ?

SELECT * FROM MLOG$_DAVE;

SELECT * FROM MLOG$_DAVE;

posted @ 2014-05-02 23:02 鴻雁閱讀(165) | 評(píng)論 (0) | 編輯收藏

忘記oracle的sys用戶密碼怎么修改

一、忘記除SYS、SYSTEM用戶之外的用戶的登錄密碼。

用SYS (或SYSTEM)用戶登錄: CONN SYS/PASS_WORD AS SYSDBA;

使用如下語(yǔ)句修改用戶的密碼: ALTER USER user_name IDENTIFIED BY "newpass";

注意：密碼不能全是數(shù)字。并且不能是數(shù)字開頭。否則會(huì)出現(xiàn)：ORA-00988: 口令缺失或無(wú)效

二、忘記SYS用戶，或者是SYSTEM用戶的密碼。

如果是忘記SYSTEM用戶的密碼，可以用SYS用戶登錄。然后用ALTER USER 命令修改密碼：

CONN SYS/PASS_WORD AS SYSDBA;

ALTER USER SYSTEM IDENTIFIED BY "newpass";

如果是忘記SYS用戶的密碼，可以用SYSTEM用戶登錄。然后用ALTER USER 命令修改密碼。

CONN SYSTEM/PASS_WORD ;

ALTER USER SYSTEM IDENTIFIED BY "newpass";

三、如果SYS,SYSTEM用戶的密碼都忘記或是丟失。

可以使用ORAPWD.EXE 工具修改密碼。

開始菜單->運(yùn)行->輸入‘CMD’,打開命令提示符窗口，輸入如下命令：

orapwd file=D:\oracle10g\database\pwdctcsys.ora password=newpass

這個(gè)命令重新生成了數(shù)據(jù)庫(kù)的密碼文件。密碼文件的位置在ORACLE_HOME目錄下的\database目錄下。

這個(gè)密碼是修改sys用戶的密碼。除sys其他用戶的密碼不會(huì)改變。

不過(guò)Oracle提供了兩種驗(yàn)證方式，一種是OS驗(yàn)證，另一種密碼文件驗(yàn)證方式，如果是第一種方式用以下方法修改密碼：

　　sqlplus /nolog;

　　connect / as sysdba

　　alter user sys identified by ;

　　alter user system identified by ;

　　如果是第二種方法就用上述方式修改，也可以下方法修改密碼：

　　orapwd file=pwdxxx.ora password=你設(shè)定的新密碼 entries=10

　　設(shè)定完后，重新啟動(dòng)服務(wù)，再次登陸就可以了。

oracle 11g

在本機(jī)安裝完Oracle以后，不記得sys用戶的密碼了，采用如下方法可以修改密碼：

1.打開cmd，輸入sqlplus /nolog，回車；輸入“conn / as sysdba”;輸入“alter user sys identified by 新密碼”，注意：新密碼最好以字母開頭，否則可能出現(xiàn)錯(cuò)誤Ora-00988。有了這個(gè)方法后，只要自己對(duì)oracle服務(wù)器有管理員權(quán)限，不記得密碼的時(shí)候就可以隨意修改密碼了。

2.在命令行執(zhí)行如下命令：sqlplus “/@服務(wù)名 as sysdba”,然后在sqlplus中alter user sys identified by 新密碼;

alter user system identified by 新密碼;

3.運(yùn)行到C盤根目錄

1)輸入：SET ORACLE_SID = 你的SID名稱

2)輸入：sqlplus/nolog

3)輸入：connect/as sysdba

4)輸入：alert user sys identified by sys

5)輸入：alert user system identified by system

6)更改完成，密碼是Oracle數(shù)據(jù)庫(kù)初始密碼

4.首先,在CMD下輸入SQLPLUS/NOLOG然后再在出來(lái)的界面中打入CONN/AS SYSDBA,這樣就會(huì)以本地系統(tǒng)登錄的用戶為信任用戶進(jìn)入數(shù)據(jù)庫(kù)的操作.解決這個(gè)問(wèn)題的方式有兩種,一個(gè)是：ALTER USER （USERNAME） IDENTIFIED BY “密碼”;這個(gè)是可以改變USERNAME的密碼.當(dāng)然這個(gè)USERNAME必須已經(jīng)存在的

另一種是：CREATE USER (USERNAME) IDENTIFIED BY “密碼”;改變用戶權(quán)限的命令是：GRANT ROLES TO （USERNAME）;以上2種方法都是針對(duì)ORACLE 9I的版本。

5.用orapwd.exe命令，可以修改命令。

orapwd file=’/oracle/pwdsid.ora’ password=123456這個(gè)命令是修改sys用戶的密碼。你把生成的文件覆蓋原來(lái)的密碼文件。除sys其他用戶的密碼不會(huì)改變。

6.su - oracle

sqlplus /nolog

conn / as sysdba

startup (如果數(shù)據(jù)庫(kù)不是處于啟動(dòng)狀態(tài)則啟動(dòng))

alter user sys identified by 123456

然后就可以使用sys用戶密碼登陸了

操作系統(tǒng)認(rèn)證方式

conn / as sysdba

alter user sys identified by xxx

posted @ 2014-05-02 13:16 鴻雁閱讀(145) | 評(píng)論 (0) | 編輯收藏

oracle修改密碼、添加用戶及授權(quán)

忘記密碼處理

登錄：sqlplus/as sysdba;
修改：alter user username identified by 密碼;

創(chuàng)建新用戶

create user 用戶名 identified by 密碼 default tablespace 表空間名

用戶授權(quán)

Grant create session to SYSDATA；//授予SYSDATA連接數(shù)據(jù)的權(quán)限
系統(tǒng)權(quán)限如下：
Create session：連接數(shù)據(jù)庫(kù)
Create sequence：創(chuàng)建序列
Create synonym：創(chuàng)建同名對(duì)象
Create table：創(chuàng)建表
Create any table：創(chuàng)建任何模式的表
Drop table：刪除表
Create procedure：創(chuàng)建存儲(chǔ)過(guò)程
Execute any procedure：執(zhí)行任何模式的存儲(chǔ)過(guò)程
Create user：創(chuàng)建用戶
Create view：創(chuàng)建視圖
Drop user：刪除用戶
Drop any table：刪除任何模式的表
向用戶授予系統(tǒng)特權(quán)

Grant execute any procedure toSYSDATA with admin option
此句意思為為sha用戶創(chuàng)建系統(tǒng)特權(quán)并且可以用SYSDATA為別的用戶授予權(quán)限

連接SYSDATA用戶connect SYSDATA/admin

可以創(chuàng)建別的用戶

查看用戶權(quán)限

進(jìn)入SYSDATA用戶connect SYSDATA/admin

Select * from user_sys_privs;可以查看到一個(gè)列表，
列代表用戶名權(quán)限是否可以給別的用戶附加權(quán)限（N、Y）

行代表權(quán)限都有那些

撤銷用戶授予的權(quán)限

必須連接connect system/admin

撤銷權(quán)限r(nóng)evoke是系統(tǒng)撤銷權(quán)限的關(guān)鍵詞

Revoke execute any procedure from SYSDATA；
更改用戶密碼

進(jìn)入SYSDATA用戶connect SYSDATA/admin

Password輸入舊密碼輸入新密碼倆邊OK

或者用alter user SYSDATA identified by xinmima

刪除用戶

Connect system/admin
Drop user SYSDATA；
為用戶授予角色

Grant dba to SYSDATA；
Grant connect to SYSDATA；
Linux下的plsql創(chuàng)建用戶

create user SYSDATA identified by admin
Default tablespace user
Temporary tablespace temp;
Granr unlimited tablespace to SYSDATA；//權(quán)限
Grant dba to SYSDATA；//角色
Grant connect to SYSDATA；

select * from dba_users; //用戶表
select * from dba_tablespaces; //表空間

http://database.51cto.com/art/200911/160296.htm

Oracle 權(quán)限設(shè)置
一、權(quán)限分類：
系統(tǒng)權(quán)限：系統(tǒng)規(guī)定用戶使用數(shù)據(jù)庫(kù)的權(quán)限。（系統(tǒng)權(quán)限是對(duì)用戶而言)。
實(shí)體權(quán)限：某種權(quán)限用戶對(duì)其它用戶的表或視圖的存取權(quán)限。（是針對(duì)表或視圖而言的）。

二、系統(tǒng)權(quán)限管理：
1、系統(tǒng)權(quán)限分類：
DBA: 擁有全部特權(quán)，是系統(tǒng)最高權(quán)限，只有DBA才可以創(chuàng)建數(shù)據(jù)庫(kù)結(jié)構(gòu)。
RESOURCE:擁有Resource權(quán)限的用戶只可以創(chuàng)建實(shí)體，不可以創(chuàng)建數(shù)據(jù)庫(kù)結(jié)構(gòu)。
CONNECT:擁有Connect權(quán)限的用戶只可以登錄Oracle，不可以創(chuàng)建實(shí)體，不可以創(chuàng)建數(shù)據(jù)庫(kù)結(jié)構(gòu)。
對(duì)于普通用戶：授予connect, resource權(quán)限。
對(duì)于DBA管理用戶：授予connect，resource, dba權(quán)限。

2、系統(tǒng)權(quán)限授權(quán)命令：
[系統(tǒng)權(quán)限只能由DBA用戶授出：sys, system(最開始只能是這兩個(gè)用戶)]
授權(quán)命令：SQL> grant connect, resource, dba to 用戶名1 [,用戶名2]…;
[普通用戶通過(guò)授權(quán)可以具有與system相同的用戶權(quán)限，但永遠(yuǎn)不能達(dá)到與sys用戶相同的權(quán)限，system用戶的權(quán)限也可以被回收。]
例：
SQL> connect system/manager
SQL> Create user user50 identified by user50;
SQL> grant connect, resource to user50;
查詢用戶擁有哪里權(quán)限：
SQL> select * from dba_role_privs;
SQL> select * from dba_sys_privs;
SQL> select * from role_sys_privs;
刪除用戶：SQL> drop user 用戶名 cascade; //加上cascade則將用戶連同其創(chuàng)建的東西全部刪除

3、系統(tǒng)權(quán)限傳遞：
增加WITH ADMIN OPTION選項(xiàng)，則得到的權(quán)限可以傳遞。
SQL> grant connect, resorce to user50 with admin option; //可以傳遞所獲權(quán)限。

4、系統(tǒng)權(quán)限回收：系統(tǒng)權(quán)限只能由DBA用戶回收
命令：SQL> Revoke connect, resource from user50;

說(shuō)明：
1）如果使用WITH ADMIN OPTION為某個(gè)用戶授予系統(tǒng)權(quán)限，那么對(duì)于被這個(gè)用戶授予相同權(quán)限的所有用戶來(lái)說(shuō)，取消該用戶的系統(tǒng)權(quán)限并不會(huì)級(jí)聯(lián)取消這些用戶的相同權(quán)限。
2）系統(tǒng)權(quán)限無(wú)級(jí)聯(lián)，即A授予B權(quán)限，B授予C權(quán)限，如果A收回B的權(quán)限，C的權(quán)限不受影響；系統(tǒng)權(quán)限可以跨用戶回收，即A可以直接收回C用戶的權(quán)限。

三、實(shí)體權(quán)限管理
1、實(shí)體權(quán)限分類：select, update, insert, alter, index, delete, all //all包括所有權(quán)限
execute //執(zhí)行存儲(chǔ)過(guò)程權(quán)限
user01:
SQL> grant select, update, insert on product to user02;
SQL> grant all on product to user02;
user02:
SQL> select * from user01.product;
// 此時(shí)user02查user_tables，不包括user01.product這個(gè)表，但如果查all_tables則可以查到，因?yàn)樗梢栽L問(wèn)。

2. 將表的操作權(quán)限授予全體用戶：
SQL> grant all on product to public; // public表示是所有的用戶，這里的all權(quán)限不包括drop。
[實(shí)體權(quán)限數(shù)據(jù)字典]:
SQL> select owner, table_name from all_tables; // 用戶可以查詢的表
SQL> select table_name from user_tables; // 用戶創(chuàng)建的表
SQL> select grantor, table_schema, table_name, privilege from all_tab_privs; // 獲權(quán)可以存取的表（被授權(quán)的）
SQL> select grantee, owner, table_name, privilege from user_tab_privs; // 授出權(quán)限的表(授出的權(quán)限)

3. DBA用戶可以操作全體用戶的任意基表(無(wú)需授權(quán)，包括刪除)：
DBA用戶：
SQL> Create table stud02.product(
id number(10),
name varchar2(20));
SQL> drop table stud02.emp;
SQL> create table stud02.employee
as
select * from scott.emp;

4. 實(shí)體權(quán)限傳遞(with grant option)：
user01:
SQL> grant select, update on product to user02 with grant option; // user02得到權(quán)限，并可以傳遞。

5. 實(shí)體權(quán)限回收：
user01:
SQL>Revoke select, update on product from user02; //傳遞的權(quán)限將全部丟失。

說(shuō)明
1）如果取消某個(gè)用戶的對(duì)象權(quán)限，那么對(duì)于這個(gè)用戶使用WITH GRANT OPTION授予權(quán)限的用戶來(lái)說(shuō)，同樣還會(huì)取消這些用戶的相同權(quán)限，也就是說(shuō)取消授權(quán)時(shí)級(jí)聯(lián)的。

Oracle 用戶管理
一、創(chuàng)建用戶的Profile文件
SQL> create profile student limit // student為資源文件名
FAILED_LOGIN_ATTEMPTS 3 //指定鎖定用戶的登錄失敗次數(shù)
PASSWORD_LOCK_TIME 5 //指定用戶被鎖定天數(shù)
PASSWORD_LIFE_TIME 30 //指定口令可用天數(shù)

二、創(chuàng)建用戶
SQL> Create User username
Identified by password
Default Tablespace tablespace
Temporary Tablespace tablespace
Profile profile
Quota integer/unlimited on tablespace;
例:
SQL> Create user acc01
identified by acc01 // 如果密碼是數(shù)字，請(qǐng)用雙引號(hào)括起來(lái)
default tablespace account
temporary tablespace temp
profile default
quota 50m on account;
SQL> grant connect, resource to acc01;
[*] 查詢用戶缺省表空間、臨時(shí)表空間
SQL> select username, default_tablespace, temporary_tablespace from dba_users;
[*] 查詢系統(tǒng)資源文件名：
SQL> select * from dba_profiles;
資源文件類似表，一旦創(chuàng)建就會(huì)保存在數(shù)據(jù)庫(kù)中。
SQL> select username, profile, default_tablespace, temporary_tablespace from dba_users;
SQL> create profile common limit
failed_login_attempts 5
idle_time 5;

SQL> Alter user acc01 profile common;

三、修改用戶：
SQL> Alter User 用戶名
Identified 口令
Default Tablespace tablespace
Temporary Tablespace tablespace
Profile profile
Quota integer/unlimited on tablespace;

1、修改口令字：
SQL>Alter user acc01 identified by “12345″;
2、修改用戶缺省表空間：
SQL> Alter user acc01 default tablespace users;
3、修改用戶臨時(shí)表空間
SQL> Alter user acc01 temporary tablespace temp_data;
4、強(qiáng)制用戶修改口令字：
SQL> Alter user acc01 password expire;
5、將用戶加鎖
SQL> Alter user acc01 account lock; // 加鎖
SQL> Alter user acc01 account unlock; // 解鎖

四、刪除用戶
SQL>drop user 用戶名; //用戶沒(méi)有建任何實(shí)體
SQL> drop user 用戶名 CASCADE; // 將用戶及其所建實(shí)體全部刪除
*1. 當(dāng)前正連接的用戶不得刪除。

五、監(jiān)視用戶：
1、查詢用戶會(huì)話信息：
SQL> select username, sid, serial#, machine from v$session;
2、刪除用戶會(huì)話信息：
SQL> Alter system kill session ‘sid, serial#’;
3、查詢用戶SQL語(yǔ)句：
SQL> select user_name, sql_text from v$open_cursor;

Oracle 角色管理
一、何為角色
　　角色。角色是一組權(quán)限的集合，將角色賦給一個(gè)用戶，這個(gè)用戶就擁有了這個(gè)角色中的所有權(quán)限。

二、系統(tǒng)預(yù)定義角色
　　預(yù)定義角色是在數(shù)據(jù)庫(kù)安裝后，系統(tǒng)自動(dòng)創(chuàng)建的一些常用的角色。下介簡(jiǎn)單的介紹一下這些預(yù)定角色。角色所包含的權(quán)限可以用以下語(yǔ)句查詢：
sql>select * from role_sys_privs where role=’角色名’;

1．CONNECT, RESOURCE, DBA
這些預(yù)定義角色主要是為了向后兼容。其主要是用于數(shù)據(jù)庫(kù)管理。oracle建議用戶自己設(shè)計(jì)數(shù)據(jù)庫(kù)管理和安全的權(quán)限規(guī)劃，而不要簡(jiǎn)單的使用這些預(yù)定角色。將來(lái)的版本中這些角色可能不會(huì)作為預(yù)定義角色。

2．DELETE_CATALOG_ROLE， EXECUTE_CATALOG_ROLE， SELECT_CATALOG_ROLE
這些角色主要用于訪問(wèn)數(shù)據(jù)字典視圖和包。

3．EXP_FULL_DATABASE， IMP_FULL_DATABASE
這兩個(gè)角色用于數(shù)據(jù)導(dǎo)入導(dǎo)出工具的使用。

4．AQ_USER_ROLE， AQ_ADMINISTRATOR_ROLE
AQ:Advanced Query。這兩個(gè)角色用于oracle高級(jí)查詢功能。

5． SNMPAGENT
用于oracle enterprise manager和Intelligent Agent

6．RECOVERY_CATALOG_OWNER
用于創(chuàng)建擁有恢復(fù)庫(kù)的用戶。關(guān)于恢復(fù)庫(kù)的信息，參考o(jì)racle文檔《Oracle9i User-Managed Backup and Recovery Guide》

7．HS_ADMIN_ROLE
A DBA using Oracle’s heterogeneous services feature needs this role to access appropriate tables in the data dictionary.

三、管理角色
1.建一個(gè)角色
sql>create role role1;

2.授權(quán)給角色
sql>grant create any table,create procedure to role1;

3.授予角色給用戶
sql>grant role1 to user1;

4.查看角色所包含的權(quán)限
sql>select * from role_sys_privs;

5.創(chuàng)建帶有口令以角色(在生效帶有口令的角色時(shí)必須提供口令)
sql>create role role1 identified by password1;

6.修改角色：是否需要口令
sql>alter role role1 not identified;
sql>alter role role1 identified by password1;

7.設(shè)置當(dāng)前用戶要生效的角色
(注：角色的生效是一個(gè)什么概念呢？假設(shè)用戶a有b1,b2,b3三個(gè)角色，那么如果b1未生效，則b1所包含的權(quán)限對(duì)于a來(lái)講是不擁有的，只有角色生效了，角色內(nèi)的權(quán)限才作用于用戶，最大可生效角色數(shù)由參數(shù)MAX_ENABLED_ROLES設(shè)定；在用戶登錄后，oracle將所有直接賦給用戶的權(quán)限和用戶默認(rèn)角色中的權(quán)限賦給用戶。）
sql>set role role1;//使role1生效
sql>set role role,role2;//使role1,role2生效
sql>set role role1 identified by password1;//使用帶有口令的role1生效
sql>set role all;//使用該用戶的所有角色生效
sql>set role none;//設(shè)置所有角色失效
sql>set role all except role1;//除role1外的該用戶的所有其它角色生效。
sql>select * from SESSION_ROLES;//查看當(dāng)前用戶的生效的角色。

8.修改指定用戶，設(shè)置其默認(rèn)角色
sql>alter user user1 default role role1;
sql>alter user user1 default role all except role1;
詳見(jiàn)oracle參考文檔

9.刪除角色
sql>drop role role1;
角色刪除后，原來(lái)?yè)碛迷摻巧挠脩艟筒辉贀碛性摻巧?，相?yīng)的權(quán)限也就沒(méi)有了。

說(shuō)明:
1)無(wú)法使用WITH GRANT OPTION為角色授予對(duì)象權(quán)限
2)可以使用WITH ADMIN OPTION 為角色授予系統(tǒng)權(quán)限,取消時(shí)不是級(jí)聯(lián)

posted @ 2014-05-02 11:05 鴻雁閱讀(200) | 評(píng)論 (0) | 編輯收藏

js合并指定列值重復(fù)的單元格

posted @ 2013-07-06 15:09 鴻雁閱讀(893) | 評(píng)論 (0) | 編輯收藏

僅列出標(biāo)題

The important thing in life is to have a great aim , and the determination

導(dǎo)航

留言簿(7)

隨筆分類

隨筆檔案

文章檔案

相冊(cè)

閱讀排行榜

評(píng)論排行榜

常用鏈接

統(tǒng)計(jì)

IT技術(shù)鏈接

保險(xiǎn)相關(guān)

友情鏈接

基金知識(shí)

生活相關(guān)

最新評(píng)論

解決ORA-12560: TNS: 協(xié)議適配器錯(cuò)誤

Oracle優(yōu)化全攻略一（Oracle SQL Hint）

Oracle 執(zhí)行計(jì)劃（Explain Plan）說(shuō)明

使用EXPLAIN PLAN獲取SQL語(yǔ)句執(zhí)行計(jì)劃

執(zhí)行計(jì)劃的使用(EXPLAIN)

《Oracle物化視圖實(shí)戰(zhàn)手冊(cè)》

Oracle 物化視圖說(shuō)明

一．物化視圖概述

1.1 物化視圖可以分為以下三種類型

1.2 物化視圖，根據(jù)不同的著重點(diǎn)可以有不同的分類:

1.3 物化視圖有三種刷新方式：COMPLETE、FAST和 FORCE。

1.4 物化視圖Refresh子句的其他說(shuō)明與示例

1.4.1 主鍵和ROWD子句：

1.4.2 刷新時(shí)間

1.5 ON PREBUILD TABLE 說(shuō)明

1.6 物化視圖分區(qū)

二．物化視圖操作示例

忘記oracle的sys用戶密碼怎么修改

oracle修改密碼、添加用戶及授權(quán)

js合并指定列值重復(fù)的單元格

導(dǎo)航

留言簿(7)

隨筆分類

隨筆檔案

文章檔案

相冊(cè)

閱讀排行榜

評(píng)論排行榜

常用鏈接

統(tǒng)計(jì)

IT技術(shù)鏈接

保險(xiǎn)相關(guān)

友情鏈接

基金知識(shí)

生活相關(guān)

最新評(píng)論

一． 物化視圖概述

1.1 物化視圖可以分為以下三種類型

1.2 物化視圖，根據(jù)不同的著重點(diǎn)可以有不同的分類:

1.3 物化視圖有三種刷新方式：COMPLETE、FAST和 FORCE。

1.4 物化視圖Refresh子句的其他說(shuō)明與示例

1.4.1 主鍵和ROWD子句：

1.4.2 刷新時(shí)間

1.5 ON PREBUILD TABLE 說(shuō)明

1.6 物化視圖分區(qū)

二． 物化視圖操作示例

一．物化視圖概述

1.3 物化視圖有三種刷新方式：COMPLETE、FAST和 FORCE。

二．物化視圖操作示例