oracle系列（四）oracle事務和常用資料庫對象

部落客QQ：819594300

有什麼疑問的朋友可以聯系部落客，部落客會幫你們解答，謝謝支援！

前言：本次内容的相關知識點我們在學習sqlserver2008R2的時候介紹過一些，包括：事務、索引、視圖等。那麼今天我們學習在oracle上實作這些重要的内容，以實作資料庫的優化。

一、事務

1、簡介

事務是資料處理的核心，是業務上的一個邏輯單元，它能夠保證其中對資料所有的操作，要麼全部成功，要麼全部失敗。DBMS通過事務的管理來協調使用者的并發行為，減少使用者通路資源的沖突。

1）顯示送出：當事務遇到COMMIT指令時，将結束事務并永久儲存所有的更改的資料。

2）顯示復原：當事務遇到ROLLBACK指令時，也将結束事務的執行，但是此時它復原所有更改的資料到事務開始時的原始值，即取消更改，資料沒有變化。

3）DDL語句：一旦使用者執行了DDL（資料定義語言，如create，drop等）語句，則之前的所有DML（資料操作語言）操作作為一個事務送出，這種送出稱為隐示送出。

4）正常結束程式：如果oracle資料庫應用程式正常結束，如使用sqlplus工具更改了資料，而正常退出該程式（exit），則oracle自動送出事務。

5）非正常地結束程式：當程式崩潰或意外終止時，所有資料更改都被復原，這種復原成為隐示復原。

2、事務的特點

事務有4個特性，簡寫為ACID特性。

1）原則性：以轉賬操作為例，轉出賬戶餘額減少和轉入餘額增加是兩個DML語句，但是必須作為一個不可分割的完整操作。要麼同時成功，要麼同時失敗，隻轉出而沒有轉入顯然是不可接受的。

2）一緻性：無論是在事務前、事務中、事務後，資料庫始終處于一緻的狀态。例如：轉賬前分别是2000和1000，總金額是3000，轉賬300後分别是1700和1300，總金額還是3000.這就叫做一緻性。不一緻就是在某個時間點查詢到的總金額不是3000.

3）隔離性：在某個時間段，肯定有很多人都在轉賬，每個人的轉賬都是在自己的事務中，是以在一個資料庫中，會有很多事物同時存在。雖然同時存在很多事物，但是事物之間不會互相影響。

4）持久性：如果事物送出成功，則資料修改永遠生效，如果是復原，則資料完全沒有沒修改，就相當于沒有這件事情發生。

3、學會事物的控制

1）使用COMMIT和ROLLBACK實作事物控制

COMMIT：送出事物，把事物中對資料庫的修改進行永久儲存。

ROLLBACK：復原事物，取消對資料庫所做的任何修改。

首先執行插入資料。

執行COMMIT送出事物，資料将會永久儲存。

再次插入資料，并執行rollback復原。

查詢沒有發現70這一行資料

2）使用AUTOCOMMIT實作事物的自動送出

Oracle提供了一種自動送出DML操作的方式，這樣一旦使用者執行了DML操作，如UPDATE，DELETE等，資料就會自動送出。

例2：使用autocommit實作事物自動送出，設定autocommit為ON

說明：隻要提前執行了set autocommit on 指令，資料就會自動送出，及時執行了復原資料也會依然儲存。

3）驗證隔離性

（1）建立表yuangong，并插入資料。

此時insert記錄的事務并沒有送出，沒有送出事務就沒有真正的完成，此時還有rollback的機會。

（2）查詢一下

（3）然後打開一個新的sqlplus會話，檢視表時會發現并沒有新插入的記錄，這是事物的隔離性。

（4）在第一個sqlplus會話中送出事物

（5）送出之後才能在第二個會話中看到被插入的第四條記錄

4）驗證持久性

一旦使用commit指令來結束某個事務，那麼就必須保證資料庫不丢失這個事務。在事務進行期間，隔離性的原則要求除了指定會話涉及的使用者之外的任何使用者都不能檢視目前所做的變化。不過事務一旦完成，所有使用者都必須能夠立即看到所做的變化，同時資料庫必須保證這些變化絕不會丢失。Oracle通過使用日志檔案來滿足這個需求。日志檔案具有兩種形式：聯機重做日志檔案，歸檔重做日志檔案。

一個正确配置的oracle資料庫是不可能丢失資料的。當然使用者的錯誤（包括不恰當的DML或删除對象）也會造成資料的丢失。DDL語句有自動送出功能（create、drop、truncate、alter）

（1）

（2）建立表students，并插入一條資料

（3）復原事務

（4）再次寫入資料

（5）退出sqlplus

（6）在另外一個sqlplus中檢視students表中的記錄，會發現新插入的lisi的記錄了。如果使用sqlplus工具更改了資料之後，正常退出sqlplus時，oracle會自動送出事物。

關于事物的總結：

1. 需要注意的是，Commit:隻是用來确認這個資料已經正式的修改了，不一定非得寫入硬碟，DBWn什麼都不做。執行commit指令時發生的所有實體操作時LGWR程序将日志緩沖區的内容寫入磁盤。DBWN程序完全沒有執行任何操作。DBWN程序與送出事物處理沒有關系，不過最終DBWN程序會将變化的資料塊寫入磁盤。

2. commit和rollback語句隻應于DML語句，我們無法復原DDL語句。DDL語句一旦被執行就會立即具有持久狀态。

3.自動送出和隐式送出：oracle在某些情況下可以進行自動送出：執行DDL語句是一種情況，退出某個使用者程序也是一種自動送出。

二、索引

1、索引的含義

Oracle 資料庫對象又稱模式對象，資料庫對象是邏輯結構的集合，最基本的資料庫對象是表，索引也是其中之一。其他資料庫對象包括：

索引是oracle的一個對象，是與表關聯的可選結構，提供了一種快速通路資料的途徑，提高了資料庫檢索性能。索引使資料庫程式無需對整個表進行掃描，就可以在其中找到所需要的資料。就像書的目錄，可以通過目錄快速查找所需資訊，無需閱讀整本書。

2、索引的特點

适當地使用索引可以提高查詢速度

可以對表的一列或多列建立索引

建立索引的數量沒有限制

索引需要磁盤存儲，可以指定表空間，由oracle自動維護

索引對使用者透明，檢索時是否使用索引由oracle自身決定

Oracle資料庫管理系統在通路資料時使用以下三種方式：

全表掃描

通過ROWID（行位址，快速通路表的一行）

使用索引

注：當沒有索引或者不選擇使用索引時就用全表掃描的方式

3、索引的分類

1）B樹索引結構

索引的頂部為根，其中包含指向下一級索引的項。下一級為分支塊，分支塊又指向索引中下一級的塊，最低一級的塊稱為葉節點，其中包含指向表資料行的索引項。葉節點為雙向連接配接，有助于按關鍵字值得升序和降序掃描索引。

例如：查詢id從2到31行的資料

上圖中使用索引周遊過程如下：

先找到id<=50的分支塊，再找到30-40的分支塊，在找到id=31對應的索引項，之後通過葉節點雙向連結，平行地找到包含id=2的索引塊，完成對id的查詢。

4、建立索引的文法

create [unique] index 索引名稱 on 表名（列名）[tablespace 表空間名稱]

解釋：

[unique]用于指定唯一索引，預設情況下為非唯一索引

[tablespace]為索引指定表空間

1）建立标準索引

SQL> create index index_name on tablename(columnname)

tablespace index_tbs;

2）重建索引

SQL> alter index index_namerebuld;

3）合并索引碎片

SQL> alter index index_name coalesce

4）删除索引

SQL> drop indexindex_name;

例1：

1）在雇員表（emp）中，為雇員名稱（ename）列建立b樹索引。

5、建立唯一索引

確定在定義索引的列中沒有重複值

Oracle 自動在表的主鍵列上建立唯一索引

使用CREATE UNIQUE INDEX語句建立唯一索引

文法如下：

SQL> CREATE UNIQUE INDEX index_name

ONtablename(columnname);

例：在薪水級别（salgrade）表中，為級别編号grade列建立唯一索引。

6、反向鍵索引

與正常B樹索引相反，反向鍵索引在保持列順序的同時反轉索引列的位元組。反向鍵索引通過反轉索引鍵的資料值，使得索引的修改平均分布到整個索引樹上。主要應用于所多個執行個體同時通路一個資料庫的場景中。如下圖：

如果在正常的B樹索引情況下，由于兩個雇員号empno索引在索引樹種的位置相近而處于同一個索引塊中，多個執行個體同時更新時會發生沖突，進而導緻i/o通路上的瓶頸。是以這時候可以使用反向鍵索引。反向鍵索引通常建立在一些連續增長的列上，如：編号。

例：在雇員emp表中，為雇員編号empno列建立反向鍵索引。CREATE INDEX emp_empno_reverse_idx ON emp(empno) REVERSE;

提示此列已經建立索引了，執行下面指令查詢索引有哪些：

下面查詢一下PK_EMP索引是為哪列建立的：

由上圖可以看出，PK_EMP索引是為empno列建立的，是以上面建立反向鍵索引建立不了，說明相同的列不能建立多個索引。

是以要麼把PK_EMP索引删除，要麼就保留這個索引，不建立反向鍵索引

那我們現在把原來的PK_EMP删除

提示無法删除，因為這個索引的表EMP有主鍵，想删除索引，必須去掉主鍵，指令如下：

然後建立反向鍵索引：

查詢建立是否成功：

7、位圖索引

位圖索引适合低于基數的列，即該列的值是有限的幾個。例如：雇員表中的工種（job）列，即便是幾百萬條雇員記錄，工種也是有限的。Job列可以作為位圖索引，類似的還有圖書表中的圖書類别列等。

圖索引不直接存儲ROWID，而是存儲位元組位到ROWID的映射，減少響應時間,節省空間占用。位圖索引不應當在頻發發生insert、update、delete操作的表上使用，這是因為單個位圖索引指向表的很多資料行，當修改索引項時需要将其指向的資料行全部鎖定，這會嚴重降低資料庫的并發處理能力。位圖索引适合用于資料倉庫和決策支援系統中。

例：在雇員emp表中，為工種（job）列建立位圖索引。

基本文法：

CREATE BITMAP INDEX weitu ON emp(job);

查詢一下建立是否成功:

8、組合索引

類似sqlserver的複合索引，在表内多列上建立索引。索引中的列不必與表中的列順序一緻，也不必互相鄰接。

例：雇員表中部門和職務列上的索引。組合索引的列最多包含32列。

9、基于函數的索引

需要建立的索引需要使用表中一列或多列的函數或表達式，也可以将基于函數的索引建立為B樹索引或位圖索引。

SQL> CREATE INDEX emp_ename_upper_idx

ON tablename(UPPER(columnname));

例：在雇員（emp）表中，為雇員名稱（ename）列建立小寫函數索引。

現在建立基于函數的索引：

10、建立索引的原則

頻繁搜尋的列可以作為索引列

經常排序，分組的列可以作為索引

經常用作連接配接的列（主鍵/外鍵）可以作為索引

将索引放在一個單獨的表空間中，不要放在有回退段、臨時段和表的表空間中

對于大型索引而言，考慮使用NOLOGIN子句建立大型索引。

根據業務資料發生頻率，定期重新生成或重新組織索引，進行碎片整理。

例：将索引放在一個單獨的表空間中

1）使用sys登入建立表空間

2）修改索引到表空間。

3）查詢一下是否修改。

由上圖可以看出表空間已經改為了new_tbs。

例2：使用nologging子句

11、檢視索引列相關的資訊：索引名、表名、索引列。

12、維護索引

1）重建索引

索引需要維護，如果建立了索引的表中有大量的删除和插入操作，會使得索引很大，因為删除操作後，删除值的索引空間不能被自動重新使用，對于大表和DML操作很頻繁的表，索引的維護是很重要的。Oracle提供了rebuild指令來重建索引。使索引空間可以重用删除值所占用的空間，使索引更加緊湊。

2）合并索引碎片

合并索引碎片可以釋放部分磁盤空間，是索引維護的一種重要方式，也是維護磁盤空間的方式，類似于磁盤碎片整理，把不用的空間釋放出來再利用。

3）删除索引

三、視圖

1、概述

視圖是一個虛表，不占用實體空間，因為視圖本身的定義語句存儲在資料字典裡，視圖中的資料是一個或多個實際表中獲得的。那些用于産生視圖的表叫做該視圖的基表。一個視圖也可以從另一個視圖中産生。

2、視圖的優點：

1) 提供了另外一種級别的表安全性

2) 隐藏的資料的複雜性：一個視圖可能是用多表連接配接定義的，但使用者不需要知道多表連接配接的語句也可以查詢資料。

3) 簡化的使用者的SQL指令：查詢視圖的時候不需要寫出複雜的查詢語句，隻需要查詢視圖名稱即可。

4) 隔離基表結構的改變：視圖建立好了之後，如果修改了表的結構，也不會影響視圖的。

5) 通過重命名列，從另一個角度提供資料：例如在銷售系統中，每日下班前要對當日資料進行彙總，在銷售人員眼中，該彙總表成為日銷售統計表，在财務人眼中，該銷售表成為銷售日報表。

3、建立視圖的文法：

1） CREATE [OR REPLACE] [FORCE] VIEW

view_name[(alias[, alias]...)]

ASselect_statement

[WITH CHECKOPTION]

[WITH READONLY];

OR REPLACE:如果視圖已存在，此選項将重新建立該視圖。

FORCE:如果使用此關鍵字，則無論基表是否存在，都将建立視圖。

NOFORCE：這是預設值，如果使用此關鍵字，則僅當基表存在時才建立視圖。

VIEW_NAME：要建立視圖的名稱

Alias：指定由視圖的查詢所選擇的表達式或列的别名。别名的數目必須與視圖所選擇的表達式的數目相比對。

Select_statement:select語句

WITH CHECK OPTION :此選項指定隻能插入或更新視圖可以通路的行。術語constraint表示為CHECK OPTION限制指定的名稱。

WITH READ ONLY：此選項保證不能在此視圖上執行任何修改操作。

2）視圖中的ORDER BY子句

可以在建立視圖時在SELECT語句中使用ORDERBY子句，以便按照特定的順序進行排序，這樣，在查詢視圖時即使不使用ORDER BY子句，結果集也會按指定的順序進行排列。

3）建立帶有錯誤的視圖

如果在create view語句中使用FORCE選項，即使存在系列情況，oracle也會建立視圖。

視圖定義的查詢引用了一個不存在的表。

視圖定義的查詢引用了現有表中無效的列。

視圖的所有者沒有所需的權限。

在這些情況下，oracle僅檢查createview語句中的文法錯誤。如果文法正确，将會建立視圖，并将視圖的定義存在資料字典中。但是，該視圖卻不能使用。這種視圖被認為是“帶錯誤建立”的。可使用SHOWERRORS VIEW視圖名來檢視錯誤。

4、實驗案例：對單表視圖的操作

1）連接配接到oracle，使用scott使用者登入

2）建立表order_master

3）插入資料

4）建立訂單狀态為"p"的視圖，提示沒有建立視圖的權限。

5）授予建立視圖的權限（使用sys使用者登入）

6）再次建立視圖

7）查詢視圖

8）通過視圖修改資料，将狀态為p的訂單修改為d，但是修改完成之後再次查詢視圖将查不出任何資料，因為修改了建立視圖時作為條件的列。

9）為了避免修改視圖後查詢不到的現象，使用with_check_option語句建立檢查限制，以防止上述情況發生，同時可以使用constraint指定限制名稱。

10）再次寫入資料，并且再次更新（出現違規提示）

11）看一下最終結果

5、實驗案例：建立隻讀視圖

1）使用read only建立隻讀視圖

2）查詢視圖

3）再次更新視圖，提示無法對隻讀視圖進行dml操作。

6、實驗案例：建立帶有錯誤的視圖

1）使用force建立帶有錯誤的視圖，其中venmast表不存在，但是也能建立成功。

2）建立表

3）重新編譯現有視圖，使其生效。

4）測試查詢視圖

7、建立order by 子句的視圖（查詢視圖之後會自動排序）

然後再建立一個降序的：

8、DML語句和複雜視圖

DML語句是指用于修改資料的insert、delete和update語句。因為視圖是一個虛拟的表，是以這些語句也可以與視圖一同使用。一般情況下不通過視圖修改資料，而是直接修改基表，因為這樣條例更清晰。在視圖上使用DML語句有如下限制（相對于表）。

1)DML語句隻能修改視圖中的一個基表。

2)如果過記錄的修改違反了基表的限制條件，則将無法更新視圖。

3)如果建立的視圖包含連接配接運算符，DISTINCT運算符、集合運算符、聚合函數和groupby子句，則将無法更新視圖。

4)如果建立的視圖包含僞清單達式，則将無法更新視圖。

簡單視圖基于單個基表，不包括函數和分組函數，那麼可以在此視圖中進行insert、update、delete操作，這些操作實際上在基表中插入、更新和删除行。

複雜視圖從多個表提取資料，包括函數分組函數。複雜視圖不一定能進行DML操作。

1）查詢視圖

通過資料字典user_views可以查詢目前使用者下建立的視圖名稱。

2）删除視圖

要從資料庫中删除視圖，可以使用drop view指令。

9、物化視圖

1）物化視圖的含義

物化視圖是和普通視圖相對應的。在oracle使用普通視圖時，它會重複執行建立視圖的所有sql語句，如果這樣的sql語句含有多張表的連接配接或者order by 子句，而且表資料量很大，則會非常耗時，效率非常低下，為了解決這個問題，oracle提出了物化視圖的概念。

簡單的講，物化視圖就是具有實體存儲的特殊視圖，占據實體空間，就像表一樣。物化視圖是基于表、物化視圖等建立的。他需要和源表進行同步，不斷地重新整理物化視圖中的資料。

物化視圖中有兩個重要概念:查詢重寫和物化視圖的同步。

查詢重寫：

對sql語句進行重寫，當使用者使用sql語句對基表進行查詢時，如果已經建立了基于這些表的物化視圖，oracle将自動計算和使用物化視圖來完成查詢，在某些情況下可以節約查詢時間，減少系統i/o。Oracle将這種查詢優化技術成為查詢重寫。參數QUERY_REWRITE_ENABLED決定是否使用重寫查詢，該參數為布爾型。在建立物化視圖需要使用ENABLE_QUERY REWRITE來啟動查詢重寫功能。通過SHOW指令可以檢視該參數的值。

2）物化視圖的同步：

物化視圖是基于表建立的，是以當基表變化時，需要同步資料以更新物化視圖中的資料，這樣保持物化視圖中的資料和基表的資料一緻性。Oracle提供了兩種物化視圖的重新整理方式，決定何時進行重新整理，即ON COMMIT方式和ON DEMAND方式。

ON COMMIT方式：指物化視圖在對基表的DML操作事務送出的同時進行重新整理。

ON DEMAND方式：指物化視圖在使用者需要的時候進行更新，可以手工通過DBMS_MVIEW.REFRESH等方式來進行重新整理，也可以通過JOB定時進行重新整理。

選擇重新整理方式之後，還需要選擇一種重新整理類型，重新整理類型指定重新整理時基表與物化視圖如何實作資料的同步，oracle提供了以下4種重新整理類型。

COMPLETE:對整個物化視圖進行完全的重新整理。

FAST：采用增量重新整理，隻重新整理自上次重新整理後進行的修改。

FORCE:oracle在重新整理時會去判斷是否可以進行快速重新整理，如果可以則采用FAST方式，否則采用COMPLETE方式。

NEVER:物化視圖不進行任何重新整理。

預設值是FORCE重新整理類型。

3）建立物化視圖

①　建立物化視圖的前提條件

具備建立物化視圖的權限，QUERY REWRITE的權限，以及對建立物化視圖所涉及的表的通路權限和建立表的權限。

以sys身份登入，之後授予scott使用者建立物化視圖的權限。

②　建立物化視圖日志

物化視圖日志是使用者選擇了FAST重新整理類型時使用的，以增量同步基表的變化。

對scott使用者的表DETP和表EMP建立物化視圖，是以對這兩個基表建立物化視圖日志。

③　建立物化視圖語句

通過create materializedview 語句建立物化視圖，需要注意各個參數的含義

查詢物化視圖：

④　删除物化視圖

與删除普通視圖相似，需要添加一個materialized關鍵字。

再次查詢視圖，提示視圖不存在。

四、序列

序列是用來生成唯一、連續的整數資料庫對象。序列通常用來自動生成主鍵或唯一鍵的值。序列可以按升序排列，也可以按降序排列，與excel的自動排序，以及sqlserver的辨別符是一樣的。

1、建立序列

1）使用sys登入授予scott建立序列的權限。

例1：在scott使用者建立序列号，從序号1開始，每次增加1，最大為2000，不循環，再增加會報錯。

2、通路序列

建立了序列之後，可以通過NEXTVAL和CURRVAL僞列來通路該序列的值。可以從僞列中選擇值。但是不能操縱他們的值。

NETXVAL：建立序列後第一次使用NEXTVAL時，将傳回該序列的初始值。以後再引用NETXVAL時，将使用INCREMENT BY子句的值來增加序列值，并傳回這個新值。

CURRVAL:傳回序列的目前值，即最後一次引用NEXTVAL時傳回的值。

例2：在玩具表中，需要辨別列toyid作為辨別，不需要有任何含義，可以做為主鍵。

1）建立表

2）插入資料

3）查詢資料

4）檢視序列目前值

Currval傳回序列的目前值，即最後一次引用NEXTVAL時傳回的值。

5）測試currval

3、更改序列

Alter sequence指令用于修改序列的定義。如果要進行下列操作，則會修改序列。

設定或删除MINVALUE 或MAXVALUE

修改增量值

修改緩存中的序列号的數目

不能更改序列的START WITH參數

例3：設定一個新的maxvalue，并為xulie序列打開了cycle。

例4：修改序列為沒有最大封頂值

例5：将每次增量設定為10

4、檢視序列

可以通過查詢名為user_sequences的資料字典視圖，來擷取使用者所建立的序列的詳細資訊。

5、删除序列

DROP SEQUENCE指令用于删除序列。

五、同義詞

同義詞是對象的一個别名，不占用任何的實際存儲空間，隻在oracle的資料字典中儲存其定義描述，在使用同義詞時，oracle會将其翻譯為對應對象的名稱。

1、同義詞的用途

1）簡化sql語句

如果使用者建立的表的名字很長，可以為這個表建立一個oracle同義詞來簡化語句。

2）隐藏對象的名稱和所有者

多使用者協同開發中，可以屏蔽對象的名稱及持有者。如果沒有同義詞，當操作其他使用者的表時，必須通過“使用者名.表名”的形式操作，采用了oracle同義詞之後就可以隐藏掉使用者名。例如：使用者user1要通路使用者的SCOTT的EMP表，必須使用SCOTT.emp來引用。如果為使用者建立一個名為emp的同義詞代表SCOTT.emp，那麼user1就可以用該同義詞像通路自己的表一樣引用SCOTT.emp了。

3）為分布式資料庫的遠端對象提供位置透明性

要完成遠端對象的通路，先要了解資料庫連接配接的概念。資料庫連結是一個命名的對象，說明一個資料庫到另一個資料庫的路徑，通過其可以實作不同的資料庫之間的通信。同義詞在資料庫連結中的作用就是提供位置透明性。

4）提供對資料庫對象的公共通路

公有同義詞隻是為資料庫對象定義了一個公共的别名，即其他使用者都可以通過這個别名通路，但能夠通過該别名通路成功，還要看是否已經具有資料庫對象的通路權限。

2、同義詞的分類

同義詞分為以下兩類：私有同義詞和公有同義詞

私有同義詞隻能在其模式内通路，且不能與目前模式的對象同名

公有同義詞可被所有的資料庫使用者通路

2-1：私有同義詞

私有同義詞隻能被目前模式的使用者通路，私有同義詞名稱不可與目前模式的對象名稱相同。要在自身的模式建立私有同義詞，使用者必須擁有create synonym系統權限。要在其他使用者模式建立私有同義詞，使用者必須擁有createany synonym系統權限。

2）建立私有同義詞的文法如下：

CREATE [OR REPLACE] SYNONYM [schema.]synonym_name FOR[schema.]object_name;

[OR REPLACE]:在同義詞存在的情況下替換該同義詞

synonym_name：要建立同義詞的名稱

object_name:指定要為之建立同義詞的對象的名稱。

例1：在SYSTEM模式下建立私有同義詞通路SCOTT模式下EMP表。

1）以SYSTEM使用者身份登入資料庫，并通路SCOTT下的EMP表。

2）以system身份登入資料庫,建立同義詞。

3）通路同義詞tyc，實際通路的是SCOTT的emp表（隐藏了真實的表名，提高了安全性）

例2：通路網絡服務名為orclsv的遠端資料庫中的表scott.emp

（我這裡隻有一台伺服器，是以我就把自己當做遠端伺服器，效果是一樣的）

1）以SYSTEM使用者身份登入資料庫，建立資料庫連接配接dblink_sw_orcl來連接配接遠端資料庫，其中遠端資料庫使用者名為system，密碼為123456，本地網絡伺服器名為orcl，最後查詢遠端資料庫中的表emp。

2）建立私有同義詞bieming作為遠端資料庫表emp的别名

3）通路同義詞bieming，對應的是遠端資料庫中的表。

2-2：公有同義詞

公有同義詞被所有的資料庫通路。公有同義詞可以隐藏基表的身份，并降低sql語句的複雜性。要建立公有公有同義詞，使用者必須擁有create public SYNOYM的系統權限。

例3：在scott模式下對部門表dept建立公有同義詞public_sy_dept,目的是使其他使用者可以直接通路public_sy_dept。

注意：如果不建立公有同義詞，那麼其他使用者通路scott模式下建立的同義詞，一定要加scott字首，即SCOTT.xxx。如果建立了公有同義詞，同義詞有了公有屬性，那麼其他使用者都可以使用了。

1）以system使用者身份登入資料庫，将建立公有同義詞權限給SCOTT使用者。

2）以sys使用者身份登入，建立一個新使用者zpf

3）以scott使用者身份登入資料庫，并把查詢tmp權限賦予給zpf

4)建立公有同義詞public_dept作為scott使用者dept表的别名

5）以sys使用者的身份登入資料庫，賦予給zpf有登入資料庫的權限

6）以zpf身份登入資料庫

3、删除同義詞

要删除同義詞，使用者必須擁有相應的權限。

例：删除同義詞tyc和公有同義詞public_dept，可以執行如下語句。

1)以sys身份登入，賦予zpf有删除私有和公有同義詞的權限

2）以zpf使用者身份登入，來删除system建立的私有同義詞tyc和公有同義詞public_dept

六、分區表

1、分區表的含義

Oracle允許把一個表中的所有行分成幾個部分，并将它們存儲在不同的表空間，分成的每一部分成為一個分區，被分區的表成為分區表。

對于包含大量資料的表來說，分區很有用，優點有以下幾點：

1）改善表的查詢性能。在對表進行分區後，使用者執行sql查詢時可以隻通路表中的特定分區而非整個表。

2）表更容易管理。因為分區表的資料存儲在多個部分中，按分區加載和删除資料比在表中加載和删除更容易。

3）便于備份和恢複。可以獨立地備份和恢複每個分區。

4）提高資料安全性。将不同的分區分布在不同的磁盤，可以減少所有分區的資料同時損壞的可能性。

複合一下什麼條件的表可以建成分區表：

1）資料量大于2GB。

2）已有的資料和新添加的資料有明顯的界限劃分。

表分區對使用者是透明的，及應用程式可以不知道表已被分區，在更新和查詢分區表時當做普通表來操作，但oracle優化程式知道表已被分區。

注意：要分區的表不能具有LONG和LONG RAW資料類型的列。

2、分區表的分類

Oracle提供的分區方法有範圍分區、清單分區、散列分區、複合分區、間隔分區和虛拟列分區等。其中間隔分區和虛拟列分區是oracle11g的新特性

範圍分區案例：

是一種常用的表分區方法，它是oracle引進的第一個分區類型。範圍分區用于可以根據某些條件按範圍分開的資料。如果資料均勻的分布在所建立的不同的範圍内，那麼使用範圍分區将得到最好的分區效果。範圍可以基于順序數或部分數，範圍分區技術通常基于時間（例如月或季度）

1）建立表并且分區，以age分區

2）向表中插入資料

3）查詢P1區的資料

查詢p200區的資料

4）如果向表中插入以下記錄，會提示插入的分區關鍵字未映射到任何分區

5）按範圍分區時，如果某些記錄暫時無法預測範圍，可以建立maxvalue分區，所有不在指定範圍内的記錄都會被存儲到maxvalue所在的分區中。

6）再次插入以下資料

7）查詢

8）檢視所有分區的指令

一般建立範圍分區時都會将最後一個分區設定為maxvalue，将其他資料落入此分區，一旦需要時可以利用拆分分區的技術将需要的資料從最後一個分區分離出入，單獨形成一個分區，如果沒有建立最大的分區，插入的資料查出範圍就會報錯。如果插入的資料是分區鍵上的值，則該資料落入下一個分區，例如：插入資料為20就會落入p2分區。

本文轉自Mr大表哥部落格，原文連結： http://blog.51cto.com/zpf666/1930174 如需轉載請自行聯系原作者

oracle系列（四）oracle事務和常用資料庫對象

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List