【鎖】Oracle鎖系列
【鎖】Oracle鎖系列
1 BLOG文件結構圖
2 前言部分
2.1 導讀和注意事項
各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① 鎖的概念、分類、及其模擬
② 查詢鎖的檢視及檢視之間的關聯
③ 鎖的引數（DML_LOCKS、DDL_LOCK_TIMEOUT）
④ FOR UPDATE及FOR UPDATE OF系列
⑤ 帶ONLINE和不帶ONLINE建立索引的鎖情況（是否阻塞DML操作）
⑥ 包或存過不能編譯的解決方法
⑦ ORA-08104解決方法
Tips：
① 本文在itpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）上有同步更新。
② 文章中用到的所有程式碼、相關軟體、相關資料及本文的pdf版本都請前往小麥苗的雲盤下載，小麥苗的雲盤地址見：http://blog.itpub.net/26736162/viewspace-1624453/。
③ 若網頁文章程式碼格式有錯亂，請下載pdf格式的文件來閱讀。
④ 在本篇BLOG中，程式碼輸出部分一般放在一行一列的表格中。其中，需要特別關注的地方我都用灰色背景和粉紅色字型來表示，比如在下邊的例子中，thread 1的最大歸檔日誌號為33，thread 2的最大歸檔日誌號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字型標註；對程式碼或程式碼輸出部分的註釋一般採用藍色字型表示。
|
List of Archived Logs in backup set 11 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48 1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58 2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49 2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53 [ZHLHRDB1:root]:/>lsvg -o T_XLHRD_APP1_vg rootvg [ZHLHRDB1:root]:/> 00:27:22 SQL> alter tablespace idxtbs read write; ====》2097152*512/1024/1024/1024=1G |
本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
2.2 本文簡介
有網友一直催著說發一些鎖系列的文章，其實小麥苗一直對鎖這塊也沒有徹底去研究過，今年寫書裡邊寫到了鎖的內容，乾脆就徹底把這一塊整理了一下，現在分享給大家，若有錯誤，還請大家及時指正。
文章很多內容來源於網路或Concepts的內容，若有侵權還請聯絡小麥苗刪除。
第二章 鎖
2.1 鎖的基本概念
鎖的定義：鎖（lock）機制用於管理對共享資源的併發訪問，用於多使用者的環境下，可以保證資料庫的完整性和一致性。鎖是防止訪問相同資源的事務之間的破壞性互動的機制。既可以是使用者物件(例如表或行)，也可以是對使用者不可見的系統物件(例如共享資料結構和資料字典行)。
鎖的解釋：當多個使用者併發地存取資料時，在資料庫中就會產生多個事務同時存取同一資料的情況。若對併發操作不加控制就可能會讀取和儲存不正確的資料，破壞資料庫的完整性和一致性。當事務在對某個資料物件進行操作前，先向系統發出請求，對其加鎖。加鎖後事務就對該資料物件有了一定的控制。
鎖的作用：在併發事務之間防止破壞性的互動作用，不需要使用者的動作，自動使用最低的限制級別，在事務處理期間保持。
資料庫是一個多使用者使用的共享資源。當多個使用者併發地存取資料時，在資料庫中就會產生多個事務同時存取同一資料的情況。若對併發操作不加控制就可能會讀取和儲存不正確的資料，破壞資料庫的一致性。
鎖（lock）是防止訪問相同資源（例如表或資料行等使用者物件，或記憶體中的共享資料結構及資料字典等對使用者不可見的系統物件）的事務產生破壞性互動的機制。
在任何情況下，Oracle 都能夠自動地獲得執行 SQL 語句所必須的所有鎖，無需使用者干預。Oracle 會盡可能地減少鎖產生的影響，從而最大程度地保證資料的併發訪問能力，並確保資料一致性及錯誤恢復。同時，Oracle 也支援使用者手工加鎖的操作。
Oracle 從來不會升級鎖，但是它會執行鎖轉換（lock conversion）或鎖提升（lock promotion）。
A lock is a mechanism that prevents destructive interactions, which are interactions that incorrectly update data or incorrectly alter underlying data structures, between transactions accessing shared data. Locks play a crucial row in maintaining database concurrency and consistency.
鎖是一種機制，用來防止多個共同訪問共享資料的事務之間的破壞性互動，包括不正確地更新資料或不正確地更改基礎資料結構。鎖在維護資料庫併發性和一致性當中扮演著一個關鍵的角色。
2.1.1 併發和並行
併發(concurrency)和並行(parallel)。併發意思是在資料庫中有超過兩個以上使用者對同樣的資料做修改，而並行的意思就是將一個任務分成很多小的任務，讓每一個小任務同時執行，最後將結果彙總到一起。所以說，鎖產生的原因就是併發，併發產生的原因是因為系統和客戶的需要。
2.1.2 使用鎖
在單使用者資料庫中，鎖不是必需的，因為只有一個使用者在修改資訊。但是，當多個使用者在訪問和修改資料時，資料庫必須提供一種方法，以防止對同一資料進行併發修改。鎖實現了以下重要的資料庫需求：
v ·一致性
一個會話正在檢視或更改的資料不能被其它會話更改，直到使用者會話結束。
v ·完整性
資料和結構必須按正確的順序反映對他們所做的所有更改。資料庫通過其鎖定機制，提供在多個事務之間的資料併發性、一致性、和完整性。鎖定將自動執行，並且不需要使用者操作。
執行SQL語句時，Oracle資料庫自動獲取所需的鎖。例如，在資料庫允許某個會話修改資料之前，該會話必須先鎖定資料。鎖給予該會話對資料的獨佔控制權，以便在釋放該鎖之前，任何其它事務都不可以修改被鎖定的資料。
因為資料庫的鎖定機制與事務控制緊密地繫結在一起，應用程式設計人員只需要正確地定義事務，而資料庫會自動管理鎖定。
2.1.3 鎖模式（Lock Modes）--共享和排它
Oracle資料庫自動使用最低適用的限制級別，來提供最高程度的資料併發，但還能提供非常安全的資料完整性。限制級別越低、則有更多的可用資料供其他使用者訪問。相反，限制級別越高，則其它事務為獲取其所需的鎖型別就將遭受更多的限制。
在多使用者的資料庫系統中，Oracle使用兩種模式的鎖：
2.1.4 鎖的持續時間
事務內各語句獲得的鎖在事務執行期內有效，以防止事務間破壞性的相互干擾，例如：髒讀取（dirty read），無效地更新（lost update），以及其它併發事務中具有破壞性的 DDL 操作。如果某個事務中的 SQL 語句對資料進行了修改，只有在此事務提交後開始的事務才能看到前者修改的結果。
當使用者提交（commit）或撤銷（undo）一個事務後，Oracle 將釋放此事務內各個 SQL 語句獲得的鎖。當使用者在事務內回滾到某個儲存點（savepoint）後，Oracle 也會釋放此儲存點後獲得的鎖。只有當前沒有等待被鎖資源的事務才能獲得可用資源的鎖。等待事務不會對可用資源加鎖而是繼續等待，直至擁有其所等待資源的事務完成提交或回滾。
2.2 顯式鎖定和隱式鎖定
有兩種型別：顯式鎖定和隱式鎖定。Oracle鎖被自動執行，並且不要求使用者干預的鎖為隱式鎖。對於SQL語句隱式鎖是必須的，依賴被請求的動作。隱式鎖定除SELECT外，對所有的SQL語句都發生。使用者也可以手動鎖定資料，這是顯式鎖定。
隱式鎖定：這是Oracle中使用最多的鎖。通常使用者不必宣告要對誰加鎖，Oracle 自動可以為操作的物件加鎖，這就是隱式鎖定。
顯式鎖定：使用者可以使用命令明確的要求對某一物件加鎖。顯式鎖定很少使用。
2.2.1 顯式鎖定
LOCK TABLE沒有觸發行鎖，只有TM表鎖。
|
LOCK TABLE TABLE_NAME IN ROW SHARE MODE NOWAIT; --2：RS LOCK TABLE TABLE_NAME IN SHARE UPDATE MODE; --2：RS LOCK TABLE TABLE_NAME IN ROW EXCLUSIVE MODE NOWAIT; --3：RX LOCK TABLE TABLE_NAME IN SHARE MODE; --4：S LOCK TABLE TABLE_NAME IN SHARE ROW EXCLUSIVE MODE; --5：SRX LOCK TABLE TABLE_NAME IN EXCLUSIVE MODE NOWAIT; --6：X |
2.2.2 隱式鎖定
隱式鎖定：
Select * from table_name……
Insert into table_name……
Update table_name……
Delete from table_name……
Select * from table_name for update
2.3 悲觀鎖和樂觀鎖
2.3.1 悲觀鎖
鎖在使用者修改之前就發揮作用：
Select ..for update（nowait)
Select * from tab1 for update
使用者發出這條命令之後，oracle將會對返回集中的資料建立行級封鎖，以防止其他使用者的修改。
如果此時其他使用者對上面返回結果集的資料進行dml或ddl操作都會返回一個錯誤資訊或發生阻塞。
1：對返回結果集進行update或delete操作會發生阻塞。
2：對該表進行ddl操作將會報：Ora-00054:resource busy and acquire with nowait specified.
原因分析
此時Oracle已經對返回的結果集上加了排它的行級鎖，所有其他對這些資料進行的修改或刪除操作都必須等待這個鎖的釋放，產生的外在現象就是其它的操作將發生阻塞，這個這個操作commit或rollback.
同樣這個查詢的事務將會對該表加表級鎖，不允許對該表的任何ddl操作，否則將會報出ora-00054錯誤：:resource busy and acquire with nowait specified.
會話1：
|
SYS@lhrdb S1> create table t_lock_lhr as select rownum as id,0 as type from dual connect by rownum <=3;
Table created.
SYS@lhrdb S1> select * from t_lock_lhr where id=2 and type =0 for update nowait;
ID TYPE ---------- ---------- 2 0 |
會話2：
|
SYS@lhrdb S2> select * from t_lock_lhr where id=2 and type=0 for update nowait; select * from t_lock_lhr where id=2 and type=0 for update nowait * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
|
會話1：
|
SYS@lhrdb S1> update t_lock_lhr set type=1 where id=2 and type=0;
1 row updated.
SYS@lhrdb S1> commit;
Commit complete.
SYS@lhrdb S1> select * from t_lock_lhr where id=2;
ID TYPE ---------- ---------- 2 1
|
會話2：
|
SYS@lhrdb S2> select * from t_lock_lhr where id=2 and type=0 for update nowait;
no rows selected
|
2.3.2 樂觀鎖
樂觀的認為資料在select出來到update進取並提交的這段時間資料不會被更改。這裡面有一種潛在的危險就是由於被選出的結果集並沒有被鎖定，是存在一種可能被其他使用者更改的可能。因此Oracle仍然建議是用悲觀封鎖，因為這樣會更安全。
|
會話1： SYS@lhrdb S1> select id,type,ora_rowscn from t_lock_lhr where id = 3;
ID TYPE ORA_ROWSCN ---------- ---------- ---------- 3 0 37698547 會話2： SYS@lhrdb S2> select id,type,ora_rowscn from t_lock_lhr where id = 3;
ID TYPE ORA_ROWSCN ---------- ---------- ---------- 3 0 37698547 會話1： SYS@lhrdb S1> update t_lock_lhr set type=1 where ora_rowscn=37698547 and id = 3;
1 row updated.
SYS@lhrdb S1> commit;
Commit complete. SYS@lhrdb S1> select id,type,ora_rowscn from t_lock_lhr where id = 3;
ID TYPE ORA_ROWSCN ---------- ---------- ---------- 3 1 37698591 會話2： SYS@lhrdb S2> update t_lock_lhr set type=1 where ora_rowscn=37698547 and id =3;
0 rows updated.
SYS@lhrdb S2> select id,type,ora_rowscn from t_lock_lhr where id = 3;
ID TYPE ORA_ROWSCN ---------- ---------- ---------- 3 1 37698591
|
2.3.3 更新丟失問題的解決方法
更新丟失是指多個使用者通過應用程式訪問資料庫時，由於查詢資料並返回到頁面和使用者修改完畢點選儲存按鈕將修改後的結果儲存到資料庫這個時間段（即修改資料在頁面上停留的時間）在不同使用者之間可能存在偏差，從而最先查詢資料並且最後提交資料的使用者會把其他使用者所作的修改覆蓋掉。
解決方法如下：
2.4 鎖轉換和鎖升級（Lock Conversion and Escalation）
資料庫在必要時執行鎖轉換。在鎖轉換中，資料庫自動將較低限制的表鎖轉換為較高限制的其它鎖定。一個事務在該事務中所有執行插入、更新、或刪除的行上持有行獨佔鎖。因為行鎖是在最高程度限制下獲得的，因此不要求鎖轉換，也不執行鎖轉換。鎖轉換不同於鎖升級，鎖升級發生在當某個粒度級別持有許多鎖（例如行），資料庫將其提高到更高粒度級別（例如表）。如果一個使用者鎖定了一個表中的許多行，則某些資料庫自動將行鎖升級到單個表鎖。鎖的數量減少了，但被鎖定的東西卻增加了。
Oracle資料庫永遠不會升級鎖。鎖升級極大地增加了死鎖的可能性。假定一個系統嘗試升級事務1中的鎖，但因為事務2持有該鎖，故不能成功。如果事務2在它可以繼續操作之前也需要在相同的資料上進行鎖升級，則將發生一個死鎖。
ORACLE的鎖是block裡面實現的,SQLSERVER,DB2是記憶體裡面實現的.記憶體實現有資源消耗問題,當記憶體不足會引發鎖升級,但是ORACLE不會發生鎖升級。
事務擁有在此事務內被插入（insert），更新（update），刪除（delete）的資料行的排它行級鎖（exclusive row lock）。對於資料行來說，排它行級鎖已經是限制程度最高的鎖，因此無需再進行鎖轉換（lock conversion）。
2.5 鎖的分類
Oracle能夠自動地選擇不同型別的鎖對資料併發訪問進行控制，防止使用者間破壞性的互動操作。Oracle 將自動地為事務進行鎖管理，防止其它事務對需要排它訪問的資源執行操作。當事務不再需要加鎖的資源並觸發某個事件後，鎖能夠被自動地釋放。
在事務執行期間，Oracle 能夠根據加鎖的資源及需要執行的操作自動地決定鎖的型別（types of lock）及對資源的限制級別（level of restrictiveness）。
V$LOCK_TYPE 該檢視是對DML鎖的型別的解釋。
select * from V$LOCK_TYPE v where v.IS_USER='YES';
當Oracle執行DML語句時，系統自動在所要操作的表上申請TM型別的鎖。當TM鎖獲得後，系統再自動申請TX型別的鎖，並將實際鎖定的資料行的鎖標誌位進行置位。這樣在事務加鎖前檢查TX鎖相容性時就不用再逐行檢查鎖標誌，而只需檢查TM鎖模式的相容性即可，大大提高了系統的效率。TM鎖包括了SS、SX、S、X等多種模式，在資料庫中用0－6來表示。不同的SQL操作產生不同型別的TM鎖。
在資料行上只有X鎖（排它鎖）。在Oracle資料庫中，當一個事務首次發起一個DML語句時就獲得一個TX鎖，該鎖保持到事務被提交或回滾。當兩個或多個會話在表的同一條記錄上執行DML語句時，第一個會話在該條記錄上加鎖，其它的會話處於等待狀態。當第一個會話提交後，TX鎖被釋放，其它會話才可以加鎖。
當Oracle資料庫發生TX鎖等待時，如果不及時處理常常會引起Oracle資料庫掛起，或導致死鎖的發生，產生ORA-60的錯誤。這些現象都會對實際應用產生極大的危害，如長時間未響應，大量事務失敗等。
2.5.1 DML鎖（DML Locks）
當Oracle執行DELETE,UPDATE,INSERT,SELECT FOR UPDATE DML語句時，oracle首先自動在所要操作的表上申請TM型別的鎖。當TM鎖獲得後，再自動申請TX型別的鎖，並將實際鎖定的資料行的鎖標誌位(lb 即lock bytes)進行置位。在記錄被某一會話鎖定後，其它需要訪問被鎖定物件的會話會按先進先出的方式等待鎖的釋放，對於select操作而言，並不需要任何鎖，所以即使記錄被鎖定，select語句依然可以執行，實際上，在此情況下，oracle是用到undo的內容進行一致性讀來實現的。
當Oracle執行DML語句時，系統自動在所要操作的表上申請TM型別的鎖。當TM鎖獲得後，系統再自動申請TX型別的鎖，並將實際鎖定的資料行的鎖標誌位進行置位。這樣在事務加鎖前檢查TX鎖相容性時就不用再逐行檢查鎖標誌，而只需檢查TM鎖模式的相容性即可，大大提高了系統的效率。DML語句能夠自動地獲得所需的表級鎖（table-level lock）與行級鎖（row-level lock）。
DML鎖，也稱為資料鎖，確保由多個使用者併發訪問的資料的完整性。例如，DML鎖可防止兩個客戶從一個線上書店購買某一本書所剩的最後一個拷貝。DML鎖也可以防止多個相互衝突的DML或DDL操作產生破壞性干擾。
DML語句自動獲取下列型別的鎖：
n 行鎖(TX)
n 表鎖(TM)
2.5.1.1 行鎖（Row Locks，TX）
行級鎖（row-level lock）的作用是防止兩個事務同時修改相同的資料行。當一個事務需要修改一行資料時，就需對此行資料加鎖。Oracle 對語句或事務所能獲得的行級鎖的數量沒有限制，Oracle 也不會講行級鎖的粒度升級（lock escalation）。行級鎖是粒度最精細的鎖，因此行級鎖能夠提供最好的資料併發訪問能力及資料處理能力。
Oracle 同時支援多版本併發訪問控制（multiversion concurrency control）及行級鎖技術（row-level locking），因此使用者只有在訪問相同資料行時才會出現競爭，具體來說：
l 讀取操作無需等待對相同資料行的寫入操作。
l 寫入操作無需等待對相同資料行的讀取操作，除非讀取操作使用了 SELECT ... FOR UPDATE 語句，此讀取語句需要對資料加鎖。
l 寫入操作只需等待併發地且針對相同資料行的其它寫入操作。
提示：讀取操作可能會等待對相同資料塊（data block）的寫入操作，這種情況只會在出現掛起的分散式事務（pending distributed transaction）時偶爾出現。
在執行下列語句時，事務需要獲得被修改的每一資料行的排它行級鎖（exclusive row lock）：INSERT，UPDATE，DELETE，及使用了FOR UPDATE 子句的 SELECT 語句。
在事務被提交或回滾前，此事務擁有在其內部被修改的所有資料行的排它鎖，其它事務不能對這些資料行進行修改操作。但是，如果事務由於例項故障而終止，在整個事務被恢復前，資料塊級的恢復將使資料塊內資料行上的鎖釋放。執行前面提到的 4 種 SQL 語句時，Oracle 能自動地對行級鎖進行管理。
當事務獲得了某些資料行上的行級鎖時，此事務同時獲得了資料行所屬表上的表級鎖（table lock）。表級鎖能夠防止系統中併發地執行有衝突的 DDL 操作，避免當前事務中的資料操作被併發地 DDL 操作影響。
行級鎖機制：
當一個事務開始時，必須申請一個TX鎖，這種鎖保護的資源是回滾段、回滾資料塊。因此申請也就意味著：使用者程式必須先申請到回滾段資源後才開始一個事務，才能執行DML操作。申請到回滾段後，使用者事務就可以修改資料了。具體順序如下：
1、首先獲得TM鎖，保護事務執行時，其他使用者不能修改表結構
2、事務修改某個資料塊中記錄時，該資料塊頭部的ITL表中申請一個空閒表項，在其中記錄事務項號，實際就是記錄這個事務要使用的回滾段的地址（應該叫包含）
3、事務修改資料塊中的某條記錄時，會設定記錄頭部的ITL索引指向上一步申請到的表項。然後修改記錄。修改前先在回滾段將記錄之前的狀態做一個拷貝，然後修改表中資料。
4、其他使用者併發修改這條記錄時，會根據記錄頭部ITL索引讀取ITL表項內容，確認是否事務提交。
5、若沒有提交，必須等待TX鎖釋放
從上面的機制來看，無論一個事務修改多少條記錄，都只需要一個TX鎖。所謂的“行級鎖”其實也就是資料塊頭、資料記錄頭的一些欄位，不會消耗額外的資源。 從另一方面也證明了，當使用者被阻塞時，不是被某條記錄阻塞，而是被TX鎖堵塞。也正因為這點，很多人也傾向把TX鎖稱為事務鎖。這裡可通過實驗來驗證所說 結論。
會話1：
|
SQL> select * from test; ID NAME ---------- -------- 1 A 2 B 3 C
SQL> savepoint a; Savepoint created.
SQL> update test set name='ssss' where id=2; 1 row updated.
|
會話2，更新同一行發生阻塞：
|
SQL> update test set name='ssdsdsds'where id=2;
|
會話1：
|
SQL> rollback to a; Rollback complete.
|
可以看到，雖然會話1已經撤銷了對記錄的修改，但是會話2仍然處於等待狀態這是因為會話2是被會話1的TX鎖阻塞的，而不是被會話1上的行級鎖 阻塞(rollback to savepoint不會結束事務) 。
|
會話3： SQL> select username,event,sid,blocking_session from v$session where SID IN (146,159); USERNAME EVENT SID BLOCKING_SESSION -------- ----------------------------------- ---------- ---------------- HR enq: TX - row lock contention 146 159 HR SQL*Net message from client 159 會話1： SQL> rollback; 會話2： SQL> update test set name='ssdsdsds'where id=2; 1 row updated. 會話3： SQL> select username,event,sid,blocking_session from v$session where username='HR'; USERNAME EVENT SID BLOCKING_SESSION -------- ----------------------------------- ---------- ---------------- HR SQL*Net message from client 159 |
事務結束，tx鎖釋放，會話2update執行成功。
行鎖，也稱為TX 鎖，是一個表中單個行上的鎖。一個事務在被INSERT、UPDATE、DELETE、MERGE、或SELECT ... FOR UPDATE 等語句所修改的每一行上獲取一個行鎖。行鎖一直存在直到事務提交或回滾。行鎖主要作為一種排隊的機制，以防止兩個事務修改相同的行。資料庫始終以獨佔模式鎖定修改的行，以便其它事務不能修改該行，直到持有鎖的事務提交或回滾。行鎖定提供了近乎最細粒度的鎖定，並因此提供了近乎最佳的併發性和吞吐量。
如果一個事務因為資料庫例項失效而終止，會先進行塊級恢復以使行可用，之後進行整個事務恢復。
2.5.1.2 表鎖（Table Locks，TM）
表級鎖（table-level lock）的作用是對併發的 DDL 操作進行訪問控制，例如防止在 DML 語句執行期間相關的表被移除。當使用者對錶執行 DDL 或 DML 操作時，將獲取一個此表的表級鎖。表級鎖不會影響其他併發的 DML 操作。對於分割槽表來說，表級鎖既可以針對整個表，也可以只針對某個分割槽。
當使用者執行以下 DML 語句對錶進行修改：INSERT，UPDATE，DELETE，及 SELECT ... FOR UPDATE，或執行 LOCK TABLE 語句時，事務將獲取一個表級鎖。這些 DML 語句獲取表級鎖的目的有兩個：首先保證自身對錶的訪問不受其它事務 DML 語句的干擾，其次阻止其它事務中和自身有衝突的 DDL 操作執行。任何型別的表級鎖都將阻止對此表的排它 DDL 鎖（exclusive DDL lock），從而阻止了必須具備排它 DDL 鎖才能執行的 DDL 操作。例如，當一個未提交的事務擁有某個表上的鎖時，此表就無法被修改定義或被移除。
表級鎖具有以下幾種模式：行共享（row share，RS），行排它（row exclusive，RX），共享（share，S），共享行排它（share row exclusive，SRX），及排它（exclusive，X）。各種模式的表級鎖具有的限制級別決定了其是否能與其他表級鎖共處於同一資料表上。
下表顯示了各種語句所獲得的表級鎖的模式，以及此模式下被允許或禁止的操作。
ORACLE裡鎖有以下幾種模式:
鎖的相容模式如下表所示：
表鎖，也稱為TM鎖，當一個表被INSERT、UPDATE、DELETE、MERGE、帶FOR UPDATE子句的SELECT等修改時，由相關事務獲取該鎖。DML操作需要表鎖來為事務保護DML對錶的訪問，並防止可能與事務衝突的DDL操作。
表鎖可能以下列模式之一持有：
這種鎖也被稱為子共享表鎖(SS，subshare table lock)，表示在表上持有鎖的事務在表中有被鎖定的行，並打算更新它們。行共享鎖是限制最少的表級鎖模式，提供在表上最高程度的併發性。
1、 實驗ROW SHARE模式允許同時訪問被鎖定的表，但是禁止使用者以排它方式鎖定整個表。ROW SHARE與SHARE UPDATE相同，只是為了相容早期的Oracle版本。對應lmode2，row-S (SS)。
版本：11.2.0.4
|
會話1： SYS@lhrdb> set sqlprompt "_user'@'_connect_identifier S1> " SYS@lhrdb S1> select userenv('sid') from dual;
USERENV('SID') -------------- 6
SYS@lhrdb S1> LOCK TABLE SCOTT.EMP IN ROW SHARE MODE;
Table(s) Locked. 會話2： SYS@lhrdb> set sqlprompt "_user'@'_connect_identifier S2> " SYS@lhrdb S2> select userenv('sid') from dual;
USERENV('SID') -------------- 114
SYS@lhrdb S2> LOCK TABLE SCOTT.EMP IN EXCLUSIVE MODE;
====>>>>> 產生了阻塞 查詢2個會話的鎖： SYS@lhrdb S1> SELECT D.SID, D.TYPE, D.ID1, D.ID2, D.LMODE, D.REQUEST, D.CTIME, D.BLOCK 2 FROM V$LOCK D 3 WHERE D.SID IN (114, 6) 4 ORDER BY D.SID, D.TYPE; SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 6 AE 100 0 4 0 231 0 6 TM 86893 0 2 0 169 1 114 AE 100 0 4 0 378 0 114 TM 86893 0 0 6 144 0 114 TO 79619 1 3 0 376 0 |
SELECT D.SID, D.TYPE, D.ID1, D.ID2, D.LMODE, D.REQUEST, D.CTIME, D.BLOCK
FROM V$LOCK D
WHERE D.SID IN (114, 6)
ORDER BY D.SID, D.TYPE;
由BLOCK列可以看到sid為6的會話阻塞了一個會話，這裡其實就是114，而114正在請求模式為6的鎖。將2個會話提交後繼續測試：
|
SYS@lhrdb S1> LOCK TABLE SCOTT.EMP IN SHARE UPDATE MODE;
Table(s) Locked.
SYS@lhrdb S1> SELECT D.SID, D.TYPE, D.ID1, D.ID2, D.LMODE, D.REQUEST, D.CTIME, D.BLOCK 2 FROM V$LOCK D 3 WHERE D.SID IN (114, 6) 4 AND D.TYPE = 'TM' 5 ORDER BY D.SID, D.TYPE;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 6 TM 86893 0 2 0 387 0
|
二、 行獨佔表鎖 Row Exclusive Table Lock (RX)
這種鎖也被稱為子獨佔表鎖（SX，subexclusive table lock），通常表示持有鎖的事務已更新了錶行或發出了SELECT...FOR UPDATE。一個SX鎖允許其它事務併發地查詢、插入、更新、刪除、或鎖定在同一個表中的其它行。因此，SX鎖允許多個事務對同一個表同時獲得SX和子共享表鎖。
ROW EXCLUSIE類似於ROW SHARE模式，但是不能應用在SHARE模式中。當update,insert,delete發生時，ROW EXCLUSIVE會自動獲得。對應lmode3，row-X (SX) 。
1、 實驗實驗內容：but it also prohibits locking in SHARE mode
|
會話1： SQL> set sqlprompt "_user'@'_connect_identifier S1> " SYS@oratest S1> select distinct sid from v$mystat;
SID ---------- 21 SYS@oratest S1> lock table scott.emp in share mode;
Table(s) Locked.
會話2： SQL> set sqlprompt "_user'@'_connect_identifier S2> " SYS@oratest S2> select distinct sid from v$mystat;
SID ---------- 142
SYS@oratest S2> lock table scott.emp in row exclusive mode;
====>>>>> 產生了阻塞
檢視鎖： SYS@oratest S1> set line 9999 SYS@oratest S1> select * from v$lock where sid in (21,142);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000774D8518 00000000774D8570 142 TO 68064 1 3 0 7021 0 00000000774D9870 00000000774D98C8 142 TO 76985 1 3 0 7365 0 00000000774D9DC8 00000000774D9E20 21 AE 100 0 4 0 162 0 00000000774DA068 00000000774DA0C0 142 AE 100 0 4 0 7379 0 00007F567ADC2700 00007F567ADC2760 142 TM 75335 0 0 3 36 0 00007F567ADC2700 00007F567ADC2760 21 TM 75335 0 4 0 58 1
6 rows selected.
SYS@oratest S1> select * from v$lock where sid in (21,142) AND TYPE IN ('TX','TM');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F567ADC7818 00007F567ADC7878 142 TM 75335 0 0 3 76 0 00007F567ADC7818 00007F567ADC7878 21 TM 75335 0 4 0 98 1
SYS@oratest S1> SELECT * FROM DBA_DML_LOCKS;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ------------------------------ ------------------------------ ------------- ------------- ------------ ---------------------------------------- 142 SCOTT EMP None Row-X (SX) 101 Not Blocking 21 SCOTT EMP Share None 123 Blocking
SYS@oratest S1>
|
這裡可以看到會話1的TM4阻塞了會話2的TM3。
提交2個會話後，接著實驗：ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.
|
SYS@oratest S1> update scott.emp set sal=sal where empno=7369;
1 row updated.
SYS@oratest S1> select * from v$lock where sid in (21,142) AND TYPE IN ('TX','TM');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F567ADE6AC8 00007F567ADE6B28 21 TM 75335 0 3 0 4 0 0000000076227AB0 0000000076227B28 21 TX 196620 1097 6 0 4 0
|
當會話1做了修改而沒有commit或者rollback時，這裡有兩個鎖，其中一個就是TM3的，一個是TX6的。
三、 共享表鎖 Share Table Lock (S)
由某個事務擁有的共享表鎖允許其它事務查詢（而不使用SELECT...FOR UPDATE)，但是更新操作只能在僅有單個事務持有共享表鎖時才允許。因為可能有多個事務同時持有共享表鎖，所以持有此鎖不足以確保一個事務可以修改該表。
SHARE允許同時查詢，但是禁止更新被鎖定的表。對應lmode4，share (S) 。
1、 實驗|
會話1： SQL> set sqlprompt "_user'@'_connect_identifier S1> " SYS@oratest S1> select distinct sid from v$mystat;
SID ---------- 21 SYS@oratest S1> lock table scott.emp in share mode;
Table(s) Locked.
會話2： SQL> set sqlprompt "_user'@'_connect_identifier S2> " SYS@oratest S2> select distinct sid from v$mystat;
SID ---------- 142
SYS@oratest S2> update scott.emp set sal=sal where empno=7369;
====>>>>> 產生了阻塞
檢視鎖： SYS@oratest S1> select * from v$lock where sid in (21,142) AND TYPE IN ('TX','TM');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F567ADE6AC8 00007F567ADE6B28 142 TM 75335 0 0 3 43 0 00007F567ADE6AC8 00007F567ADE6B28 21 TM 75335 0 4 0 62 1
SYS@oratest S1> SELECT * FROM DBA_DML_LOCKS;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- -------- ------ ------------- ------------- ------------ --------------- 142 SCOTT EMP None Row-X (SX) 113 Not Blocking 21 SCOTT EMP Share None 132 Blocking SYS@oratest S1>
|
這裡可以看到會話1的TM4阻塞了會話2的TM3。
四、 共享行獨佔表鎖 Share Row Exclusive Table Lock (SRX)
這種鎖也稱為共享子獨佔表鎖（SSX，share-subexclusive table lock），比共享表鎖的限制性更強。一次只能有一個事務可以獲取給定的表上的SSX鎖。由某個事務擁有的SSX鎖允許其它事務查詢該表（除SELECT...FOR UPDATE）但不能更新該表。
共享行級排它鎖有時也稱共享子排它鎖（Share Subexclusive Table Lock，SSX），它比共享鎖有更多限制。定義共享行級排它鎖的語法為：
Lock Table TableName In Share Row Exclusive Mode;
1、 實驗|
會話1： SQL> set sqlprompt "_user'@'_connect_identifier S1> " SYS@oratest S1> select distinct sid from v$mystat;
SID ---------- 21 SYS@oratest S1> lock table scott.emp in share row exclusive mode;
Table(s) Locked.
會話2： SQL> set sqlprompt "_user'@'_connect_identifier S2> " SYS@oratest S2> select distinct sid from v$mystat;
SID ---------- 142
SYS@oratest S2> lock table scott.emp in share mode;
====>>>>> 產生了阻塞
檢視鎖： SYS@oratest S1> select * from v$lock where sid in (21,142) AND TYPE IN ('TX','TM');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F567ADE7B00 00007F567ADE7B60 142 TM 75335 0 0 4 21 0 00007F567ADE7B00 00007F567ADE7B60 21 TM 75335 0 5 0 69 1 SYS@oratest S1> SELECT * FROM DBA_DML_LOCKS;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ------- ------ ------------- ------------- ------------ --------------- 142 SCOTT EMP None Share 44 Not Blocking 21 SCOTT EMP S/Row-X (SSX) None 92 Blocking |
這裡可以看到會話1的TM5阻塞了會話2的TM4。
五、 獨佔表鎖 Exclusive Table Lock (X)
這種鎖是最嚴格的鎖，禁止其它事務執行任何型別的DML語句，或在表上放置任何型別的鎖。
EXCLUSIVE EXCLUSIVE permits queries on the locked table but prohibits any other activity on it.
EXCLUSIVE模式允許查詢被鎖表上的資料，但是禁止任何其他任何活動（這裡我理解是禁止新增其他任何模式的鎖）。對應lomde6，exclusive (X) 。
1、 實驗|
會話1： SQL> set sqlprompt "_user'@'_connect_identifier S1> " SYS@oratest S1> select distinct sid from v$mystat;
SID ---------- 21 SYS@oratest S1> CREATE TABLE SCOTT.EMP_01 AS SELECT * FROM SCOTT.EMP;
Table created.
SYS@oratest S1> update scott.emp_01 set sal=sal where empno=7369;
1 row updated.
會話2： SQL> set sqlprompt "_user'@'_connect_identifier S2> " SYS@oratest S2> select distinct sid from v$mystat;
SID ---------- 142
SYS@oratest S2> DELETE FROM scott.emp_01 where empno=7369;
====>>>>> 產生了阻塞
檢視鎖： SYS@oratest S1> select * from v$lock where sid in (21,142) AND TYPE IN ('TX','TM');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000774D9EA8 00000000774D9F00 142 TX 393247 1337 0 6 28 0 00007F567ABBC0A0 00007F567ABBC100 142 TM 77624 0 3 0 28 0 00007F567ABBC0A0 00007F567ABBC100 21 TM 77624 0 3 0 36 0 0000000076255548 00000000762555C0 21 TX 393247 1337 6 0 36 1
SYS@oratest S1> SELECT * FROM DBA_DML_LOCKS;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- -------- -------- ------------- ------------- ------------ --------------- 142 SCOTT EMP_01 Row-X (SX) None 35 Not Blocking 21 SCOTT EMP_01 Row-X (SX) None 43 Not Blocking |
在這裡，從BLOCK欄位可以看到會話1的TM3並沒堵塞會話2的TM3，這裡真正發生堵塞的是會話1的TX6。
這裡還有一個鎖定物件的問題。上面兩個TM3的鎖針對的物件是object_id為77624的表，既然描述是類似行共享，自然是不會堵塞的。而兩個TX6的鎖針對的物件可以理解成表中的行，在這些行上新增EXCLUSIVE鎖（lmode6，exclusive (X) )自然是會堵塞其他的EXCLUSIVE鎖的。
解決這種型別的鎖堵塞當然就是在程式碼中儘早commit結束事務。很多地方都寫到儘早commit可以提高執行效率，這裡所指的是釋放鎖（特別是lmode6的EXCLUSIVE鎖）減少堵塞，以提高併發性。（不是以減少資料的量來提高效率的，事實上不管多大的資料量，一個commit的過程都是很"平"的。
2、 INSERT /*+APPEND*/ INTO加6級TM和TX獨佔鎖
|
會話1： SYS@lhrdb> set sqlprompt "_user'@'_connect_identifier S1> " SYS@lhrdb S1> SELECT DISTINCT SID FROM V$MYSTAT;
SID ---------- 27 SYS@lhrdb S1> CREATE TABLE T_APPEND_161107_LHR AS SELECT * FROM DUAL;
Table created.
SYS@lhrdb S1> INSERT /*+ APPEND */ INTO T_APPEND_161107_LHR SELECT * FROM DUAL;
3 rows created.
會話2： SYS@lhrdb> set sqlprompt "_user'@'_connect_identifier S2> " SYS@lhrdb S2> SELECT DISTINCT SID FROM V$MYSTAT;
SID ---------- 162
SYS@lhrdb S2> INSERT /*+ APPEND */ INTO T_APPEND_161107_LHR SELECT * FROM DUAL;
<<<<<<<<<-------- 產生了阻塞
|
會話3：
|
SYS@lhrdb> set sqlprompt "_user'@'_connect_identifier S3> " SYS@lhrdb S3> set line 9999 SYS@lhrdb S3> SELECT * FROM V$LOCK T WHERE T.SID IN (27,162) AND T.TYPE IN ('TX','TM') ORDER BY T.SID ;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000001109F5A40 00000001109F5AA0 27 TM 100957 0 6 0 2217 1 070001007C7EB2B0 070001007C7EB328 27 TX 589843 58249 6 0 2217 0 00000001109F5A40 00000001109F5AA0 162 TM 100957 0 0 6 2214 0
====>>>>> 過了很久 SYS@lhrdb S3> SELECT * FROM V$LOCK T WHERE T.SID IN (27,162) AND T.TYPE IN ('TX','TM') ORDER BY T.SID ;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000001109F6A78 00000001109F6AD8 27 TM 100957 0 6 0 2882 1 070001007C7EB2B0 070001007C7EB328 27 TX 589843 58249 6 0 2882 0 00000001109F6A78 00000001109F6AD8 162 TM 100957 0 0 6 2879 0
SYS@lhrdb S3> /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000001109F5A40 00000001109F5AA0 27 TM 100957 0 6 0 2885 1 070001007C7EB2B0 070001007C7EB328 27 TX 589843 58249 6 0 2885 0 00000001109F5A40 00000001109F5AA0 162 TM 100957 0 0 6 2882 0
|
其中，會話1的sid為27，分別在TX和TM級別，擁有LMODE為6的X鎖。BLOCK為1說明會話1阻塞了其它會話（0表示沒有阻塞，2表示RAC環境需要用GV$LOCK）。CTIME表示擁有此鎖的時間，單位為秒。會話2的sid為162，REQUEST為6表示正在請求模式為6的鎖。
當TYPE列為TM的時候，即對於TM鎖來說，ID1列表示被鎖定的物件的物件ID，ID2始終為0，如下：
|
SYS@lhrdb S3> COL OWNER FORMAT A5 SYS@lhrdb S3> COL OBJECT_NAME FORMAT A20 SYS@lhrdb S3> SELECT D.OWNER,D.OBJECT_NAME,D.OBJECT_ID FROM DBA_OBJECTS D WHERE D.OBJECT_ID = 100957; OWNER OBJECT_NAME OBJECT_ID ----- -------------------- ---------- SYS T_APPEND_161107_LHR 100957
|
當TYPE列為TX的時候，即對於TX鎖來說，ID1列表示事務使用的回滾段編號以及在事務表中對應的記錄編號，ID2表示該記錄編號被重用的次數（wrap），ID1列表示事務的資訊，如下：
|
SYS@lhrdb S3> SELECT A.TADDR FROM V$SESSION A WHERE SID = 27;
TADDR ---------------- 070001007C7EB2B0
SYS@lhrdb S3> SELECT A.XIDUSN, A.XIDSLOT, A.XIDSQN 2 FROM V$TRANSACTION A 3 WHERE A.ADDR = '070001007C7EB2B0';
XIDUSN XIDSLOT XIDSQN ---------- ---------- ---------- 9 19 58249
SYS@lhrdb S3> SELECT TRUNC(589843 / POWER(2, 16)) AS UNDO_SEG#, 2 BITAND(589843, TO_NUMBER('ffff', 'xxxx')) + 0 AS SLOT#, 3 58249 XIDSQN 4 FROM DUAL;
UNDO_SEG# SLOT# XIDSQN ---------- ---------- ---------- 9 19 58249
SYS@lhrdb S3> SELECT SID, 2 STATUS, 3 SQL_ID, 4 LAST_CALL_ET, 5 BLOCKING_INSTANCE, 6 BLOCKING_SESSION, 7 EVENT 8 FROM GV$SESSION 9 WHERE BLOCKING_SESSION IS NOT NULL;
SID STATUS SQL_ID LAST_CALL_ET BLOCKING_INSTANCE BLOCKING_SESSION EVENT ---------- -------- ------------- ------------ ----------------- ---------------- --------------------- 162 ACTIVE 2kvrfkkjukryr 4875 1 27 enq: TM - contention
SYS@lhrdb S3> select sql_text from v$sql where sql_id='2kvrfkkjukryr';
SQL_TEXT ---------------------------------------------------- INSERT /*+ APPEND */ INTO T_APPEND_161107_LHR SELECT * FROM DUAL
SYS@lhrdb S3> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (27, 162);
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ----- --------------------- ------------- ------------- ------------ --------------- 27 SYS T_APPEND_161107_LHR Exclusive None 647 Blocking 162 SYS T_APPEND_161107_LHR None Exclusive 468 Not Blocking |
從檢視DBA_DML_LOCKS可以非常直觀的看出鎖的情況，會話1即SID為27，擁有Exclusive的排它鎖，沒有請求其它鎖，而會話2即SID為162正在請求Exclusive的排它鎖。
SELECT * FROM V$EVENT_NAME WHERE NAME = 'enq: TM - contention';
從會話查詢鎖的資訊：
SELECT SID,
STATUS,
SQL_ID,
LAST_CALL_ET,
EVENT,
A.P1,
A.P2,
A.P3,
CHR(BITAND(P1, -16777216) / 16777215) ||
CHR(BITAND(P1, 16711680) / 65535) "LOCK",
BITAND(P1, 65535) "MODE",
(SELECT OBJECT_NAME FROM DBA_OBJECTS D WHERE D.OBJECT_ID = A.P2) OBJECT_NAME
FROM GV$SESSION A
WHERE A.EVENT = 'enq: TM - contention';
會話1提交，檢視會話2的情況：
|
SYS@lhrdb S1> commit;
Commit complete.
SYS@lhrdb S1> 會話2： SYS@lhrdb S2> INSERT /*+ APPEND */ INTO T_APPEND_161107_LHR SELECT * FROM DUAL;
3 rows created.
SYS@lhrdb S2> SYS@lhrdb S2> SYS@lhrdb S2> commit;
Commit complete.
SYS@lhrdb S2> SELECT * FROM V$LOCK T WHERE T.SID IN (27,162) AND T.TYPE IN ('TX','TM') ORDER BY T.SID ;
no rows selected
|
二.5.1.3 總結
執行不同的 DML 語句時，Oracle自動地對資料加鎖。
一、 查詢操作預設獲取的鎖執行查詢（query）的 SQL 語句不易與其他 SQL 語句衝突，因為查詢只需讀取資料。除了 SELECT 之外，INSERT，UPDATE，及 DELETE 語句中也可能包含隱式的查詢。因此，以下語句都屬於查詢操作：
SELECT
INSERT ... SELECT ... ;
UPDATE ... ;
DELETE ... ;
但是以下語句不屬於查詢操作：
SELECT ... FOR UPDATE OF ... ;
查詢操作具備以下特性：
l 查詢無需獲取資料鎖。因此當某事務查詢資料表時，其它事務可以併發地查詢、更新同一個表，包括此表中正在被查詢的資料行。沒有使用 FOR UPDATE 子句的 SELECT 語句無需獲取任何資料鎖，因此也不會阻塞任何操作，此類查詢在 Oracle 中被稱為非阻塞查詢（nonblocking query）。
l 執行查詢也不受資料鎖的限制。（在某些特殊情況下，查詢需要等待掛起的分散式事務所擁有的資料鎖）
二、 INSERT，UPDATE，DELETE，及 SELECT ... FOR UPDATE 語句預設獲取的鎖INSERT，UPDATE，DELETE，及 SELECT ... FOR UPDATE 語句預設獲取的鎖有以下特點：
l 包含 DML 語句的事務需要獲得被其修改的資料行上的排它行級鎖（exclusive row lock）。在擁有鎖的事務提交或回滾前，其它事務不能更新或刪除被加鎖的資料行。
l 事務無需獲取 DML 語句內的子查詢（subquery）或隱式查詢（implicit query）（例如 WHERE 子句內的查詢）所選擇的行上的行級鎖。DML 內的子查詢或隱式查詢獲得的資料相對查詢開始的時間點滿足一致性，這些查詢不會看到 DML 語句自身對資料的影響。
l 事務內的查詢能夠看到本事務內之前執行的 DML 語句對資料的修改，但無法看到本事務開始後執行的其它事務對資料的修改。
l 事務內的 DML 語句除了需要獲得必要的排它行級鎖（exclusive row lock）外，至少還需獲得包含被修改資料行的表上的行排它表級鎖（row exclusive table lock）。如果事務已經獲得了相關表上的共享表級鎖（share），共享行排它表級鎖（share row exclusive），或排它表級鎖（exclusive），那麼就無需獲取行排它表級鎖了。如果事務已經獲得了相關表上的行共享表級鎖（row share table lock），Oracle 將自動地將此鎖轉換為行排它表級鎖。
2.5.2 DDL鎖（DDL Locks）
當某個執行中的DDL操作正在操作或引用某模式物件時，資料字典(DDL)鎖保護該模式物件的定義。在DDL操作的過程中，只有被修改或引用的單個模式的物件被鎖定。資料庫絕不會鎖定整個資料字典。
Oracle資料庫將為任何要求鎖的DDL事務自動獲取DDL鎖。使用者不能顯式請求DDL鎖。例如，如果使用者建立一個儲存過程，則資料庫自動為過程定義中引用的所有模式物件獲取DDL鎖。DDL鎖防止在過程編譯完成之前，這些物件被更改或刪除。
資料字典鎖（data dictionary lock，DDL）的作用是在執行 DDL 操作時對被修改的方案物件或其引用物件的定義進行保護。管理員及開發者應該意識到 DDL 語句將會隱式地提交一個事務。例如，使用者建立一個儲存過程時，相當於執行一個只包含一條 SQL 語句的事務，Oracle 會自動獲取過程定義中所引用的所有方案物件的 DDL 鎖。DDL 鎖能夠防止編譯期間過程所引用的物件被其它事務修改或移除。
當 DDL 事務需要時 Oracle 將自動地為其獲取資料字典鎖。使用者不能顯示地獲取 DDL 鎖。只有在 DDL 操作中被修改或引用的物件才會被加鎖，整個資料字典不會被加鎖。
當使用者釋出DDL（Data Definition Language）語句時會對涉及的物件加DDL鎖。由於DDL語句會更改資料字典，所以該鎖也被稱為字典鎖。
DDL鎖能防止在用DML語句運算元據庫表時，對錶進行刪除，或對錶的結構進行更改。
對於DDL鎖，要注意的是：
l DDL鎖只鎖定DDL操作所涉及的物件，而不會鎖定資料字典中的所有物件。
l DDL鎖由Oracle自動加鎖和釋放。不能顯式地給物件加DDL鎖，即沒有加DDL鎖的語句。
l 在過程中引用的物件，在過程編譯結束之前不能被改變或刪除，即不能被加排它DDL鎖。
DDL 鎖可以分為三類：排它 Ddl 鎖（Exclusive DDL Lock），共享 Ddl 鎖（Share DDL Lock），及可中斷的解析鎖（Breakable Parse Lock）。
2.5.2.1 排它DDL鎖（eXclusive DDL Locks，XDDL）--獨佔DDL鎖
大多數DDL 都帶有一個排它DDL 鎖。如果發出如下一條語句：
Alter table t add new_column date;
在執行這條語句時，表T 不能被別人修改。在此期間，可以使用SELECT 查詢這個表，但是大多數其他操作都不允許執行，包括所有DDL 語句。
獨佔DDL鎖可防止其它會話獲取DDL或DML鎖。除了那些在"共享DDL鎖"中所述操作之外，絕大多數DDL操作需要對資源獲取獨佔鎖，以防止和其它可能會修改或引用相同模式物件的DDL之間的破壞性干擾。例如，當ALTER TABLE正在將一列新增到表時，不允許DROP TABLE刪除表，反之亦然。
獨佔DDL鎖在整個DDL語句執行期間一直持續，並自動提交。在獨佔DDL鎖獲取過程中，如果另一個操作在該模式物件上持有另一個DDL鎖，則這個鎖獲取將一直等待，直到前一個DDL鎖被釋放，才能繼續。
2.5.2.2 共享DDL鎖（Share DDL Locks，SDDL）
create index t_idx on t(x) ONLINE;
ONLINE 關鍵字會改變具體建立索引的方法。Oracle 並不是加一個排它DDL 鎖 防止資料修改，而只會試圖得到表上的一個低階 （mode 2 ）TM 鎖。這會有效地防止其他DDL 發生，同時還允許DML 正常進行。Oracle 執行這一壯舉”的做法是，為DDL 語句執行期 間對錶所做的修改維護一個記錄，執行CREATE 時再把這些修改應用至新的索引。這樣能大大增加資料的可用性。
另外一類DDL 會獲得共享DDL 鎖。在建立儲存的編譯物件（如過程和檢視）時，會對依賴的物件加這種共享DDL 鎖。例如，如果 執行以下語句：
Create view MyView as select * from emp, dept where emp.deptno = dept.deptno;
表EMP 和DEPT 上都會加共享DDL 鎖，而CREATE VIEW 命令仍在處理。可以修改這些表的內容，但是不能修改它們的結構。
A share DDL lock for a resource prevents destructive interference with conflicting DDL operations, but allows data concurrency for similar DDL operations.
在資源上的共享DDL鎖可防止與衝突的DDL操作發生破壞性干擾，但允許類似的DDL操作的資料併發。
例如，當CREATE PROCEDURE語句執行時，所在事務將為所有被引用的表獲取共享DDL鎖。其它事務可以同時建立引用相同表的過程，並在相同的表上同時獲得共享DDL鎖，但沒有任何事務能在任何被引用表上獲取獨佔DDL鎖。
共享DDL鎖在整個DDL語句執行期間持續存在，並自動提交。因此，持有一個共享DDL鎖的事務，可保證在事務過程中，被引用模式物件的定義保持不變。
某些 DDL 操作需要獲取相關資源上的共享 DDL 鎖（share DDL lock）以防止與之衝突的 DDL 操作造成破壞性的干擾，但與之類似的 DDL 操作可以併發地訪問資料，不受共享 DDL 鎖的限制。例如，執行 CREATE PROCEDURE 語句時，事務將獲取所有引用物件上的共享 DDL 鎖。此時，其它事務可以併發地獲取相同表上的共享 DDL 鎖並建立引用了相同表的過程。但任何事務都無法獲取被引用表上的排它 DDL 鎖（exclusive DDL lock），即任何事務都無法對錶進行修改或移除操作。因此獲得了共享 DDL 鎖的事務能夠保證在其執行期間，所有引用物件的定義不會被修改。
執行以下 DDL 語句時，需要獲取引用物件上的共享 DDL 鎖：AUDIT，NOAUDIT，COMMENT，CREATE [OR REPLACE] VIEW/ PROCEDURE/PACKAGE/PACKAGE BODY/FUNCTION/ TRIGGER，CREATE SYNONYM，及 CREATE TABLE（沒有使用 CLUSTER 引數時）。
2.5.2.3 分析鎖（Breakable Parse Locks，可中斷解析鎖，BPL）
SQL語句或PL/SQL程式單元，為每個被其引用的模式物件持有一個解析鎖。獲取解析鎖的目的是，如果被引用的物件被更改或刪除，可以使相關聯的共享SQL區無效。解析鎖被稱為可中斷的解析鎖，因為它並不禁止任何DDL操作，並可以被打破以允許衝突的DDL操作。
解析鎖是在執行SQL語句的分析階段，在共享池中獲取的。只要該語句的共享SQL區仍保留在共享池中，該鎖就一直被持有。
位於共享池（shared pool）內的 SQL 語句（或 PL/SQL 程式結構）擁有其引用的所有方案物件上的解析鎖（parse lock）。解析鎖的作用是，當共享 SQL 區（shared SQL area）所引用的物件被修改或移除後，此共享 SQL 區能夠被置為無效。解析鎖不會禁止任何 DDL 操作，當出現與解析鎖衝突的 DDL 操作時，解析鎖將被解除，因此也稱之為可解除的解析鎖。
解析鎖是在 SQL 語句執行的解析階段（parse phase）獲得的，在共享 SQL 區被清除出共享池（shared pool）前一直保持。
你的會話解析一條語句時，對於該語句引用的每一個物件都會加一個解析鎖。加這些鎖的目的是：如果以某種方式刪除或修改了一個被引用的物件，可以將共享池中已解析的快取語句置為無效（重新整理輸出）。
一、 檢視分析鎖|
CREATE OR REPLACE PROCEDURE P_BPL_LHR AS BEGIN NULL; END;
|
要看到一個實際的可中斷解析鎖，下面先建立並執行儲存過程P_BPL_LHR：
|
SYS@lhrdb> CREATE OR REPLACE PROCEDURE P_BPL_LHR AS 2 BEGIN 3 NULL; 4 END; 5 /
Procedure created.
SYS@lhrdb> exec P_BPL_LHR;
PL/SQL procedure successfully completed.
SYS@lhrdb> SELECT DISTINCT SID FROM V$MYSTAT;
SID ---------- 194
|
過程P_BPL_LHR現在會出現在DBA_DDL_LOCKS 檢視中。我們有這個過程的一個解析鎖：
SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID = 194;
然後重新編譯這個過程，並再次查詢檢視：
|
SYS@lhrdb> ALTER PROCEDURE P_BPL_LHR COMPILE;
Procedure altered.
|
可以看到，現在這個檢視中沒有P_BPL_LHR了。我們的解析鎖被中斷了。這個檢視對 發人員很有用，發現測試或開發系統中某段程式碼無法編譯時，將會掛起並最終超時。這說明，有人正在使用這段程式碼 （實際上在執行這段程式碼），你可以使用這個檢視 檢視這個人是誰。對於GRANTS 和物件的其他型別的DDL 也是一樣。例如，無法對正在執行的過程授予EXECUTE 許可權。可以使用同樣的方法 發現潛在的阻塞者和等待者。
2.5.2.4 DDL 鎖的持續時間
DDL 鎖的持續時間取決於其型別。共享 DDL 鎖（share DDL lock）及排它 DDL 鎖（exclusive DDL lock）在 DDL 語句執行期間一直存在，在 DDL 語句自動提交後釋放。而解析鎖一直存在，直至相關的共享 SQL 區從共享池中被清除。
2.5.2.5 DDL 鎖與簇
對簇（cluster）執行的 DDL 操作需要獲取簇及簇內所有表及物化檢視上的排它 DDL 鎖（exclusive DDL lock）。對簇內表及物化檢視的 DDL 操作需要獲取簇上的共享 DDL 鎖（share DDL lock），以及表或物化檢視上的共享 DDL 鎖或排它 DDL 鎖。簇上的共享 DDL 鎖能夠防止操作期間其他 DDL 操作將簇移除。
2.5.3 系統鎖（System Locks）
Oracle資料庫使用各種型別的系統鎖，來保護資料庫內部和記憶體結構。由於使用者不能控制其何時發生或持續多久，這些機制對於使用者幾乎是不可訪問的。閂鎖、互斥體、和內部鎖是完全自動的。
2.5.3.1 閂鎖（Latches）
閂鎖（latche）是一種簡單的底層序列化機制，用於保護 SGA 內的共享資料結構。例如，用於記錄當前正在訪問資料庫的使用者的列表，或用於記錄位於資料庫快取（buffer cache）內的資料塊的資料結構，都可通過閂鎖進行保護。當服務程式（background process）或後臺程式（server process）需要操作或查詢此類資料結構時，就需要獲取一個閂鎖，但其加鎖時間極短。閂鎖的實現與作業系統有關，例如程式是否需要等待栓鎖以及等待多長時間等。
閂鎖是簡單、低階別的序列化機制，用於協調對共享資料結構、物件、和檔案的多使用者訪問。閂鎖防止共享記憶體資源被多個程式訪問時遭到破壞。具體而言，閂鎖在以下情況下保護資料結構：
l 被多個會話同時修改
l 正在被一個會話讀取時，又被另一個會話修改
l 正在被訪問時，其記憶體被釋放（換出）
通常，一個單一的閂鎖保護SGA中的多個物件。例如，後臺程式（如DBWn和LGWR）從共享池分配記憶體來建立資料結構。為分配此記憶體，這些程式使用共享池閂鎖來序列化對記憶體的訪問，以防止兩個程式同時嘗試檢查或修改共享池。記憶體分配後，其它程式可能需要訪問共享池區域，如用於解析所需的庫快取記憶體。在這種情況下，程式只在庫快取獲取閂鎖，而不是在整個共享池。
與行鎖之類的入隊閂鎖不同，閂鎖不允許會話排隊。當閂鎖可用時，請求閂鎖的第一個會話將獲得它的獨佔訪問許可權。閂鎖旋轉（Latch spinning）發生在當一個程式不斷地迴圈來請求一個閂鎖時，而閂鎖睡眠（latch sleeping）發生在重新發起閂鎖請求之前，釋放CPU時。
通常，一個Oracle程式在操作或檢視一種資料結構時，只需在一個極短的時間內獲得閂鎖。例如，僅僅為某一名員工處理工資更新，資料庫就可能需要獲取並釋放成千上萬個閂鎖。閂鎖的實現依賴於作業系統，特別是在一個程式是否會在閂鎖上等待以及會在閂鎖等待多長時間方面。
閂鎖的增加意味著併發的降低。例如，過度硬解析操作會產生庫快取閂鎖爭用。V$LATCH檢視包含每個閂鎖的詳細使用情況的統計資訊，包括每個閂鎖被請求和被等待的次數。
2.5.3.2 互斥物件（Mutexes）
互斥物件（mutual exclusion object，mutex），也叫互斥體，它是一種底層機制，用於防止在記憶體中的物件在被多個併發程式訪問時，被換出記憶體或遭到破壞。互斥物件類似於閂鎖，但閂鎖通常保護一組物件，而互斥物件通常保護單個物件。
互斥物件提供以下幾個優點：
1、 互斥體可以減少發生爭用的可能性。
由於閂鎖保護多個物件，當多個程式試圖同時訪問這些物件的任何一個時，它可能成為一個瓶頸。而互斥體僅僅序列化對單個物件的訪問，而不是一組物件，因此互斥體提高了可用性。
2、 互斥體比閂鎖消耗更少的記憶體。
3、 在共享模式下，互斥體允許被多個會話併發引用。
2.5.3.3 內部鎖（Internal Locks）
內部鎖是比閂鎖和互斥體更高階、更復雜的機制，並用於各種目的。資料庫使用以下型別的內部鎖：
1、 字典快取鎖（Dictionary cache locks）
這些鎖的持續時間很短，當字典快取中的條目正在被修改或使用時被持有。它們保證正在被解析的語句不會看到不一致的物件定義。字典快取鎖可以是共享的或獨佔的。共享鎖在解析完成後被釋放，而獨佔鎖在DDL操作完成時釋放。
當使用者更新或使用時資料字典快取內的條目（entry）時，需要獲取條目上的資料字典快取鎖（dictionary cache lock），此類鎖的持續時間極短。此類鎖的作用是確保正在被解析的語句不會看到不一致的物件定義。資料字典快取鎖可以為共享或排它的。當語句解析結束時共享鎖將被釋放，而當 DDL 操作結束時排它鎖將被釋放。
2、 檔案和日誌管理鎖（File and log management locks）
這些鎖保護各種檔案。例如，一種內部鎖保護控制檔案，以便一次只有一個程式可以對其進行更改。而另一種鎖用於協調聯機重做日誌檔案的使用和歸檔。資料檔案被鎖定，確保資料庫被多個例項以共享模式裝載，或以獨佔模式被單個例項裝載。因為檔案和日誌鎖表示檔案的狀態，這些鎖必要時會被持有較長一段時間。
此類內部鎖（internal lock）用於保護各種檔案。例如，保護控制檔案（control file）的鎖，確保同一時間只有一個程式能夠對其進行修改。還有協調重做日誌檔案（redo log file）使用與歸檔的鎖。以及資料檔案（datafile）鎖，實現多例項在共享模式下掛載資料庫，或一個例項在排它模式下掛載資料庫。由於檔案及重做日誌鎖反映的是 物理檔案的狀態，因此此類鎖的持續時間較長。
3、 表空間和撤銷段鎖（Tablespace and undo segment locks）
這些鎖保護的表空間和撤銷段。例如，訪問資料庫的所有例項對一個表空間是否處於聯機或離線必須保持一致。撤銷段被鎖定，以便只能有一個資料庫例項可以寫入該段。
此類鎖用於保護表空間及回滾段（rollback segment）。例如，一個表空間處於聯機（online）還是離線（offline）狀態對訪問同一資料庫的所有例項應該是一致的。回滾段上的鎖保證 同一時間只有一個例項能夠對其執行寫操作。
2.6 死鎖(Deadlock)
有關死鎖的內容之前釋出過一次，具體內容參考：http://blog.itpub.net/26736162/viewspace-2127247/，本篇文章不再講解。
2.7 資料字典
常用的資料字典檢視有DBA_DML_LOCKS、DBA_DDL_LOCKS、V$LOCK、DBA_LOCK、V$LOCKED_OBJECT。
---查詢的都是當前例項的鎖
select * from dba_dml_locks;
select * from dba_ddl_locks d where d.owner not in('SYS','WMSYS','MDSYS');
select * from DBA_LOCK V where V.session_id=23;
select * from V$LOCK V where V.SID=23;
select * from V$LOCK_TYPE;
select * from V$LOCKED_OBJECT;
2.7.1 V$LOCK和dba_lock、dba_locks
本檢視列出Oracle 伺服器當前擁有的鎖以及未完成的鎖或栓鎖請求。
2.7.1.1 三者關係
v$lock和dba_locks和 dba_lock 內容一樣，dba_locks是dba_lock的同義詞。可以用動態效能檢視的定義來檢視它們的關係V$FIXED_VIEW_DEFINITION。
SELECT * FROM Dba_Objects d WHERE d.object_name LIKE '%DBA_LOCK%' ;
SELECT * FROM Dba_Synonyms d WHERE d.synonym_name LIKE '%DBA_LOCK%' ;
SELECT * FROM V$FIXED_VIEW_DEFINITION d WHERE d.VIEW_NAME LIKE '%V$LOCK%' ;
2.7.2 V$LOCKED_OBJECT
注意：V$LOCKED_OBJECT記錄的是DML鎖資訊，DDL鎖的資訊不在裡面。
這個檢視列出系統上的每個事務處理所獲得的所有鎖。記錄了當前已經被鎖定的物件的資訊
XIDUSN表示當前事務使用的回滾段的編號
XIDSLOT說明該事務在回滾段頭部的事務表中對應的記錄編號
XIDSQN說明序列號
OBJECT_ID說明當前被鎖定的物件的ID號，可以根據該ID號到dba_objects裡查詢被鎖定的物件名稱
LOCKED_MODE說明鎖定模式的數字編碼
V$LOCKED_OBJECT中的列說明：
示例：1.以DBA角色檢視當前資料庫裡鎖的情況可以用如下SQL語句：
SELECT v.object_id,
d.OBJECT_NAME,
d.OBJECT_TYPE,
locked_mode,
v2.username,
v2.sid,
v2.serial#,
v2.logon_time
FROM v$locked_object v,
dba_objects d,
v$session v2
WHERE v.OBJECT_ID = d.OBJECT_ID
AND v.SESSION_ID = v2.SID
ORDER BY v2.logon_time;
v$locked_object檢視列出當前系統中哪些物件正被鎖定.
v$lock檢視列出當前系統持有的或正在申請的所有鎖的情況.
2.7.3 DBA_DDL_LOCKS
DBA_DDL_LOCKS lists all DDL locks held in the database and all outstanding requests for a DDL lock.
查詢所有DDL鎖的資訊：
SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID = 115;
如果提示沒有這個檢視，可以在sys使用者下執行$ORACLE_HOME/rdbms/admin/catblock.sql指令碼進行建立（這個指令碼還包含其他一些非常有意義的鎖相關檢視）
sys@ora10g> conn / as sysdba
Connected.
sys@ora10g> @?/rdbms/admin/catblock.sql
這裡省略建立過程
列印一下catblock.sql指令碼的內容，這個建立指令碼其實可以當做一個參考文件來用，尤其是其中關於鎖型別的描述。
2.7.4 DBA_DML_LOCKS
DBA_DML_LOCKS lists all DML locks held in the database and all outstanding requests for a DML lock.
|
SQL> CREATE TABLE TB_DML_LOCK_LHR (ID NUMBER);
Table created.
SQL> INSERT INTO TB_DML_LOCK_LHR VALUES(1);
1 row created.
SQL> set line 9999 SQL> SELECT * FROM DBA_DML_LOCKS;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ------- ------------------ ------------- ------------- ------------ ----------------- 151 SYS TB_DML_LOCK_LHR Row-X (SX) None 10 Not Blocking
SQL>
|
2.7.5 一些欄位的說明
|
會話1： SYS@oratest S1> select distinct sid from v$mystat;
SID ---------- 22
SYS@oratest S1> CREATE TABLE SCOTT.EMP_LHR AS SELECT * FROM SCOTT.EMP;
Table created.
SYS@oratest S1> delete from scott.EMP_LHR where empno=7369;
1 row deleted.
SYS@oratest S1>
會話2： SYS@oratest S2> select distinct sid from v$mystat;
SID ---------- 143
SYS@oratest S2> delete from scott.EMP_LHR where empno=7369;
====>>>>> 產生了阻塞
會話3查詢鎖： SQL> set line 9999 SQL> SELECT A.TADDR, 2 A.LOCKWAIT, 3 A.ROW_WAIT_OBJ#, 4 A.ROW_WAIT_FILE#, 5 A.ROW_WAIT_BLOCK#, 6 A.ROW_WAIT_ROW#, 7 A.EVENT, 8 A.P1, 9 A.P2, 10 A.SID, 11 A.BLOCKING_SESSION 12 FROM V$SESSION A 13 WHERE A.SID IN (22, 143); TADDR LOCKWAIT ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# EVENT P1 P2 SID BLOCKING_SESSION ---------------- ---------------- ------------- -------------- --------------- ------------- ------------------------------ ---------- ---------- ---------- ---------------- 000000007622B710 -1 0 0 0 SQL*Net message from client 1650815232 1 22 000000007622AD00 00000000774DA0C0 77669 8 2799 0 enq: TX - row lock contention 1415053318 524299 143 22
|
V$SESSION檢視的TADDR列表示事務處理狀態物件的地址，對應於V$TRANSACTION.ADDR列；V$SESSION檢視的LOCKWAIT列表示等待鎖的地址，對應於V$LOCK的KADDR列；若當前會話沒有被阻塞則為空。V$SESSION檢視的SADDR列對應於V$TRANSACTION的SES_ADDR列。可以通過ROW_WAIT_OBJ#、ROW_WAIT_FILE#、ROW_WAIT_BLOCK#、ROW_WAIT_ROW#這幾個欄位查詢現在正在被鎖的表的相關資訊（ROWID），例如，表名、檔名及行號。P1和P2根據等待事件的不同所代表的含義不同，可以從V$EVENT_NAME檢視獲知每個引數的含義。
|
SQL> SELECT D.PARAMETER1,D.PARAMETER2,D.PARAMETER3 FROM V$EVENT_NAME D WHERE D.NAME='enq: TX - row lock contention';
PARAMETER1 PARAMETER2 PARAMETER3 ------------ --------------- ---------- name|mode usn<<16 | slot sequence
SQL> SELECT CHR(BITAND(P1, -16777216) / 16777215) || 2 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 3 BITAND(P1, 65535) "MODE", 4 TRUNC(P2 / POWER(2, 16)) AS XIDUSN, 5 BITAND(P2, TO_NUMBER('FFFF', 'XXXX')) + 0 AS XIDSLOT, 6 P3 XIDSQN 7 FROM V$SESSION A 8 WHERE A.SID IN (143);
LOCK MODE XIDUSN XIDSLOT XIDSQN ---- ---------- ---------- ---------- ---------- TX 6 4 30 894
<<<<<---從P1引數獲知請求的鎖的型別和模式；從P2引數可以獲知槽位號
SQL> SELECT ADDR,XIDUSN,XIDSLOT,XIDSQN FROM v$transaction a WHERE a.ADDR IN ('000000007622B710');
ADDR XIDUSN XIDSLOT XIDSQN ---------------- ---------- ---------- ---------- 000000007622B710 4 30 894
SQL> SELECT ADDR,XIDUSN,XIDSLOT,XIDSQN FROM v$transaction a WHERE a.SES_ADDR ='0000000077E6F600';
ADDR XIDUSN XIDSLOT XIDSQN ---------------- ---------- ---------- ---------- 000000007622B710 4 30 894
SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (22, 143) AND A.TYPE IN ('TX','TM') AND A.KADDR='00000000774DA0C0' ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000774DA068 00000000774DA0C0 143 TX 262174 894 0 6 658 0
SQL> SELECT DBMS_ROWID.ROWID_CREATE(1, 77766, 4, 131, 0) FROM DUAL;
DBMS_ROWID.ROWID_C ------------------ AAAS/GAAEAAAACDAAA
SQL> SELECT * FROM SCOTT.EMP A WHERE A.ROWID='AAAS/GAAEAAAACDAAA';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SQL>
|
可以看到被鎖的行的地址。
|
SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (22, 143) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007FF44BF72D18 00007FF44BF72D78 22 TM 77766 0 3 0 793 0 000000007622B710 000000007622B788 22 TX 262174 894 6 0 793 1 00007FF44BF72D18 00007FF44BF72D78 143 TM 77766 0 3 0 787 0 00000000774DA068 00000000774DA0C0 143 TX 262174 894 0 6 787 0 6 rows selected.
SQL> SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (22, 143) ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- -------- -------- ------------- ------------- ------------ --------------- 22 SCOTT EMP_LHR Row-X (SX) None 1146 Not Blocking 143 SCOTT EMP_LHR Row-X (SX) None 1140 Not Blocking
SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE 2 FROM DBA_OBJECTS D 3 WHERE D.OBJECT_ID IN (77766);
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE -------- ------------ ---------- ------------------- SCOTT EMP_LHR 77766 TABLE
SQL> SQL> SELECT a.XIDUSN, 2 a.XIDSLOT, 3 a.XIDSQN FROM v$transaction a WHERE a.XIDSQN =894;
XIDUSN XIDSLOT XIDSQN ---------- ---------- ---------- 4 30 894 SQL> SELECT 4*POWER(2,16)+30 FROM DUAL;
4*POWER(2,16)+30 ---------------- 262174
SQL> SQL> SELECT TRUNC(ID1 / POWER(2, 16)) AS XIDUSN, 2 BITAND(ID1, TO_NUMBER('FFFF', 'XXXX')) + 0 AS XIDSLOT, 3 894 XIDSQN 4 FROM V$LOCK A 5 WHERE A.SID IN (22, 143) 6 AND A.TYPE IN ('TX', 'TM') 7 AND A.ADDR = '000000007622B710' 8 ORDER BY A.SID, A.TYPE;
XIDUSN XIDSLOT XIDSQN ---------- ---------- ---------- 4 30 894
|
在V$LOCK中，當TYPE列的值為TM時，ID1的值為DBA_OBJECTS.OBJECT_ID；當為TX鎖時，ID1對應檢視V$TRANSACTION中的XIDUSN欄位（Undo segment number：事務對應的撤銷段序列號）和XIDSLOT欄位（Slot number：事務對應的槽位號）。其中ID1的高16位為XIDUSN，低16位為XIDSLOT。計算公式為：SELECT TRUNC(ID1/POWER(2,16)) AS XIDUSN,BITAND(ID1,TO_NUMBER('FFFF','XXXX')) + 0 AS XIDSLOT , ID2 XIDSQN FROM DUAL;
在V$LOCK中，當TYPE列的值為TM鎖時，ID2的值為0；當為TX鎖時，ID2對應檢視V$TRANSACTION中的XIDSQN欄位（Sequence number：事務對應的序列號）。
從V$SESSION檢視可以得到所有內容：
SELECT A.TADDR,
A.LOCKWAIT,
A.ROW_WAIT_OBJ#,
A.ROW_WAIT_FILE#,
A.ROW_WAIT_BLOCK#,
A.ROW_WAIT_ROW#,
(SELECT D.OWNER || '|' || D.OBJECT_NAME || '|' || D.OBJECT_TYPE
FROM DBA_OBJECTS D
WHERE D.OBJECT_ID = A.ROW_WAIT_OBJ#) OBJECT_NAME,
A.EVENT,
A.P1,
A.P2,
A.P3,
CHR(BITAND(P1, -16777216) / 16777215) ||
CHR(BITAND(P1, 16711680) / 65535) "LOCK",
BITAND(P1, 65535) "MODE",
TRUNC(P2 / POWER(2, 16)) AS XIDUSN,
BITAND(P2, TO_NUMBER('FFFF', 'XXXX')) + 0 AS XIDSLOT,
P3 XIDSQN,
A.SID,
A.BLOCKING_SESSION,
A.SADDR,
DBMS_ROWID.ROWID_CREATE(1, 77669, 8, 2799, 0) REQUEST_ROWID,
(SELECT B.SQL_TEXT
FROM V$SQL B
WHERE B.SQL_ID = NVL(A.SQL_ID, A.PREV_SQL_ID)) SQL_TEXT
FROM V$SESSION A
WHERE A.SID IN (143);
2.7.5.1 關聯關係圖
2.8 引數
2.8.1 DML_LOCKS引數
可以獲得的TX鎖定的總個數由初始化引數transactions決定，而可以獲得的TM鎖定的個數則由初始化引數dml_locks決定
select name,value from v$parameter where name in('transactions','dml_locks');
|
SYS@racdb1> col name format a15 SYS@racdb1> col value format a5 SYS@racdb1> select name,value from v$parameter where name in('transactions','dml_locks');
NAME VALUE --------------- ----- dml_locks 1088 transactions 272
SYS@racdb1> select 272*4 from dual;
272*4 ---------- 1088
|
DML_LOCKS引數屬於推導引數，DML_LOCKS=4 * TRANSACTIONS。
select resource_name as "R_N",current_utilization as "C_U",max_utilization as "M_U",initial_allocation as "I_U"
from v$resource_limit
where resource_name in('transactions','dml_locks');
|
SYS@racdb1> select resource_name as "R_N",current_utilization as "C_U",max_utilization as "M_U",initial_allocation as "I_U" 2 from v$resource_limit 3 where resource_name in('transactions','dml_locks');
R_N C_U M_U I_U ------------------------------ ---------- ---------- -------------------- dml_locks 0 28 1088 transactions 0 6 272
|
系統中允許的TM 鎖總數可以由你配置（有關細節請見Oracle Database Reference 手冊中的DML_LOCKS 引數定義）。實際上，這個數可能設定為0。但這並不是說你的資料庫變成了一個只讀資料庫（沒有鎖），而是說不允許DDL。在非常專業的應用（如RAC 實現）中，這一點就很有用，可以減少例項內可能發生的協調次數。通過使用ALTER TABLE TABLENAME DISABLE TABLE LOCK 命令，還可以逐物件地禁用TM 鎖。這是一種快捷方法，可以使意外刪除表的難度更大”，因為在刪除表之前，你必須重新啟用表鎖。還能用它檢測由於外來鍵未加索引而導致的全表鎖（前面已經討論過）。
|
Property |
Description |
|
Parameter type |
Integer |
|
Default value |
Derived: 4 * TRANSACTIONS |
|
Modifiable |
No |
|
Range of values |
20 to unlimited; a setting of 0 disables enqueues |
|
Basic |
No |
|
Oracle RAC |
You must set this parameter for every instance, and all instances must have positive values or all must be 0. |
A DML lock is a lock obtained on a table that is undergoing a DML operation (insert, update, delete). DML_LOCKS specifies the maximum number of DML locks—one for each table modified in a transaction. The value should equal the grand total of locks on tables currently referenced by all users. For example, if three users are modifying data in one table, then three entries would be required. If three users are modifying data in two tables, then six entries would be required.
The default value assumes an average of four tables referenced for each transaction. For some systems, this value may not be enough.
Enqueues are shared memory structures that serialize access to database resources. If you set the value of DML_LOCKS to 0, enqueues are disabled and performance is slightly increased. However, you should be aware of the following restrictions when you set you DML_LOCKS to 0:
l You cannot use DROP TABLE, CREATE INDEX statements
l You cannot use explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE
l Enterprise Manager cannot run on any instances for which DML_LOCKS is set to 0
Oracle holds more locks during parallel DML than during serial execution. Therefore, if your database supports a lot of parallel DML, you may need to increase the value of this parameter.
2.8.2 DDL_LOCK_TIMEOUT
11g以前，DDL 語句是不會等待DML語句的，當DDL語句訪問的物件正在執行的DML語句，會立即報錯ORA-00054: 資源正忙, 但指定以 NOWAIT 方式獲取資源, 或者超時失效。而在11g以後，DDL_LOCK_TIMEOUT引數可以修改這一狀態，當DDL_LOCK_TIMEOUT=0時，DDL 不等待DML，當DDL_LOCK_TIMEOUT 為N（秒）時，DDL等待DML N 秒，該值預設為0。
|
Property |
Description |
|
Parameter type |
Integer |
|
Default value |
0 |
|
Modifiable |
ALTER SESSION |
|
Range of values |
0 to 1,000,000 (in seconds) |
|
Basic |
No |
DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.
If a lock is not acquired before the timeout period expires, then an error is returned.
會話1：
|
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set sqlprompt "_user'@'_connect_identifier S1> " SYS@oratest S1> set timing on SYS@oratest S1> update scott.emp set ename='' where empno=7499;
1 row updated.
Elapsed: 00:00:00.00 SYS@oratest S1>
|
會話2：
|
SQL> set sqlprompt "_user'@'_connect_identifier S2> " SYS@oratest S2> set timing on SYS@oratest S2> drop table scott.emp; drop table scott.emp * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Elapsed: 00:00:00.74 SYS@oratest S2> show parameter ddl_lock_timeout
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 0 SYS@oratest S2> alter session set ddl_lock_timeout=5;
Session altered.
Elapsed: 00:00:00.00 SYS@oratest S2> drop table scott.emp; drop table scott.emp * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Elapsed: 00:00:05.01 SYS@oratest S2> alter session set ddl_lock_timeout=10;
Session altered.
Elapsed: 00:00:00.00 SYS@oratest S2> drop table scott.emp; drop table scott.emp * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Elapsed: 00:00:10.03 SYS@oratest S2>
|
綜上，設定ddl_lock_timeout為N（秒）後，DDL執行後將等待N秒鐘後才丟擲報錯資訊。在ddl_lock_timeout為預設值 0 時，DDL語句提交之後馬上報錯。
2.9 for update、for update of、for update nowait
SELECT...FOR UPDATE 語句的語法如下：
SELECT ... FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED];
其中：
l OF 這個OF子句在牽連到多個表時，具有較大作用，如不使用OF指定鎖定的表的列，則所有表的相關行均被鎖定，若在OF中指定了需修改的列，則只有與這些列相關的表的行才會被鎖定。
l WAIT 子句指定等待其他使用者釋放鎖的秒數，防止無限期的等待。
“使用FOR UPDATE WAIT”子句的優點如下：
１防止無限期地等待被鎖定的行；
２允許應用程式中對鎖的等待時間進行更多的控制。
３對於互動式應用程式非常有用，因為這些使用者不能等待不確定
４ 若使用了skip locked，則可以越過鎖定的行，不會報告由wait n 引發的‘資源忙’異常報告
2.9.1 FOR UPDATE 和 FOR UPDATE NOWAIT 的區別
for update nowait和 for update都會對所查詢到得結果集進行加鎖，所不同的是，如果另外一個程式正在修改結果集中的資料，for update nowait不會進行資源等待，只要發現結果集中有些資料被加鎖，立刻返回“ORA-00054錯誤，內容是資源正忙, 但指定以 NOWAIT 方式獲取資源”。
for update 和 for update nowait加上的是一個行級鎖，也就是隻有符合where條件的資料被加鎖。如果僅僅用update語句來更改資料時，可能會因為加不上鎖而沒有響應地、莫名其妙地等待，但如果在此之前，for update NOWAIT語句將要更改的資料試探性地加鎖，就可以通過立即返回的錯誤提示而明白其中的道理，或許這就是For Update和NOWAIT的意義之所在。
|
會話1： SYS@oratest S1> SELECT EMPNO, ENAME FROM SCOTT.EMP WHERE EMPNO = '7369' FOR UPDATE NOWAIT;
EMPNO ENAME ---------- ---------- 7369 SMITH 會話2： SYS@oratest S2> SELECT EMPNO, ENAME FROM SCOTT.EMP WHERE EMPNO = '7369' FOR UPDATE NOWAIT; SELECT EMPNO, ENAME FROM SCOTT.EMP WHERE EMPNO = '7369' FOR UPDATE NOWAIT * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
上面會話都提交commit，開啟會話1，不使用NOWAIT： SYS@oratest S1> SELECT EMPNO, ENAME FROM SCOTT.EMP WHERE EMPNO = '7369' FOR UPDATE ;
EMPNO ENAME ---------- ---------- 7369 SMITH
SYS@oratest S1> 開啟另一會話 SYS@oratest S2> SELECT EMPNO, ENAME FROM SCOTT.EMP WHERE EMPNO = '7369' FOR UPDATE ;
====>>>>> 產生了阻塞
|
阻塞，不返回錯誤。提交第一個會話，第二個回話自動執行，然後提交第二個會話
二.9.2 SELECT...FOR UPDATE OF COLUMNS
select for update of，這個of子句在牽連到多個表時，具有較大作用，如不使用of指定鎖定的表的列，則所有表的相關行均被鎖定，若在of中指定了需修改的列，則只有與這些列相關的表的行才會被鎖定。
|
會話1： SYS@oratest S1> SELECT * FROM SCOTT.EMP E, SCOTT.DEPT D WHERE E.DEPTNO = D.DEPTNO FOR UPDATE;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ---------- -------------- ------------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 30 SALES CHICAGO 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 10 ACCOUNTING NEW YORK
14 rows selected. 會話2： SYS@oratest S2> SELECT * FROM SCOTT.DEPT FOR UPDATE NOWAIT; SELECT * FROM SCOTT.DEPT FOR UPDATE NOWAIT * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SYS@oratest S2> SYS@oratest S2> SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F8FABF13398 00007F8FABF133F8 16 TM 77667 0 3 0 201 0 00007F8FABF13398 00007F8FABF133F8 16 TM 77669 0 3 0 201 0 000000007620A7C0 000000007620A838 16 TX 327687 1138 6 0 201 0
SYS@oratest S2> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (16,27) ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- --------- ------ ------------- ------------- ------------ --------------- 16 SCOTT EMP Row-X (SX) None 225 Not Blocking 16 SCOTT DEPT Row-X (SX) None 225 Not Blocking SYS@oratest S2>
|
可以看到，會話1在SCOTT.EMP和SCOTT.DEPT表上都加上了3級的行級排它鎖。
提交以上的會話，然後繼續試驗OF特性：
|
會話1： SYS@oratest S1> SELECT * FROM SCOTT.EMP E, SCOTT.DEPT D WHERE E.DEPTNO = D.DEPTNO FOR UPDATE OF SAL ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ---------- -------------- ------------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 30 SALES CHICAGO 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 10 ACCOUNTING NEW YORK
14 rows selected.
會話2： SYS@oratest S2> SELECT * FROM SCOTT.DEPT FOR UPDATE NOWAIT;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SYS@oratest S2>
SYS@oratest S1> SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F73CBCE38D8 00007F73CBCE3938 16 TM 77669 0 3 0 114 0 000000007620A7C0 000000007620A838 16 TX 327698 1138 6 0 114 0 00007F73CBCE38D8 00007F73CBCE3938 27 TM 77667 0 3 0 81 0 000000007620B1D0 000000007620B248 27 TX 131076 1128 6 0 81 0
SYS@oratest S1> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (16,27) ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- --------- ------ ------------- ------------- ------------ --------------- 16 SCOTT EMP Row-X (SX) None 123 Not Blocking 27 SCOTT DEPT Row-X (SX) None 90 Not Blocking
SYS@oratest S1>
|
可以看到，會話1在SCOTT.EMP表上加上了3級的行級排它鎖，而會話2在和SCOTT.DEPT表上加上了3級的行級排它鎖。
2.9.3 9i中的SELECT FOR UPDATE鎖
|
SQL*Plus: Release 9.2.0.1.0 - Production on 星期一 11月 14 17:29:40 2016
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
請輸入使用者名稱: sys as sysdba 請輸入口令:
連線到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production
SQL> set line 9999 SQL> set pagesize 9999 SQL> select * from scott.emp for update;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
已選擇14行。
SQL> select distinct sid from v$mystat;
SID ---------- 10
SQL> SELECT * FROM V$LOCK A WHERE A.SID=10 ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 67B4E0F8 67B4E10C 10 TM 30139 0 2 0 35 0 67BAB0CC 67BAB1D8 10 TX 131082 2874 6 0 25 0
SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID =10 ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- --------- ------ ------------- ------------- ------------ ------------------ 10 SCOTT EMP Row-S (SS) None 99 Not Blocking
|
可以看到在Oracle 10g之前，SELECT FOR UPDATE獲取的是2級TM鎖，在Oracle 10g及其之後的版本中，SELECT FOR UPDATE獲取的是3級TM鎖。
2.9.4 總結
1. SELECT * FROM TABLE1 FOR UPDATE 鎖定表的所有行，其它會話只能讀不能寫
2. SELECT * FROM TABLE1 WHERE PKID = 1 FOR UPDATE 只鎖定PKID=1的行
3. SELECT * FROM TABLE1 A JOIN TABLE2 B ON A.PKID=B.PKID FOR UPDATE 鎖定兩個表的所有記錄
4. SELECT * FROM TABLE1 A JOIN TABLE2 B ON A.PKID=B.PKID WHERE A.PKID = 10 FOR UPDATE 鎖定兩個表的中滿足條件的行
5. SELECT * FROM TABLE1 A JOIN TABLE2 B ON A.PKID=B.PKID WHERE A.PKID = 10 FOR UPDATE OF A.PKID 只鎖定TABLE1中滿足條件的行
FOR UPDATE 是把所有的表都鎖定。FOR UPDATE OF 根據OF後表的條件鎖定相對應的表。
2.10 Oracle包被鎖定的原因分析及解決方案
摘抄自網路，小麥苗感覺自己對這個部分也沒啥可寫的，主要是包不能編譯的時候需要查詢DBA_DDL_LOCKS檢視，最後殺會話的時候需要穩重一點。
在資料庫的開發過程中，經常碰到包、儲存過程、函式無法編譯或編譯時會導致PL/SQL 無法響應的問題。碰到這種問題，基本上都要重啟資料庫解決，嚴重浪費開發時間。本文將就產生這種現象的原因和解決方案做基本的介紹。
問題分析
從事資料庫開發的都知道鎖的概念，如：執行 Update Table xxx Where xxx 的時候就會產生鎖。這種常見的鎖在Oracle裡面被稱為DML鎖。在Oracle中還有一種DDL鎖，主要用來保證儲存過程、表結構、檢視、包等資料庫物件的完整性，這種鎖的資訊可以在DBA_DDL_LOCKS中查到。注意：V$LOCKED_OBJECT記錄的是DML鎖資訊，DDL鎖的資訊不在裡面。
對應DDL鎖的是DDL語句，DDL語句全稱資料定義語句（Data Define Language）。用於定義資料的結構或Schema，如：CREATE、ALTER、DROP、TRUNCATE、COMMENT、RENAME。當我們在執行某個儲存過程、或者編譯它的時候Oracle會自動給這個物件加上DDL鎖，同時也會對這個儲存過程所引用的物件加鎖。
舉例：
1、 開啟一個PL/SQL，開始除錯某個函式（假設為：FUN_CORE_SERVICECALL），並保持在除錯狀態
2、 開啟一個SQL Window，輸入Select * From dba_ddl_locks a Where a.name = 'FUN_CORE_SERVICECALL' 會發現一行記錄：
3、 開啟一個新的PL/SQL，重新編譯這個函式。我們會發現此時已經無法響應了
4、 回到第一個PL/SQL,重新執行Select * From dba_ddl_locks a Where a.name = 'FUN_CORE_SERVICECALL' 我們將會看到如下記錄：
5、 上述的情況表明發生了鎖等待的情況。
當我們試圖編譯、修改儲存過程、函式、包等對資料物件的時候，如果別人也正在編譯或修改他們時就會產生鎖等待；或者我們在編譯某個儲存過程的時候，如果它所引用的資料庫物件正在被修改應該也會產生鎖等待。這種假設有興趣的兄弟可以測試下，不過比較困難。
解決方案
碰到這種問題，如果知道是被誰鎖定了（可以查出來的），可以讓對方儘快把鎖釋放掉；實在查不出來只能手工將這個鎖殺掉了。步驟如下：
1、 首先查出哪些程式鎖住了這個物件，語句如下：
Select b.SID,b.SERIAL#
From dba_ddl_locks a, v$session b
Where a.session_id = b.SID
And a.name = 'FUN_CORE_SERVICECALL';
2、 執行如下語句殺程式：alter system kill session 'sid,serial#' IMMEDIATE;
3、 執行了以上的語句後，有的時候不一定能夠將程式殺掉。這個時候就需要連到資料庫伺服器上殺掉伺服器端的程式了，查詢語句：
Select spid, osuser, s.program
From v$session s, v$process p
Where s.paddr = p.addr
And s.sid =（上面查出來的SID）
在伺服器上執行如下語句：
#kill -9 spid（UNIX平臺）
orakill sid thread（Windows平臺 SID是Oracle的例項名，thread是上面查出來的SID）
執行完4步以後基本上就可以殺掉這些鎖死的程式了，不放心的話可以再執行第一步確認下。
2.10.1 實驗
|
SQL> select distinct sid from v$mystat;
SID ---------- 24
SQL> CREATE OR REPLACE PROCEDURE PRO_TESTDDL_LHR AS 2 3 V_COUNT NUMBER; 4 5 BEGIN 6 7 SELECT COUNT(1) INTO V_COUNT FROM SCOTT.EMP_LHR; 8 9 DBMS_LOCK.SLEEP(600); 10 11 END; 12 /
Procedure created.
SQL> exec PRO_TESTDDL_LHR;
====>>>>> 指令碼在執行
|
檢視DDL鎖：
SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID = 24;
SELECT *
FROM V$ACCESS A
WHERE A.SID = 24
AND A.OBJECT IN ('PRO_TESTDDL_LHR', 'EMP_LHR', 'DBMS_LOCK');
2.11 建立索引的鎖
2.11.1 建立或重建索引會阻塞DML操作
版本：11.2.0.3
|
首先建表T_INDEX_161113並插入很多資料 SYS@oratest S1> CREATE TABLE T_INDEX_161113 AS SELECT * FROM DBA_OBJECTS;
Table created.
SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;
75349 rows created.
SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;
150698 rows created.
SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;
301396 rows created.
SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;
602792 rows created.
SYS@oratest S1> COMMIT;
Commit complete. 接著再在該表上建立一個索引 SYS@oratest S1> CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME);
在建立索引的同時，在會話2上插入一條記錄： SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;
====>>>>> 產生了阻塞 在建立索引的同時，查詢相關鎖的資訊： SQL> SELECT SID, 2 A.BLOCKING_SESSION, 3 EVENT, 4 A.P1, 5 A.P2, 6 A.P3, 7 CHR(BITAND(P1, -16777216) / 16777215) || 8 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 9 BITAND(P1, 65535) "MODE", 10 (SELECT OBJECT_NAME FROM DBA_OBJECTS D WHERE D.OBJECT_ID = A.P2) OBJECT_NAME 11 FROM GV$SESSION A 12 WHERE A.SID=141;
SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE OBJECT_NAME ---------- ---------------- ----------------------- ---------- ---------- ---------- ---- ---------- ---------------- 142 21 enq: TM - contention 1414332419 77629 0 TM 3 T_INDEX_161113
SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (21,142) AND A.TYPE IN ('TX','TM'); ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F44001842E0 00007F4400184340 142 TM 77629 0 0 3 2 0 00007F44001842E0 00007F4400184340 21 TM 77629 0 4 0 3 1 00007F44001842E0 00007F4400184340 21 TM 18 0 3 0 3 0 0000000076273C58 0000000076273CD0 21 TX 65567 846 6 0 3 0
SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (21, 142); SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- --------- -------------------- ------------- ------------- ------------ -------------------- 142 SYS T_INDEX_161113 None Row-X (SX) 2 Not Blocking 21 SYS T_INDEX_161113 Share None 3 Blocking 21 SYS OBJ$ Row-X (SX) None 3 Not Blocking
SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE 2 FROM DBA_OBJECTS D 3 WHERE D.OBJECT_ID IN (18, 77629);
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE ---------- ---------------------- ---------- ------------------- SYS T_INDEX_161113 77629 TABLE SYS OBJ$ 18 TABLE SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (21, 142) AND D.name NOT IN ('STANDARD','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','UTL_RAW','DBMS_APPLICATION_INFO','SDO_GEOR_DEF','SQL_TXT','DBMS_ASSERT','SDO_GEOR_DEF','TRACE_PUT_LINE','PLITBLM','DICTIONARY_OBJ_TYPE','DDLREPLICATION','DBMS_STANDARD','DBMS_APPLICATION_INFO','UTL_FILE','DDLAUX','DBMS_ASSERT','STANDARD','UTL_RAW','DDLREPLICATION','UTL_FILE','DDLAUX','GGS_MARKER_SEQ','DATABASE','LOGIN_USER','FILTERDDL','DBMS_UTILITY','GGS_DDL_SEQ','SYSEVENT','DBMS_UTILITY','LOGIN_USER','UTL_FILE','DATABASE','SDO_GEOR_DEF','UTL_RAW','GGS_DDL_SEQ','SDO_GEOR_DEF','DICTIONARY_OBJ_TYPE','UTL_RAW','DDLREPLICATION','DBMS_UTILITY','SYSEVENT','IS_VPD_ENABLED','DBMS_APPLICATION_INFO','FILTERDDL','DDLREPLICATION','STANDARD','DDLAUX','GGS_MARKER_SEQ','DDLAUX','SQL_TXT','PLITBLM','AW_DROP_PROC','DBMS_APPLICATION_INFO','DBMS_UTILITY','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','STANDARD','DBMS_STANDARD','TRACE_PUT_LINE','UTL_FILE');
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU ---------- --------- ----------------- ----------- --------- --------- 21 SYS 73 Share None 21 SYS IDX_TEST_LHR Index Exclusive None
|
可以發現在會話1中，在建立索引的過程中會生成2個TM鎖，鎖類別分別為4和3，根據查詢結果發現lmode=4的object_id為77629的物件對應的是T_INDEX_161113這個表，對應的是TM的S鎖。另一個lmode=3的鎖物件是系統基表OBJ$表，允許其它會話對該表執行DML操作。可以得出這樣一個結論:當對錶進行建立索引操作時，會伴隨出現LMODE=4的S鎖。根據鎖的相容模式可以發現S鎖和任何DML操作都是衝突的！所以，尤其是在生產上，當在一個很大的表上進行索引建立的時候，任何對該表的DML操作都會被夯住!!!
從DBA_DDL_LOCKS檢視可以看到，建索引的同時有6級排它DDL鎖。
2.11.2 Oracle 11g下ONLINE選項不會堵塞DML操作
版本：11.2.0.3
接著上面的實驗，重建索引的時候加上ONLINE，由於會話斷開了，重新開2個會話，會話1為22，會話2為142：
|
SYS@oratest S1> ALTER INDEX IDX_TEST_LHR REBUILD ONLINE;
在建立索引的同時，在會話2上插入一條記錄： SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;
1 row created.
====>>>>> 加上ONLINE後無阻塞產生 在建立索引的同時，查詢相關鎖的資訊： SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (22,141) AND A.TYPE IN ('TX','TM');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000774D9C08 00000000774D9C60 22 TX 327688 1122 0 4 761 0 00007FD883B38350 00007FD883B383B0 22 TM 77629 0 2 0 768 0 00007FD883B38350 00007FD883B383B0 22 TM 77643 0 4 0 767 0 0000000076274668 00000000762746E0 22 TX 196612 1119 6 0 768 0 0000000076236E38 0000000076236EB0 141 TX 327688 1122 6 0 763 1 00007FD883B38350 00007FD883B383B0 141 TM 77629 0 3 0 763 0
6 rows selected.
SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (22,141);
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ----------- -------------------- ------------- ------------- ------------ ---------------------------------------- 141 SYS T_INDEX_161113 Row-X (SX) None 625 Not Blocking 22 SYS T_INDEX_161113 Row-S (SS) None 630 Not Blocking 22 SYS SYS_JOURNAL_77631 Share None 629 Not Blocking SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE 2 FROM DBA_OBJECTS D 3 WHERE D.OBJECT_ID IN (77629, 77643);
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE ---------- ------------------------- ---------- ------------------- SYS SYS_JOURNAL_77631 77643 TABLE SYS T_INDEX_161113 77629 TABLE
SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (22,141) AND D.name NOT IN ('STANDARD','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','UTL_RAW','DBMS_APPLICATION_INFO','SDO_GEOR_DEF','SQL_TXT','DBMS_ASSERT','SDO_GEOR_DEF','TRACE_PUT_LINE','PLITBLM','DICTIONARY_OBJ_TYPE','DDLREPLICATION','DBMS_STANDARD','DBMS_APPLICATION_INFO','UTL_FILE','DDLAUX','DBMS_ASSERT','STANDARD','UTL_RAW','DDLREPLICATION','UTL_FILE','DDLAUX','GGS_MARKER_SEQ','DATABASE','LOGIN_USER','FILTERDDL','DBMS_UTILITY','GGS_DDL_SEQ','SYSEVENT','DBMS_UTILITY','LOGIN_USER','UTL_FILE','DATABASE','SDO_GEOR_DEF','UTL_RAW','GGS_DDL_SEQ','SDO_GEOR_DEF','DICTIONARY_OBJ_TYPE','UTL_RAW','DDLREPLICATION','DBMS_UTILITY','SYSEVENT','IS_VPD_ENABLED','DBMS_APPLICATION_INFO','FILTERDDL','DDLREPLICATION','STANDARD','DDLAUX','GGS_MARKER_SEQ','DDLAUX','SQL_TXT','PLITBLM','AW_DROP_PROC','DBMS_APPLICATION_INFO','DBMS_UTILITY','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','STANDARD','DBMS_STANDARD','TRACE_PUT_LINE','UTL_FILE'); no rows selected
SQL> SELECT SID, 2 A.BLOCKING_SESSION, 3 EVENT, 4 A.P1, 5 A.P2, 6 A.P3, 7 CHR(BITAND(P1, -16777216) / 16777215) || 8 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 9 BITAND(P1, 65535) "MODE", 10 (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT 11 FROM GV$SESSION A 12 WHERE A.SID IN (141,22);
SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT ---------- ---------------- ---------------------------------- ---------- ---------- ---------- ---- ---------- ----------------------------------------------------------------------- 22 141 enq: TX - row lock contention 1415053316 327688 1122 TX 4 ALTER INDEX IDX_TEST_LHR REBUILD ONLINE 141 SQL*Net message from client 1650815232 1 0 be 28928 INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1
|
可以發現在會話1中，在加上ONLINE重建索引的過程中會生成2個TM鎖，鎖類別分別為2和4，根據查詢結果發現lmode=2的object_id為77629的物件對應的是T_INDEX_161113這個表，對應的是TM的Row-S (SS)鎖即行級共享鎖，該鎖允許其它會話對該表執行DML操作。另一個lmode=4的鎖物件是SYS_JOURNAL_77631，應該為系統臨時建立的物件，對應的是TM的S鎖。
在會話2中，TX為6的鎖，阻塞了其它會話，在這裡其實是阻塞了會話1的重建索引的操作。
可以得出這樣一個結論:當對錶進行建立或重建索引操作時，可以加上ONLINE選項，不阻塞其它會話的DML操作，但是在建立或重建索引的過程中，其它的會話產生的事務會阻塞索引的建立或重建操作，所以必須結束其它會話的事務才能讓建立或重建索引的操作完成。
注意：在加上ONLINE選項建立索引的過程中，若手動CTRL+C取消後，可能導致索引被鎖，出現ORA-08104: this index object 77645 is being online built or rebuilt的錯誤，這個時候可以利用如下的指令碼清理物件，77645為物件的OBJECT_ID：
|
DECLARE DONE BOOLEAN; BEGIN DONE := DBMS_REPAIR.ONLINE_INDEX_CLEAN(77645); END; |
2.11.3 Oracle 10g下ONLINE選項會堵塞DML操作
版本為：10.2.0.1.0
重新開3個會話，會話1為143，會話2為152，會話3為158：
|
SYS@lhrdb S1> alter index IDX_TEST1_LHR rebuild online;
在建立索引的同時，在會話2上插入一條記錄： SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;
1 row created.
====>>>>> 加上ONLINE後仍然會阻塞DML語句，若無阻塞可以重新連線會話2再執行插入操作 在建立索引的同時，在會話3上插入一條記錄： SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;
1 row created.
====>>>>> 加上ONLINE後仍然會阻塞DML語句，若無阻塞可以重新連線會話3再執行插入操作
在建立索引的同時，查詢相關鎖的資訊： SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158) ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000704A7850 00000000704A7870 143 DL 53121 0 3 0 144 0 00000000704A7980 00000000704A79A0 143 DL 53121 0 3 0 144 0 00000000703B8630 00000000703B8658 143 TM 53121 0 2 4 161 0 00000000703B8730 00000000703B8758 143 TM 53156 0 4 0 161 0 000000006F49F268 000000006F49F3F0 143 TX 196651 452 6 0 159 0 00000000703B8930 00000000703B8958 152 TM 53121 0 0 3 141 0 00000000703B8830 00000000703B8858 158 TM 53121 0 3 0 153 1 000000006F45DC78 000000006F45DE00 158 TX 262170 423 6 0 153 0
8 rows selected. SQL> SELECT * FROM V$lock_Type d WHERE d.TYPE='DL';
TYPE NAME ID1_TAG DESCRIPTION -------- ---------------------------------- ----------- ------------ DL Direct Loader Index Creation object # Lock to prevent index DDL during direct load
SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000703B8630 00000000703B8658 143 TM 53121 0 2 4 161 0 00000000703B8730 00000000703B8758 143 TM 53156 0 4 0 161 0 000000006F49F268 000000006F49F3F0 143 TX 196651 452 6 0 159 0 00000000703B8930 00000000703B8958 152 TM 53121 0 0 3 141 0 00000000703B8830 00000000703B8858 158 TM 53121 0 3 0 153 1 000000006F45DC78 000000006F45DE00 158 TX 262170 423 6 0 153 0
6 rows selected. SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (143,152,158) ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ------------------------------ ------------------------------ ------------- ------------- ------------ ---------------------------------------- 143 SYS T_INDEX_161113 Row-S (SS) Share 335 Not Blocking 143 SYS SYS_JOURNAL_53122 Share None 335 Not Blocking 152 SYS T_INDEX_161113 None Row-X (SX) 315 Blocking 158 SYS T_INDEX_161113 Row-X (SX) None 327 Blocking SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE 2 FROM DBA_OBJECTS D 3 WHERE D.OBJECT_ID IN (53121, 53156);
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE --------- ---------------------- ---------- ------------------- SYS T_INDEX_161113 53121 TABLE SYS SYS_JOURNAL_53122 53156 TABLE
SQL> SELECT d.owner,d.table_name,d.iot_type FROM dba_tables d WHERE d.table_name='SYS_JOURNAL_53122';
OWNER TABLE_NAME IOT_TYPE ------------------------------ ------------------------------ ------------ SYS SYS_JOURNAL_53122 IOT
SQL> SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (143,152,158) AND D.name NOT IN ('STANDARD','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','UTL_RAW','DBMS_APPLICATION_INFO','SDO_GEOR_DEF','SQL_TXT','DBMS_ASSERT','SDO_GEOR_DEF','TRACE_PUT_LINE','PLITBLM','DICTIONARY_OBJ_TYPE','DDLREPLICATION','DBMS_STANDARD','DBMS_APPLICATION_INFO','UTL_FILE','DDLAUX','DBMS_ASSERT','STANDARD','UTL_RAW','DDLREPLICATION','UTL_FILE','DDLAUX','GGS_MARKER_SEQ','DATABASE','LOGIN_USER','FILTERDDL','DBMS_UTILITY','GGS_DDL_SEQ','SYSEVENT','DBMS_UTILITY','LOGIN_USER','UTL_FILE','DATABASE','SDO_GEOR_DEF','UTL_RAW','GGS_DDL_SEQ','SDO_GEOR_DEF','DICTIONARY_OBJ_TYPE','UTL_RAW','DDLREPLICATION','DBMS_UTILITY','SYSEVENT','IS_VPD_ENABLED','DBMS_APPLICATION_INFO','FILTERDDL','DDLREPLICATION','STANDARD','DDLAUX','GGS_MARKER_SEQ','DDLAUX','SQL_TXT','PLITBLM','AW_DROP_PROC','DBMS_APPLICATION_INFO','DBMS_UTILITY','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','STANDARD','DBMS_STANDARD','TRACE_PUT_LINE','UTL_FILE','DBMS_SYS_SQL','DBMS_XDBZ0','DBMS_SYS_SQL','DBMS_SQL','DBMS_SQL','DBMS_XDBZ0');
no rows selected SQL> SELECT SID, 2 A.BLOCKING_SESSION, 3 EVENT, 4 A.P1, 5 A.P2, 6 A.P3, 7 CHR(BITAND(P1, -16777216) / 16777215) || 8 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 9 BITAND(P1, 65535) "MODE", 10 (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT 11 FROM GV$SESSION A 12 WHERE A.SID IN (143,152,158);
SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT ---------- ---------------- ---------------------------- ---------- ---------- ---------- ---- ---------- ----------------------------------------------- 143 158 enq: TM - contention 1414332420 53121 0 TM 4 alter index IDX_TEST1_LHR rebuild online 152 143 enq: TM - contention 1414332419 53121 0 TM 3 INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1 158 SQL*Net message from client 1650815232 1 0 be 28928 INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1
|
可以發現在會話1中，在加上ONLINE重建索引的過程中會生成2個TM鎖，鎖類別分別為2和4，根據查詢結果發現lmode=2的object_id為53121的物件對應的是T_INDEX_161113這個表，對應的是TM的Row-S (SS)鎖即行級共享鎖，該鎖允許其它會話對該表執行DML操作，但是該會話在請求模式為4的S鎖。另一個lmode=4的鎖物件是SYS_JOURNAL_53122，為系統臨時建立的索引組織表（IOT），對應的是TM的S鎖。
在會話2中，請求3級TM鎖。會阻塞關係可以看出，會話3阻塞了會話1，而會話1阻塞了會話2，所以提交會話3即可讓索引建立完成。
2.11.3.1 實驗10.2.0.1.0
版本為：10.2.0.1.0
重新開3個會話，會話1為143，會話2為152，會話3為158，會話1插入一條記錄：
|
SYS@lhrdb S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;
1 row created.
在會話2上採用ONLINE建立索引： SYS@lhrdb S2> alter index IDX_TEST1_LHR rebuild online;
====>>>>> 加上ONLINE後仍然會被阻塞 在建立索引的同時，查詢相關鎖的資訊： SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152) ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000703B8630 00000000703B8658 143 TM 53121 0 3 0 1119 1 000000006F495E38 000000006F495FC0 143 TX 524318 484 6 0 1119 0 00000000704A7980 00000000704A79A0 152 DL 53121 0 3 0 1113 0 00000000704A7850 00000000704A7870 152 DL 53121 0 3 0 1113 0 00000000703B8730 00000000703B8758 152 TM 53121 0 2 4 1113 0 00000000703B8830 00000000703B8858 152 TM 53162 0 4 0 1112 0
6 rows selected. SQL> SELECT * FROM V$lock_Type d WHERE d.TYPE='DL';
TYPE NAME ID1_TAG DESCRIPTION -------- ---------------------------------- ----------- ------------ DL Direct Loader Index Creation object # Lock to prevent index DDL during direct load
SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (143,152) ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ------------------------------ ------------------------------ ------------- ------------- ------------ ---------------------------------------- 143 SYS T_INDEX_161113 Row-X (SX) None 1176 Blocking 152 SYS SYS_JOURNAL_53122 Share None 1169 Not Blocking 152 SYS T_INDEX_161113 Row-S (SS) Share 1170 Not Blocking SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE 2 FROM DBA_OBJECTS D 3 WHERE D.OBJECT_ID IN (53121, 53162);
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE --------- ---------------------- ---------- ------------------- SYS T_INDEX_161113 53121 TABLE SYS SYS_JOURNAL_53122 53162 TABLE
SQL> SELECT d.owner,d.table_name,d.iot_type FROM dba_tables d WHERE d.table_name='SYS_JOURNAL_53122';
OWNER TABLE_NAME IOT_TYPE ------------------------------ ------------------------------ ------------ SYS SYS_JOURNAL_53122 IOT
SQL> SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (143,152,158) AND D.name NOT IN ('ALERT_QUE_R','AQ$_ALERT_QT_E','AW_DROP_PROC','DATABASE','DBMS_APPLICATION_INFO','DBMS_BACKUP_RESTORE','DBMS_HA_ALERTS_PRVT','DBMS_OUTPUT','DBMS_PRVT_TRACE','DBMS_RCVMAN','DBMS_SQL','DBMS_STANDARD','DBMS_SYS_SQL','DBMS_TRANSACTION','DBMS_UTILITY','DBMS_XDBZ0','DICTIONARY_OBJ_NAME','DICTIONARY_OBJ_OWNER','PLITBLM','SCHEDULER$_INSTANCE_S','STANDARD');
no rows selected SQL> SELECT SID, 2 A.BLOCKING_SESSION, 3 EVENT, 4 A.P1, 5 A.P2, 6 A.P3, 7 CHR(BITAND(P1, -16777216) / 16777215) || 8 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 9 BITAND(P1, 65535) "MODE", 10 (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT 11 FROM GV$SESSION A 12 WHERE A.SID IN (143,152);
SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT ---------- ---------------- ---------------------------- ---------- ---------- ---------- ---- ---------- ----------------------------------------------- 143 SQL*Net message from client 1650815232 1 0 be 28928 152 143 enq: TM - contention 1414332420 53121 0 TM 4 alter index IDX_TEST1_LHR rebuild online |
從上面的結果可以知道，會話2即建立索引的會話一共出現了4個鎖，兩個DL鎖，一個針對表T_INDEX_161113的TM鎖，一個是online rebuild index時需要的一箇中間表的TM鎖，中間表用於記錄rebuild期間的增量資料，原理類似於物化檢視日誌，其object_id為53162，這是一個索引組織表(IOT)，從這裡我們也可以發現IOT的優點和適合的場合，這張中間表只有插入，不會有刪除和修改操作，而且只有主鍵條件查詢，正是IOT最合適的場景。
會話2在請求一個模式為4的TM鎖，模式4會阻塞這個表上的所有DML操作，所以這時再往這個表上執行DML也會掛起。
會話3刪除一條語句：
|
SYS@lhrdb S3> delete from T_INDEX_161113 where rownum<=1;
====>>>>> 有阻塞 查詢鎖的資源： SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158) ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000703B8630 00000000703B8658 143 TM 53121 0 3 0 7573 1 000000006F495E38 000000006F495FC0 143 TX 524318 484 6 0 7573 0 00000000704A7850 00000000704A7870 152 DL 53121 0 3 0 7567 0 00000000704A7980 00000000704A79A0 152 DL 53121 0 3 0 7567 0 00000000703B8830 00000000703B8858 152 TM 53162 0 4 0 7566 0 00000000703B8730 00000000703B8758 152 TM 53121 0 2 4 7567 0 00000000703B8930 00000000703B8958 158 TM 53121 0 0 3 165 0
7 rows selected.
SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (143,152,158) ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ---------- ------------------------------ ------------- ------------- ------------ --------------- 143 SYS T_INDEX_161113 Row-X (SX) None 7582 Blocking 152 SYS T_INDEX_161113 Row-S (SS) Share 7576 Not Blocking 152 SYS SYS_JOURNAL_53122 Share None 7575 Not Blocking 158 SYS T_INDEX_161113 None Row-X (SX) 174 Blocking
SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (143,152,158) AND D.name NOT IN ('ALERT_QUE_R','AQ$_ALERT_QT_E','AW_DROP_PROC','DATABASE','DBMS_APPLICATION_INFO','DBMS_BACKUP_RESTORE','DBMS_HA_ALERTS_PRVT','DBMS_OUTPUT','DBMS_PRVT_TRACE','DBMS_RCVMAN','DBMS_SQL','DBMS_STANDARD','DBMS_SYS_SQL','DBMS_TRANSACTION','DBMS_UTILITY','DBMS_XDBZ0','DICTIONARY_OBJ_NAME','DICTIONARY_OBJ_OWNER','PLITBLM','SCHEDULER$_INSTANCE_S','STANDARD');
no rows selected
SQL> SELECT SID, 2 A.BLOCKING_SESSION, 3 EVENT, 4 A.P1, 5 A.P2, 6 A.P3, 7 CHR(BITAND(P1, -16777216) / 16777215) || 8 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 9 BITAND(P1, 65535) "MODE", 10 (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT 11 FROM GV$SESSION A 12 WHERE A.SID IN (143,152,158);
SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT ---------- ---------------- ------------------------------ ---------- ---------- ---------- ---- ---------- ---------------------------------------------- 143 SQL*Net message from client 1650815232 1 0 be 28928 152 143 enq: TM - contention 1414332420 53121 0 TM 4 alter index IDX_TEST1_LHR rebuild online 158 152 enq: TM - contention 1414332419 53121 0 TM 3 delete from T_INDEX_161113 where rownum<=1
SQL>
|
會話3請求模式為3的TM鎖無法獲得，會話被阻塞。這是因為鎖請求是需要排隊的，即使會話3和會話1是可以併發的，但由於會話2先請求鎖並進入等待佇列，後來的會話3也只好進入佇列等待。所以，如果在執行rebuild index online前有長事務，並且併發量比較大，則一旦執行alter index rebuild online，可能因為長事務阻塞，可能導致系統瞬間出現大量的鎖，對於壓力比較大的系統，這是一個不小的風險。這是需要迅速找出導致阻塞的會話kill掉，rebuild index online一旦執行，不可輕易中斷，否則可能遇到ORA-08104。
從會話級別可以看出，會話1阻塞了會話2，會話2阻塞了會話3，在會話1執行rollback，可以發現很短時間內會話3也正常執行完畢，說明會話2持有模式4的TM鎖的時間很短，然後在rebuild online的進行過程中，對錶加的是模式為2的TM鎖，所以這段時間不會阻塞DML操作：
回滾會話1，然後觀察鎖的情況：
|
SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158) ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000704A7850 00000000704A7870 152 DL 53121 0 3 0 8219 0 00000000704A7980 00000000704A79A0 152 DL 53121 0 3 0 8219 0 00000000703B8730 00000000703B8758 152 TM 53121 0 2 4 238 0 00000000703B8830 00000000703B8858 152 TM 53162 0 4 0 8218 0 000000006FFFDEB8 000000006FFFDF18 152 TS 0 4257321 6 0 237 0 000000006F4A7558 000000006F4A76E0 152 TX 262184 426 6 0 237 0 00000000703B8930 00000000703B8958 158 TM 53121 0 3 0 238 1 000000006F45DC78 000000006F45DE00 158 TX 589824 470 6 0 238 0
8 rows selected.
|
會話2又開始在請求模式4的TM鎖，被會話3阻塞!這時在會話1再執行DML操作，同樣會被會話2阻塞，進入鎖等待佇列。
在會話3執行rollback或者commit以後，會話2和會話3都很快執行完畢。
|
會話3： SYS@lhrdb S3> rollback;
Rollback complete.
會話2： SYS@lhrdb S2> alter index IDX_TEST1_LHR rebuild online;
Index altered.
SYS@lhrdb S2> SYS@lhrdb S2> SYS@lhrdb S2>
|
從上面的試驗可以發現，雖然rebuild index online在執行期間只持有模式2的TM鎖，不會阻塞DML操作，但在操作的開始和結束階段，是需要短暫的持有模式為4的TM鎖的，這段會阻塞表上的所有DML操作。我們在做rebuild index online的時候，一定要在開始和結束階段觀察系統中是否有長事務的儲存，對於併發量較大的系統，最嚴重的後果，可能在這兩個關鍵點導致資料庫產生大量鎖等待，系統負載飆升，甚至當機。
2.11.3.2 實驗11.2.0.3.0
版本為：11.2.0.3.0
開3個會話，會話1為16，會話2為27，會話3為150，會話1刪除一條記錄：
|
SYS@oratest S1> delete from T_INDEX_161113 where rownum<=1;
1 row deleted.
在會話2上採用ONLINE建立索引： SYS@lhrdb S2> alter index IDX_TEST_LHR rebuild online;
====>>>>> 會話2掛起 在建立索引的同時，查詢相關鎖的資訊： SYS@oratest S3> SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000774DA148 00000000774DA1A0 16 AE 100 0 4 0 17039 0 00007F95B6CC6C88 00007F95B6CC6CE8 16 TM 77629 0 3 0 4034 0 000000007620A7C0 000000007620A838 16 TX 131076 1126 6 0 4034 1 00000000774D9410 00000000774D9468 27 AE 100 0 4 0 18569 0 00000000774D9250 00000000774D92A8 27 DL 77629 0 3 0 115 0 00000000774DA4C8 00000000774DA520 27 DL 77629 0 3 0 115 0 00000000774DA5A8 00000000774DA600 27 OD 77631 0 6 0 115 0 00000000774D9A30 00000000774D9A88 27 OD 77629 0 4 0 115 0 00007F95B6CC6C88 00007F95B6CC6CE8 27 TM 77629 0 2 0 115 0 00007F95B6CC6C88 00007F95B6CC6CE8 27 TM 77665 0 4 0 115 0 00000000774D9090 00000000774D90E8 27 TO 68064 1 3 0 16833 0 0000000076218728 00000000762187A0 27 TX 196627 1131 6 0 115 0 00000000774D9B10 00000000774D9B68 27 TX 131076 1126 0 4 115 0
13 rows selected.
SYS@oratest S3> SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F95B6CC5588 00007F95B6CC55E8 16 TM 77629 0 3 0 4071 0 000000007620A7C0 000000007620A838 16 TX 131076 1126 6 0 4071 1 00007F95B6CC5588 00007F95B6CC55E8 27 TM 77629 0 2 0 152 0 00007F95B6CC5588 00007F95B6CC55E8 27 TM 77665 0 4 0 152 0 00000000774D9B10 00000000774D9B68 27 TX 131076 1126 0 4 152 0 0000000076218728 00000000762187A0 27 TX 196627 1131 6 0 152 0
6 rows selected.
SYS@oratest S3> SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE IN ('AE','DL','OD','TO');
TYPE NAME ID1_TAG DESCRIPTION ------- ------------------------------ --------------- ------------------------------------------ --- ----------- DL Direct Loader Index Creation object # Lock to prevent index DDL during direct load AE Edition Lock edition obj# Prevent Dropping an edition in use OD Online DDLs object # Lock to prevent concurrent online DDLs TO Temp Object object # Synchronizes DDL and DML operations on a temp object
SYS@oratest S3> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE 2 FROM DBA_OBJECTS D 3 WHERE D.OBJECT_ID IN (77665, 77629);
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE ------- ------------------- ---------- ------------------- SYS SYS_JOURNAL_77631 77665 TABLE SYS T_INDEX_161113 77629 TABLE
SYS@oratest S3>
SYS@oratest S3> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (16,27) ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- --------- ------------------ ------------- ------------- ------------ --------------- 16 SYS T_INDEX_161113 Row-X (SX) None 4093 Not Blocking 27 SYS SYS_JOURNAL_77631 Share None 174 Not Blocking 27 SYS T_INDEX_161113 Row-S (SS) None 174 Not Blocking
SYS@oratest S3> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (16,27) AND D.name NOT IN ('ALERT_QUE_R','AQ$_ALERT_QT_E','AW_DROP_PROC','DATABASE','DBMS_APPLICATION_INFO','DBMS_BACKUP_RESTORE','DBMS_HA_ALERTS_PRVT','DBMS_OUTPUT','DBMS_PRVT_TRACE','DBMS_RCVMAN','DBMS_SQL','DBMS_STANDARD','DBMS_SYS_SQL','DBMS_TRANSACTION','DBMS_UTILITY','DBMS_XDBZ0','DICTIONARY_OBJ_NAME','DICTIONARY_OBJ_OWNER','PLITBLM','SCHEDULER$_INSTANCE_S','STANDARD','SDO_GEOR_DEF','SQL_TXT');
no rows selected
SYS@oratest S3> SELECT SID, 2 A.BLOCKING_SESSION, 3 EVENT, 4 A.P1, 5 A.P2, 6 A.P3, 7 CHR(BITAND(P1, -16777216) / 16777215) || 8 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 9 BITAND(P1, 65535) "MODE", 10 (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT 11 FROM GV$SESSION A 12 WHERE A.SID IN (16,27);
SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT ---------- ---------------- ---------------------------------- ---------- ---------- ---------- ---- ---------- --------------------------------------------- 16 SQL*Net message from client 1650815232 1 0 be 28928 delete from T_INDEX_161113 where rownum<=1 27 16 enq: TX - row lock contention 1415053316 131076 1126 TX 4 alter index IDX_TEST_LHR rebuild online
|
可以看到會話2正在請求一個模式為4的TX鎖，注意和Oracle 10g請求的TM鎖是不一樣的，而且在我們以前的概念中，TX鎖的模式都是6，這裡出現了模式4的TX鎖請求，應該是Oracle 11g中新引入的。那麼模式4的TX鎖和TM鎖有什麼不同呢？我們繼續前面的實驗步驟：
|
SYS@oratest S3> delete from T_INDEX_161113 where object_id=2;
16 rows deleted.
|
會話3的DML操作順利完成，沒有被阻塞。而在10g當中，會話3是會被會話2請求的TM鎖所阻塞的，這一點改進是非常有意思的，這樣即使rebuid online操作被會話1的長事務阻塞，其他會話的DML操作，只要不和會話1衝突，都可以繼續操作，在Oracle 10g及以前版本中的執行rebuild index online而造成鎖等待的風險被大大的降低了。
依次提交會話1和會話3，則會話2成功完成。
Oracle 11g在很多細節方面確實做了不少的優化，而且像這樣的優化，對於提高系統的高可用性的好處是不言而喻的，在Oracle 11g中，執行rebuild index online的風險將比10g以及更老版本中小得多，因為從頭至尾都不再阻塞DML操作了，終於可以算得上名副其實的online操作了。
2.11.4 利用10704和10046跟蹤鎖
使用10704事件跟蹤以下四類操作並對比跟蹤結果：
*create index
*alter index rebuild
*create index online
*alter index rebuild online
1、create index與alter index rebuild所獲取的TM鎖完全一致
2、create index online與alter index rebuild online所獲取的TM鎖、臨時表完全一致
2.11.4.1 10g
版本：10.2.0.1
一、 create index|
SQL> drop index IDX_TEST1_LHR;
Index dropped.
SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> CREATE INDEX IDX_TEST1_LHR ON T_INDEX_161113(OBJECT_NAME);
Index created.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> CREATE OR REPLACE VIEW VW_SQL_TRACE_NAME_LHR AS 2 SELECT D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' || 3 P.SPID || '.trc' TRACE_FILE_NAME 4 FROM (SELECT P.SPID 5 FROM V$MYSTAT M, V$SESSION S, V$PROCESS P 6 WHERE M.STATISTIC# = '1' 7 AND S.SID = M.SID 8 AND P.ADDR = S.PADDR) P, 9 (SELECT T.INSTANCE 10 FROM V$THREAD T, V$PARAMETER V 11 WHERE V.NAME = 'thread' 12 AND (V.VALUE = '0' OR TO_CHAR(T.THREAD#) = V.VALUE)) I, 13 (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') D;
View created.
SQL> SQL> SQL> SQL> CREATE OR REPLACE PUBLIC SYNONYM SYN_TRACENAME_LHR FOR VW_SQL_TRACE_NAME_LHR;
Synonym created.
SQL> SQL> select * from VW_SQL_TRACE_NAME_LHR;
TRACE_FILE_NAME ----------------------------- /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_516.trc SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID, 5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID 6 FROM DBA_OBJECTS 7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST1_LHR');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID --------------- ---------- -------------- ------------- ------------- IDX_TEST1_LHR 53239 53239 cff7 cff7 T_INDEX_161113 53121 53121 cf81 cf81
|
trace檔案如下，搜字串“cf81”：
|
1、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 16:23:57.846 ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
2、獲取T_INDEX_161113表mode=4 TM鎖 *** 2016-11-21 16:23:57.847 ksqgtl *** TM-0000cf81-00000000 mode=4 flags=0x401 timeout=0 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
3、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 16:24:06.899 ksqrcl: DL,cf81,0 ksqrcl: returns 0
4、釋放T_INDEX_161113表TM鎖 *** 2016-11-21 16:24:06.902 ksqrcl: TM,cf81,0 ksqrcl: returns 0
|
二、 alter index ... rebuild
|
SQL> CONN / AS SYSDBA Connected. SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> ALTER INDEX IDX_TEST1_LHR REBUILD;
Index altered.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> select * from VW_SQL_TRACE_NAME_LHR;
TRACE_FILE_NAME ----------------------------- /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_1383.trc
SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID, 5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID 6 FROM DBA_OBJECTS 7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR'); OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID --------------- ---------- -------------- ------------- ------------- IDX_TEST1_LHR 53239 53242 cff7 cffa T_INDEX_161113 53121 53121 cf81 cf81
|
trace檔案如下，搜字串“cf81”：
|
1、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 16:37:04.615 ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
2、獲取T_INDEX_161113表mode=4 TM鎖 *** 2016-11-21 16:37:04.616 ksqgtl *** TM-0000cf81-00000000 mode=4 flags=0x401 timeout=0 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
3、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 16:37:09.948 ksqrcl: DL,cf81,0 ksqrcl: returns 0
4、釋放T_INDEX_161113表TM鎖 *** 2016-11-21 16:37:10.003 ksqrcl: TM,cf81,0 ksqrcl: returns 0
|
三、 create index ... online
|
SQL> conn / as sysdba Connected.
SQL> drop index IDX_TEST1_LHR;
Index dropped.
SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> CREATE INDEX IDX_TEST1_LHR ON T_INDEX_161113(OBJECT_NAME) ONLINE;
Index created.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> select * from VW_SQL_TRACE_NAME_LHR;
TRACE_FILE_NAME ----------------------------- /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_1915.trc
SQL> col object_name format a15 SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID, 5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID 6 FROM DBA_OBJECTS 7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR'); OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID --------------- ---------- -------------- ------------- ------------- IDX_TEST1_LHR 53243 53243 cffb cffb T_INDEX_161113 53121 53121 cf81 cf81
|
trace檔案如下，搜字串“cf81”：
|
1、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 16:45:14.381 ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
2、獲取T_INDEX_161113表mode=2 TM鎖 *** 2016-11-21 16:45:14.383 ksqgtl *** TM-0000cf81-00000000 mode=2 flags=0x401 timeout=21474836 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
3、2級TM鎖轉換為4級TM鎖，4級TM鎖轉換為2級TM鎖 *** 2016-11-21 16:45:14.659 ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836 *** 2016-11-21 16:45:14.659 ksqcmi: TM,cf81,0 mode=4 timeout=21474836 ksqcmi: returns 0 ksqcnv: RETURNS 0 *** 2016-11-21 16:45:14.659 ksqcnv: TM-0000cf81,00000000 mode=2 timeout=21474836 *** 2016-11-21 16:45:14.659 ksqcmi: TM,cf81,0 mode=2 timeout=21474836 ksqcmi: returns 0 ksqcnv: RETURNS 0 WAIT #1: nam='db file sequential read' ela= 14264 file#=1 block#=62781 blocks=1 obj#=53121 tim=1445037026096411 WAIT #1: nam='db file scattered read' ela= 19094 file#=1 block#=62913 blocks=3 obj#=53121 tim=1445037026118946 WAIT #1: nam='db file scattered read' ela= 4712 file#=1 block#=62980 blocks=5 obj#=53121 tim=1445037026125569 。。。。。。。。。。。
4、2級TM鎖轉換為4級TM鎖 *** 2016-11-21 16:45:26.192 ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836 *** 2016-11-21 16:45:26.192 ksqcmi: TM,cf81,0 mode=4 timeout=21474836 ksqcmi: returns 0 ksqcnv: RETURNS 0
5、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 16:45:27.274 ksqrcl: DL,cf81,0 ksqrcl: returns 0
6、釋放T_INDEX_161113表TM鎖 *** 2016-11-21 16:45:27.393 ksqrcl: TM,cf81,0 ksqrcl: returns 0 |
四、 alter index ... rebuild online
|
SQL> conn / as sysdba Connected.
SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> ALTER INDEX IDX_TEST1_LHR REBUILD ONLINE;
Index created.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> select * from VW_SQL_TRACE_NAME_LHR;
TRACE_FILE_NAME ----------------------------- /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_3347.trc
SQL> col object_name format a15
SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID, 5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID 6 FROM DBA_OBJECTS 7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR'); OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID --------------- ---------- -------------- ------------- ------------- IDX_TEST1_LHR 53243 53247 cffb cfff T_INDEX_161113 53121 53121 cf81 cf81
|
trace檔案如下，搜字串“cf81”：
|
1、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 17:06:23.837 ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
2、獲取T_INDEX_161113表mode=2 TM鎖 PARSING IN CURSOR #1 len=40 dep=0 uid=0 oct=9 lid=0 tim=1445038265466869 hv=1374438854 ad='6c6dc948' ALTER INDEX IDX_TEST1_LHR REBUILD ONLINE END OF STMT PARSE #1:c=6999,e=7057,p=0,cr=12,cu=0,mis=1,r=0,dep=0,og=1,tim=1445038265466867 *** 2016-11-21 17:06:23.838 ksqgtl *** TM-0000cf81-00000000 mode=2 flags=0x401 timeout=21474836 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
3、2級TM鎖轉換為4級TM鎖，4級TM鎖轉換為2級TM鎖 *** 2016-11-21 17:06:23.937 ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836 *** 2016-11-21 17:06:23.937 ksqcmi: TM,cf81,0 mode=4 timeout=21474836 ksqcmi: returns 0 ksqcnv: RETURNS 0 *** 2016-11-21 17:06:23.937 ksqcnv: TM-0000cf81,00000000 mode=2 timeout=21474836 *** 2016-11-21 17:06:23.937 ksqcmi: TM,cf81,0 mode=2 timeout=21474836 ksqcmi: returns 0 ksqcnv: RETURNS 0 WAIT #1: nam='db file sequential read' ela= 17434 file#=1 block#=62781 blocks=1 obj#=53121 tim=1445038265592696 WAIT #1: nam='db file scattered read' ela= 25149 file#=1 block#=62913 blocks=3 obj#=53121 tim=1445038265625891 WAIT #1: nam='db file scattered read' ela= 22659 file#=1 block#=62980 blocks=5 obj#=53121 tim=1445038265654375 WAIT #1: nam='db file sequential read' ela= 19 file#=1 block#=62984 blocks=1 obj#=53121 tim=1445038265654750 WAIT #1: nam='db file scattered read' ela= 23256 file#=1 block#=63142 blocks=2 obj#=53121 tim=1445038265680595 。。。。。。。。。。。。。。
4、2級TM鎖轉換為4級TM鎖 *** 2016-11-21 17:06:31.754 ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836 *** 2016-11-21 17:06:31.754 ksqcmi: TM,cf81,0 mode=4 timeout=21474836 ksqcmi: returns 0 ksqcnv: RETURNS 0
5、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 17:06:32.806 ksqrcl: DL,cf81,0 ksqrcl: returns 0
6、釋放T_INDEX_161113表TM鎖 *** 2016-11-21 17:06:32.976 ksqrcl: TM,cf81,0 ksqrcl: returns 0
|
2.11.4.2 11g
版本：11.2.0.3
一、 create index|
SQL> drop index IDX_TEST_LHR;
Index dropped.
SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME);
Index created.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> select value from v$diag_info where name like '%File%';
VALUE -------------------------------------------------------------------------------- /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23527.trc
SQL> col object_name format a15 SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID 5 FROM DBA_OBJECTS 6 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR'); OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID --------------- ---------- -------------- ------------- T_INDEX_161113 77629 77629 12f3d IDX_TEST_LHR 77884 77884 1303c
|
trace檔案如下，搜字串“12f3d”：
|
1、獲取T_INDEX_161113表mode=4 TM鎖 PARSING IN CURSOR #140411478315224 len=50 dep=1 uid=0 oct=26 lid=0 tim=1479709305055527 hv=3478035675 ad='716d5f28' sqlid='b3p9ubr7nx76v' LOCK TABLE "T_INDEX_161113" IN SHARE MODE NOWAIT END OF STMT PARSE #140411478315224:c=2000,e=3081,p=0,cr=19,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479709305055527
*** 2016-11-21 14:21:45.055 ksqgtl *** TM-00012f3d-00000000 mode=4 flags=0x401 timeout=0 *** ksqgtl: xcb=0x76273c58, ktcdix=2147483647, topxcb=0x76273c58 ktcipt(topxcb)=0x0
2、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 14:21:45.056 ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 *** ksqgtl: xcb=0x76273c58, ktcdix=2147483647, topxcb=0x76273c58 ktcipt(topxcb)=0x0
3、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 14:21:50.392 ksqrcl: DL,12f3d,0 ksqrcl: returns 0
4、釋放T_INDEX_161113表TM鎖 *** 2016-11-21 14:21:50.395 ksqrcl: TM,12f3d,0 ksqrcl: returns 0
|
二、 alter index ... rebuild
|
SQL> CONN / AS SYSDBA Connected. SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> ALTER INDEX IDX_TEST_LHR REBUILD;
Index altered.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> select value from v$diag_info where name like '%File%';
VALUE -------------------------------------------------------------------------------- /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23540.trc
SQL> col object_name format a15 SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID 5 FROM DBA_OBJECTS 6 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');
SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID, 5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID 6 FROM DBA_OBJECTS 7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID --------------- ---------- -------------- ------------- ------------- T_INDEX_161113 77629 77629 12f3d 12f3d IDX_TEST_LHR 77885 77886 1303d 1303e
|
trace檔案如下，搜字串“12f3d”：
|
1、獲取T_INDEX_161113表mode=4 TM鎖 PARSING IN CURSOR #140719831671200 len=59 dep=1 uid=0 oct=26 lid=0 tim=1479709686366785 hv=3620741631 ad='7176cbc8' sqlid='chctu03bx08gz' LOCK TABLE FOR INDEX "IDX_TEST_LHR" IN SHARE MODE NOWAIT END OF STMT PARSE #140719831671200:c=10999,e=29442,p=2,cr=80,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479709686366785
*** 2016-11-21 14:28:06.366 ksqgtl *** TM-00012f3d-00000000 mode=4 flags=0x401 timeout=0 *** ksqgtl: xcb=0x76209db0, ktcdix=2147483647, topxcb=0x76209db0 ktcipt(topxcb)=0x0
2、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 14:28:06.370 ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 *** ksqgtl: xcb=0x76209db0, ktcdix=2147483647, topxcb=0x76209db0 ktcipt(topxcb)=0x0
3、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 14:28:10.938 ksqrcl: DL,12f3d,0 ksqrcl: returns 0
4、釋放T_INDEX_161113表TM鎖 *** 2016-11-21 14:28:10.947 ksqrcl: TM,12f3d,0 ksqrcl: returns 0
|
三、 create index ... online
|
SQL> conn / as sysdba Connected.
SQL> drop index IDX_TEST_LHR;
Index dropped.
SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME) ONLINE;
Index created.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> select value from v$diag_info where name like '%File%';
VALUE -------------------------------------------------------------------------------- /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23672.trc
SQL> col object_name format a15 SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID, 5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID 6 FROM DBA_OBJECTS 7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR'); OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID --------------- ---------- -------------- ------------- ------------- T_INDEX_161113 77629 77629 12f3d 12f3d IDX_TEST_LHR 77887 77887 1303f 1303f
|
trace檔案如下，搜字串“12f3d”：
|
1、獲取T_INDEX_161113表mode=2 TM鎖 *** 2016-11-21 15:14:44.397 ksqrcl: CU,717dfd90,0 ksqrcl: returns 0 ===================== PARSING IN CURSOR #140118279700704 len=46 dep=1 uid=0 oct=26 lid=0 tim=1479712484397029 hv=3395312659 ad='729e1628' sqlid='g95cs0g560r0m' LOCK TABLE "T_INDEX_161113" IN ROW SHARE MODE END OF STMT PARSE #140118279700704:c=1999,e=1893,p=0,cr=19,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479712484397029
*** 2016-11-21 15:14:44.397 ksqgtl *** TM-00012f3d-00000000 mode=2 flags=0x401 timeout=21474836 *** ksqgtl: xcb=0x761eac90, ktcdix=2147483647, topxcb=0x761eac90 ktcipt(topxcb)=0x0 *** 2016-11-21 14:21:45.055
2、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 15:14:44.398 ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 *** ksqgtl: xcb=0x761eac90, ktcdix=2147483647, topxcb=0x761eac90 ktcipt(topxcb)=0x0
3、獲取T_INDEX_161113表mode=4 OD鎖 *** 2016-11-21 15:14:44.454 ksqgtl *** OD-00012f3d-00000000 mode=4 flags=0x10401 timeout=0 *** ksqgtl: xcb=0x761eac90, ktcdix=2147483647, topxcb=0x761eac90 ktcipt(topxcb)=0x0
4、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 15:14:53.066 ksqrcl: DL,12f3d,0 ksqrcl: returns 0
5、釋放T_INDEX_161113表OD、TM鎖 *** 2016-11-21 15:14:55.327 ksqrcl: OD,12f3d,0 ksqrcl: returns 0
*** 2016-11-21 15:14:55.327 ksqrcl: TM,12f3d,0 ksqrcl: returns 0
|
四、 alter index ... rebuild online
|
SQL> conn / as sysdba Connected.
SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> ALTER INDEX IDX_TEST_LHR REBUILD ONLINE;
Index created.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> select value from v$diag_info where name like '%File%';
VALUE -------------------------------------------------------------------------------- /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23792.trc
SQL> col object_name format a15
SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID, 5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID 6 FROM DBA_OBJECTS 7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID --------------- ---------- -------------- ------------- ------------- T_INDEX_161113 77629 77629 12f3d 12f3d IDX_TEST_LHR 77887 77890 1303f 13042
|
trace檔案如下，搜字串“12f3d”：
|
1、獲取T_INDEX_161113表mode=2 TM鎖 PARSING IN CURSOR #139909890400672 len=55 dep=1 uid=0 oct=26 lid=0 tim=1479715165881556 hv=1263262788 ad='7167d4f8' sqlid='6dh4ubt5nrr24' LOCK TABLE FOR INDEX "IDX_TEST_LHR" IN ROW SHARE MODE END OF STMT PARSE #139909890400672:c=1000,e=1599,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479715165881555
*** 2016-11-21 15:59:25.881 ksqgtl *** TM-00012f3d-00000000 mode=2 flags=0x401 timeout=21474836 *** ksqgtl: xcb=0x76228ed0, ktcdix=2147483647, topxcb=0x76228ed0 ktcipt(topxcb)=0x0
2、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 15:59:25.883 ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 *** ksqgtl: xcb=0x76228ed0, ktcdix=2147483647, topxcb=0x76228ed0 ktcipt(topxcb)=0x0
3、獲取T_INDEX_161113表mode=4 OD鎖 *** 2016-11-21 15:59:25.884 ksqgtl *** OD-00012f3d-00000000 mode=4 flags=0x10401 timeout=0 *** ksqgtl: xcb=0x76228ed0, ktcdix=2147483647, topxcb=0x76228ed0 ktcipt(topxcb)=0x0
4、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 15:59:30.334 ksqrcl: DL,12f3d,0 ksqrcl: returns 0
5、釋放T_INDEX_161113表OD、TM鎖 *** 2016-11-21 15:59:30.363 ksqrcl: OD,12f3d,0 ksqrcl: returns 0
*** 2016-11-21 15:59:30.363 ksqrcl: OD,1303f,0 ksqrcl: returns 0
*** 2016-11-21 15:59:30.363 ksqrcl: TM,12f3d,0 ksqrcl: returns 0
|
2.11.4.3 實驗SQL
ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
--CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME) ;
--ALTER INDEX IDX_TEST_LHR REBUILD;
--CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME) ONLINE;
ALTER INDEX IDX_TEST_LHR REBUILD;
ALTER SESSION SET EVENTS '10704 trace name context off';
ALTER SESSION SET EVENTS '10046 trace name context off';
SELECT OBJECT_NAME,
OBJECT_ID,
DATA_OBJECT_ID,
TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,
TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID
FROM DBA_OBJECTS
WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST1_LHR');
select value from v$diag_info where name like '%File%';
執行如下SQL來建立檢視：
CREATE OR REPLACE VIEW VW_SQL_TRACE_NAME_LHR AS
SELECT D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
P.SPID || '.trc' TRACE_FILE_NAME
FROM (SELECT P.SPID
FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
WHERE M.STATISTIC# = '1'
AND S.SID = M.SID
AND P.ADDR = S.PADDR) P,
(SELECT T.INSTANCE
FROM V$THREAD T, V$PARAMETER V
WHERE V.NAME = 'thread'
AND (V.VALUE = '0' OR TO_CHAR(T.THREAD#) = V.VALUE)) I,
(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') D;
建立公共同義詞：
CREATE OR REPLACE PUBLIC SYNONYM SYN_TRACENAME_LHR FOR VW_SQL_TRACE_NAME_LHR;
2.11.5 總結
不帶ONLINE的新建或重建索引的SQL語句獲取的是4級TM鎖，它會阻塞任何DML操作。
在Oracle 10g中，帶ONLINE的新建或重建索引的SQL語句在開始和結束的時候獲取的是4級TM鎖，而在讀取表資料的過程中獲取的是2級TM鎖，所以，在Oracle 10g中，即使加上ONLINE也會阻塞其它會話的DML操作。
在Oracle 11g中，帶ONLINE的新建或重建索引的SQL語句在整個執行過程中獲取的是2級TM鎖，並不會阻塞其它會話的DML操作，但是在建立或重建索引的過程中，其它的會話產生的事務會阻塞索引的建立或重建操作，所以必須結束其它會話的事務才能讓建立或重建索引的操作完成。
在Oracle 11g加上ONLINE的情況下：
(1) 過程中會持有OD(ONLINE DDL)、DL(Direct Loader Index Creation)兩種型別的鎖，在Oracle 10g下只有DL鎖沒有OD鎖
(2) 表級鎖TM的持有模式為row-S (SS)，與row-X (SX)型別的鎖互相相容，因此不會在表級發生阻塞
(3) 阻塞發生在行級鎖申請階段，即請求的share(S)型別的鎖與執行DML的session已經持有的exclusive(X)鎖之間存在不相容的情況；相比非online方式的表級鎖，鎖的粒度上更加細化，副作用更小
(4) 新增以SYS_JOURNAL_為字首的IOT表，記錄與索引建立動作同時進行的其它DML操作修改過的記錄，等到索引建立完成前將IOT表裡的記錄合併至索引中並刪除IOT表
2.12 鎖用到的SQL語句
SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;
SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) ORDER BY a.SID,a.TYPE;
SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (16,27) ORDER BY d.SESSION_ID;
SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (16,27) AND D.name NOT IN ('ALERT_QUE_R','AQ$_ALERT_QT_E','AW_DROP_PROC','DATABASE','DBMS_APPLICATION_INFO','DBMS_BACKUP_RESTORE','DBMS_HA_ALERTS_PRVT','DBMS_OUTPUT','DBMS_PRVT_TRACE','DBMS_RCVMAN','DBMS_SQL','DBMS_STANDARD','DBMS_SYS_SQL','DBMS_TRANSACTION','DBMS_UTILITY','DBMS_XDBZ0','DICTIONARY_OBJ_NAME','DICTIONARY_OBJ_OWNER','PLITBLM','SCHEDULER$_INSTANCE_S','STANDARD','SDO_GEOR_DEF','SQL_TXT');
SELECT A.TADDR,
A.LOCKWAIT,
A.ROW_WAIT_OBJ#,
A.ROW_WAIT_FILE#,
A.ROW_WAIT_BLOCK#,
A.ROW_WAIT_ROW#,
(SELECT D.OWNER || '|' || D.OBJECT_NAME || '|' || D.OBJECT_TYPE
FROM DBA_OBJECTS D
WHERE D.OBJECT_ID = A.ROW_WAIT_OBJ#) OBJECT_NAME,
A.EVENT,
A.P1,
A.P2,
A.P3,
CHR(BITAND(P1, -16777216) / 16777215) ||
CHR(BITAND(P1, 16711680) / 65535) "LOCK",
BITAND(P1, 65535) "MODE",
TRUNC(P2 / POWER(2, 16)) AS XIDUSN,
BITAND(P2, TO_NUMBER('FFFF', 'XXXX')) + 0 AS XIDSLOT,
P3 XIDSQN,
A.SID,
A.BLOCKING_SESSION,
A.SADDR,
DBMS_ROWID.ROWID_CREATE(1, 77669, 8, 2799, 0) REQUEST_ROWID,
(SELECT B.SQL_TEXT
FROM V$SQL B
WHERE B.SQL_ID = NVL(A.SQL_ID, A.PREV_SQL_ID)) SQL_TEXT
FROM V$SESSION A
WHERE A.SID IN (143);
SELECT * FROM v$lock a WHERE a.KADDR='000000007620A7C0';
SELECT * FROM v$transaction a WHERE a.ADDR='000000007620A7C0';
SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE IN ('AE','DL','OD','TO','TX');
SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE
FROM DBA_OBJECTS D
WHERE D.OBJECT_ID IN (77665, 77629);
SELECT D.PARAMETER1,D.PARAMETER2,D.PARAMETER3 FROM V$EVENT_NAME D WHERE D.NAME='enq: TX - row lock contention';
原文地址：建立-重建索引過程中需要獲取的鎖 作者：redhouser
目的：
使用10704事件跟蹤以下四類操作並對比跟蹤結果：
*create index
*alter index rebuild
*create index online
*alter index rebuild online
通過分析跟蹤檔案，可以發現：
a,create index與alter index rebuild所獲取的TM/DL鎖完全一致,步驟如下:
(1),獲取TEST表mode=3 DL lock
(2),獲取TEST表mode=4 TM lock =>會被dml操作阻塞，也不允許併發dml
(3),釋放TEST表DL lock
(4),釋放TEST表mode=4 TM lock
那麼，rebuild與drop/create相比有何好處呢？
(1),rebuild過程通過對已有索引快速掃描，減少了IO和排序資源消耗
(2),rebuild過程中支援select操作
b,create index online與alter index rebuild online所獲取的TM/DL鎖、臨時表完全一致,步驟如下:
(1),獲取TEST表mode=3 DL lock
(2),獲取TEST表mode=2 TM lock
(3),建立SYS_JOURNAL_XXX表
(4),獲取SYS_JOURNAL_XXX表mode=3 DL lock
(5),獲取SYS_JOURNAL_XXX表mode=4 TM lock
(6),釋放SYS_JOURNAL_XXX表mode=3 DL lock
(7),釋放SYS_JOURNAL_XXX表mode=4 TM lock
(8),獲取SYS_JOURNAL_XXX表mode=4 TM lock
(9),轉換TEST表為mode=4 TM lock =>會被dml操作阻塞，也不允許併發dml；轉換會優先於其他dml獲取鎖
(10),轉換TEST表為mode=2 TM lock
(11),轉換TEST表為mode=4 TM lock =>會被dml操作阻塞，也不允許併發dml；轉換會優先於其他dml獲取鎖
(12),轉換SYS_JOURNAL_XXX表為mode=6 TM lock
(13),獲取SYS_JOURNAL_XXX表mode=6 MD lock
(14),釋放TEST表DL lock
(15),釋放SYS_JOURNAL_63264表MD lock
(16),釋放SYS_JOURNAL_63264表TM lock
(17),釋放TEST表TM lock
0,版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
1,create index
==============================================================================
SQL> explain plan for create index idx_test_object_id on test(object_id);
SQL> select * from table(dbms_xplan.display());
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 57422 | 728K|
| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_OBJECT_ID | | |
| 2 | SORT CREATE INDEX | | 57422 | 728K|
| 3 | TABLE ACCESS FULL | TEST | 57422 | 728K|
---------------------------------------------------------------------
Note - estimated index size: 2097K bytes
SQL> alter session set events '10704 trace name context forever,level 10';
Session altered.
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> create index idx_test_object_id on test(object_id);
-->ora10ctg_ora_24615_create_index.trc
SELECT object_name, object_id, data_object_id
FROM dba_objects
WHERE wner = USER
AND object_name IN ('IDX_TEST_OBJECT_ID', 'TEST')
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- ---------- --------------
IDX_TEST_OBJECT_ID 63259 63259-->f71b
TEST 63258 63258-->f71a
(1),獲取TEST表mode=3 DL lock
ksqgtl *** DL-0000f71a-00000000 mode=3 flags=0x11 timeout=0 *** -->63258,TEST table
ksqgtl *** DL-0000f71a-00000000 mode=3 flags=0x11 timeout=0 *** -->63258,TEST table
create index idx_test_object_id on test(object_id)
END OF STMT
(2),獲取TEST表mode=4 TM lock
ksqgtl *** TM-0000f71a-00000000 mode=4 flags=0x401 timeout=0 ***
*** 2014-01-10 09:46:44.395
ksqgtl *** TX-0004002f-00001c81 mode=6 flags=0x401 timeout=0 ***
INSERT INTO obj$
(owner#, --0
NAME, --IDX_TEST_OBJECT_ID
namespace, --4
obj#, --63259
type#, --1
ctime, --"1/10/2014 9:46:44"
mtime, --"1/10/2014 9:46:44"
stime, --"1/10/2014 9:46:44"
status, --1
remoteowner,
linkname,
subname,
dataobj#, --63259
flags, --0
oid$,
spare1, --6
spare2) --65535
VALUES(...);
ksqgtl *** TM-00000012-00000000 mode=3 flags=0x401 timeout=21474836 *** -->OBJ$ table
ksqgtl *** MR-00000001-00000002 mode=4 flags=0x10 timeout=0 *** -->Media Recovery,file_id,type
ksqrcl: MR,1,2
--讀取TEST表
WAIT #1: nam='db file scattered read' ela= 212 file#=1 block#=62882 blocks=7 obj#=63258 tim=1356756254308970 -->TEST table
...
WAIT #1: nam='db file scattered read' ela= 1038 file#=1 block#=65081 blocks=16 obj#=63258 tim=1356756254386287
ksqgtl *** TT-00000000-00000000 mode=4 flags=0x11 timeout=21474836 *** -->Serializes DDL operations on tablespaces,Tablespace_id,operation
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** TX-0004000d-00001c72 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** MR-00000002-00000002 mode=4 flags=0x10 timeout=0 *** -->undo file
ksqrcl: MR,2,2
ksqgtl *** TS-00000000-0040fcd9 mode=6 flags=0x1 timeout=21474836 ***
INSERT INTO seg$
(file#, --1
block#, --64729
type#, --3
ts#, --0
blocks, --8
extents, --1
minexts, --1
maxexts, --2147483645
extsize, --128
extpct, --0
user#, --0
iniexts, --8
lists, --0
groups, --0
cachehint, --0
bitmapranges, --63259
hwmincr, --131073
spare1,
scanhint) --0
VALUES(...);
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 *** -->SEG$ table
ksqrcl: TX,4000d,1c72
ksqrcl: TM,e,0
ksqrcl: TT,0,10
ksqrcl: TT,0,0
ksqgtl *** HW-00000000-0040fcd9 mode=6 flags=0x11 timeout=21474836 *** -->Segment High Water Mark,Lock used to broker the high water mark during parallel inserts,tablespace #,block
ksqrcl: HW,0,40fcd9
--分配空間,不斷追加,共15extents
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** HW-00000000-0040fcd9 mode=6 flags=0x411 timeout=21474836 ***
ksqgtl *** TX-00040006-00001c83 mode=6 flags=0x401 timeout=0 ***
UPDATE seg$
SET type# = :4, --3
blocks = :5, --16
extents = :6, --2
minexts = :7, --1
maxexts = :8, --2147483645
extsize = :9, --128
extpct = :10, --0
user# = :11, --0
iniexts = :12, --8
lists = decode(:13, 65535, NULL, :13), --0
groups = decode(:14, 65535, NULL, :14),
cachehint = :15, --0
hwmincr = :16, --63259
spare1 = decode(:17, 0, NULL, :17), 131073
scanhint = :18 --0
WHERE ts# = :1 --0
AND file# = :2 --1
AND block# = :3 --64729
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,40006,1c83
ksqrcl: TM,e,0
ksqrcl: HW,0,40fcd9
ksqrcl: TT,0,10
--最後一個extent
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** HW-00000000-0040fcd9 mode=6 flags=0x411 timeout=21474836 ***
ksqgtl *** TX-00040025-00001c7f mode=6 flags=0x401 timeout=0 ***
UPDATE seg$
SET type# = :4, --3
blocks = :5, --120
extents = :6, --15
minexts = :7, --1
maxexts = :8, --2147483645
extsize = :9, --128
extpct = :10, --0
user# = :11, --0
iniexts = :12, --8
lists = decode(:13, 65535, NULL, :13), --0
groups = decode(:14, 65535, NULL, :14),
cachehint = :15, --0
hwmincr = :16, --63259
spare1 = decode(:17, 0, NULL, :17), 131073
scanhint = :18 --0
WHERE ts# = :1 --0
AND file# = :2 --1
AND block# = :3 --64729
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,40025,1c7f
ksqrcl: TM,e,0
ksqrcl: HW,0,40fcd9
ksqrcl: TT,0,10
--寫入索引,dba_extents顯示給dba屬於IDX_TEST_OBJECT_ID,但object_id(63258)表示TEST table？
WAIT #1: nam='direct path write' ela= 1 file number=1 first dba=65252 block cnt=7 obj#=63258 tim=1356756254581406
WAIT #1: nam='direct path write' ela= 51 file number=1 first dba=65252 block cnt=7 obj#=63258 tim=1356756254581455
WAIT #1: nam='direct path write' ela= 1 file number=1 first dba=65259 block cnt=1 obj#=63258 tim=1356756254581490
WAIT #1: nam='direct path write' ela= 31 file number=1 first dba=65259 block cnt=1 obj#=63258 tim=1356756254581520
--再次更新seg$
PARSING IN CURSOR #3 len=296 dep=1 uid=0 ct=6 lid=0 tim=1356756254581776 hv=2379717279 ad='5b70e2cc'
UPDATE seg$
SET type# = :4, --3
blocks = :5, --120
extents = :6, --15
minexts = :7, --1
maxexts = :8, --2147483645
extsize = :9, --128
extpct = :10, --0
user# = :11, --0
iniexts = :12, --8
lists = decode(:13, 65535, NULL, :13), --0
groups = decode(:14, 65535, NULL, :14),
cachehint = :15, --0
hwmincr = :16, --63259
spare1 = decode(:17, 0, NULL, :17), 131073
scanhint = :18 --0
WHERE ts# = :1 --0
AND file# = :2 --1
AND block# = :3 --64729
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-0004002b-00001c81 mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,4002b,1c81
ksqrcl: TM,e,0
(3),釋放TEST表DL lock
ksqrcl: DL,f71a,0
ksqrcl: DL,f71a,0
XCTEND rlbk=0, rd_only=0
INSERT INTO icol$
(obj#, --63259
bo#, --63258
intcol#, --4
pos#, --1
segcol#,
segcollength,
offset,
col#, --4
spare1, --0
spare2) --0
VALUES
(:1, :2, :3, :4, 0, 0, 0, :5, :6, :7)
ksqgtl *** TM-00000014-00000000 mode=3 flags=0x401 timeout=21474836 ***
INSERT INTO ind$
(bo#, --63258
obj#, --63259
ts#, --0
file#, --1
block#, --64729
intcols, --1
type#, --1
flags, --2
property, --0
pctfree$, --10
initrans, --2
maxtrans, --255
blevel, --1
leafcnt, --113
distkey, --51283
lblkkey, --1
dblkkey, --1
clufac, --1146
cols, --1
analyzetime, --1/10/2014 9:46:44
samplesize, --51283
dataobj#, --63259
degree, --1
instances,
rowcnt, --1
pctthres$,
indmethod#, --51283
trunccnt,
spare1,
spare4, --0
spare2,
spare6) --1/10/2014 9:46:44
VALUES(...);
ksqgtl *** TM-00000013-00000000 mode=3 flags=0x401 timeout=21474836 ***
UPDATE seg$
SET type# = :4, --6,更改型別
blocks = :5, --120
extents = :6, --15
minexts = :7, --1
maxexts = :8, --2147483645
extsize = :9, --128
extpct = :10, --0
user# = :11, --0
iniexts = :12, --8
lists = decode(:13, 65535, NULL, :13), --0
groups = decode(:14, 65535, NULL, :14),
cachehint = :15, --0
hwmincr = :16, --63259
spare1 = decode(:17, 0, NULL, :17), --131073
scanhint = :18 --0
WHERE ts# = :1 --0
AND file# = :2 --1
AND block# = :3 --64729
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
UPDATE obj$
SET obj# = :6,--63258
type# = :7,--2
ctime = :8,--"1/10/2014 9:45:43"
mtime = :9,--"1/10/2014 9:46:44"
stime = :10,--"1/10/2014 9:45:43"
status = :11,--1
dataobj# = :13,--63258
flags = :14,--0
oid$ = :15,--
spare1 = :16,--6
spare2 = :17--1
WHERE owner# = :1 --0
AND NAME = :2 --TEST
AND namespace = :3 --1
AND (remoteowner = :4 OR remoteowner IS NULL AND :4 IS NULL)
AND (linkname = :5 OR linkname IS NULL AND :5 IS NULL)
AND (subname = :12 OR subname IS NULL AND :12 IS NULL)
ksqrcl: TX,4002f,1c81
ksqrcl: TM,e,0
ksqrcl: TM,13,0
ksqrcl: TM,14,0
ksqrcl: TM,12,0
(4),釋放TEST表mode=4 TM lock
ksqrcl: TM,f71a,0
ksqrcl: TS,0,40fcd9
2,alter index rebuild
==============================================================================
SQL> explain plan for alter index idx_test_object_id rebuild ;
SQL> select * from table(dbms_xplan.display());
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 57422 | 728K|
| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_OBJECT_ID | | |
| 2 | SORT CREATE INDEX | | 57422 | 728K|
| 3 | INDEX FAST FULL SCAN| IDX_TEST_OBJECT_ID | | |
---------------------------------------------------------------------
SQL> alter session set events '10704 trace name context forever,level 10';
Session altered.
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
alter index idx_test_object_id rebuild;
-->ora10ctg_ora_27790_rebuild.trc
SELECT object_name, object_id, data_object_id
FROM dba_objects
WHERE wner = USER
AND object_name IN ('IDX_TEST_OBJECT_ID', 'TEST')
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- ---------- --------------
IDX_TEST_OBJECT_ID 63259 63260-->f71b,f71c
TEST 63258 63258-->f71a
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqrcl: TT,0,10
(1),獲取TEST表mode=3 DL LOCK
ksqgtl *** DL-0000f71a-00000000 mode=3 flags=0x11 timeout=0 ***
ksqgtl *** DL-0000f71a-00000000 mode=3 flags=0x11 timeout=0 ***
ksqcmi: DL,f71a,0 mode=3 timeout=0
ksqcmi: returns 0
alter index idx_test_object_id rebuild
(2),獲取TEST表mode=4 TM LOCK
ksqgtl *** TM-0000f71a-00000000 mode=4 flags=0x401 timeout=0 ***
ksqgtl *** TX-000a001c-00001ca7 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** MR-00000001-00000002 mode=4 flags=0x10 timeout=0 ***
ksqrcl: MR,1,2
ksqgtl *** TT-00000000-00000000 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** TX-000a000f-00001cab mode=6 flags=0x401 timeout=0 ***
ksqgtl *** MR-00000002-00000002 mode=4 flags=0x10 timeout=0 ***
ksqrcl: MR,2,2
ksqgtl *** TS-00000000-0040fef1 mode=6 flags=0x1 timeout=21474836 ***
insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,bitmapranges,hwmincr, spare1, scanhint) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,:16,DECODE(:17,0,NULL,:17),:18)
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-000a0027-00001caa mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,a0027,1caa
ksqrcl: TX,a000f,1cab
ksqrcl: TM,e,0
ksqrcl: TT,0,10
ksqrcl: TT,0,0
ksqgtl *** HW-00000000-0040fef1 mode=6 flags=0x11 timeout=21474836 ***
ksqrcl: HW,0,40fef1
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** HW-00000000-0040fef1 mode=6 flags=0x411 timeout=21474836 ***
ksqgtl *** TX-000a0019-00001ca9 mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,a0019,1ca9
ksqgtl *** TT-00000001-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** HW-00000001-00800099 mode=6 flags=0x11 timeout=21474836 ***
ksqrcl: HW,1,800099
ksqrcl: TT,1,10
ksqgtl *** TX-000a0019-00001ca9 mode=6 flags=0x401 timeout=0 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,a0019,1ca9
ksqrcl: TM,e,0
ksqrcl: HW,0,40fef1
ksqrcl: TT,0,10
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** HW-00000000-0040fef1 mode=6 flags=0x411 timeout=21474836 ***
ksqgtl *** TX-000a002e-00001ca9 mode=6 flags=0x401 timeout=0 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,a002e,1ca9
ksqrcl: TM,e,0
ksqrcl: HW,0,40fef1
ksqrcl: TT,0,10
--重複插入
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** HW-00000000-0040fef1 mode=6 flags=0x411 timeout=21474836 ***
ksqgtl *** TX-000a0010-00001cab mode=6 flags=0x401 timeout=0 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,a0010,1cab
ksqrcl: TM,e,0
ksqrcl: HW,0,40fef1
ksqrcl: TT,0,10
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** HW-00000000-0040fef1 mode=6 flags=0x411 timeout=21474836 ***
ksqgtl *** TX-000a0028-00001cab mode=6 flags=0x401 timeout=0 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,a0028,1cab
ksqrcl: TM,e,0
ksqrcl: HW,0,40fef1
ksqrcl: TT,0,10
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-000a0024-00001cab mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,a0024,1cab
ksqrcl: TM,e,0
insert into sys.wri$_optstat_ind_history(obj#,rowcnt,leafcnt,distkey, lblkkey, dblkkey,clufac,blevel,analyzetime,samplesize,guessq,cachedblk, cachehit,logicalread, savtime,flags) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16)
ksqgtl *** TM-00001064-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-000a0000-00001cac mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,a0000,1cac
ksqrcl: TM,1064,0
delete from sys.cache_stats_1$ where dataobj# = :1
ksqgtl *** TM-00000317-00000000 mode=3 flags=0x401 timeout=21474836 ***
(3),釋放TEST表DL LOCK
ksqrcl: DL,f71a,0
ksqrcl: DL,f71a,0
delete from ind_stats$ where obj#=:1
ksqgtl *** TM-000002c6-00000000 mode=3 flags=0x401 timeout=21474836 ***
update ind$ set ts#=:2,file#=:3,block#=:4,intcols=:5,type#=:6,flags=:7,property=:8,pctfree$=:9,initrans=:10,maxtrans=:11,blevel=:12,leafcnt=:13,distkey=:14,lblkkey=:15,dblkkey=:16,clufac=:17,cols=:18,analyzetime=:19,samplesize=:20,dataobj#=:21,degree=decode(:22,1,null,:22),instances=decode(:23,1,null,:23),rowcnt=:24,pctthres$=:31*256+:25, indmethod#=:26, trunccnt=:27,spare1=:28,spare4=:29,spare2=:30,spare6=:32where obj#=:1
ksqgtl *** TM-00000013-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from ind_online$ where obj#= :1
ksqgtl *** TM-000002db-00000000 mode=3 flags=0x401 timeout=21474836 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
ksqgtl *** TM-00000012-00000000 mode=3 flags=0x401 timeout=21474836 ***
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
ksqrcl: TX,a001c,1ca7
ksqrcl: TM,12,0
ksqrcl: TM,e,0
ksqrcl: TM,2db,0
ksqrcl: TM,13,0
ksqrcl: TM,2c6,0
ksqrcl: TM,317,0
(4),釋放TEST表mode=4 TM LOCK
ksqrcl: TM,f71a,0
ksqrcl: TS,0,40fef1
ksqgtl *** TS-00000000-0040fcd9 mode=6 flags=0x11 timeout=0 ***
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** RO-00010011-00000001 mode=6 flags=0x11 timeout=21474836 ***
ksqgtl *** RO-00010011-00000002 mode=1 flags=0x11 timeout=21474836 ***
ksqcnv: RO-00010011,00000001 mode=5 timeout=21474836
ksqcmi: RO,10011,1 mode=5 timeout=21474836
ksqcnv: RO-00010011,00000001 mode=6 timeout=21474836
ksqcmi: RO,10011,1 mode=6 timeout=21474836
ksqrcl: RO,10011,1
ksqcnv: RO-00010011,00000002 mode=1 timeout=21474836
ksqcmi: RO,10011,2 mode=1 timeout=21474836
ksqrcl: RO,10011,2
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-0002001a-00001c18 mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,2001a,1c18
ksqrcl: TM,e,0
ksqgtl *** TX-00050028-00001c74 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** CI-00000001-00000005 mode=6 flags=0x10 timeout=21474836 ***
ksqrcl: CI,1,5
delete from seg$ where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,50028,1c74
ksqrcl: TM,e,0
ksqrcl: TS,0,40fcd9
ksqrcl: TT,0,10
3,create index online
==============================================================================
SQL> explain plan for create index idx_test_owner on test(owner) online;
SQL> select * from table(dbms_xplan.display());
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-----------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 57422 | 953K|
| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_OWNER | | |
| 2 | SORT CREATE INDEX | | 57422 | 953K|
| 3 | TABLE ACCESS FULL | TEST | 57422 | 953K|
-----------------------------------------------------------------
SQL> alter session set events '10704 trace name context forever,level 10';
Session altered.
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
create index idx_test_owner on test(owner) online;
-->ora10ctg_ora_27869_create_index_online.trc
SELECT object_name, object_id, data_object_id
FROM dba_objects
WHERE wner = USER
AND object_name IN ('IDX_TEST_OWNER', 'TEST')
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- ---------- --------------
IDX_TEST_OWNER 63264 63264 -->f720
TEST 63258 63258 -->f71a
SYS_JOURNAL_63264 -->f721
SYS_IOT_TOP_63265 -->f722
--------------
create index idx_test_owner on test(owner) online
(1),獲取TEST表mode=3 DL lock
ksqgtl *** DL-0000f71a-00000000 mode=3 flags=0x11 timeout=0 ***
ksqgtl *** DL-0000f71a-00000000 mode=3 flags=0x11 timeout=0 ***
ksqcmi: DL,f71a,0 mode=3 timeout=0
(2),獲取TEST表mode=2 TM lock
ksqgtl *** TM-0000f71a-00000000 mode=2 flags=0x401 timeout=21474836 *** -->TEST table
ksqgtl *** TX-0001001f-000018c8 mode=6 flags=0x401 timeout=0 ***
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, :17)
-->f720,IDX_TEST_OWNER
ksqgtl *** TX-00010005-000018c9 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** MR-00000002-00000002 mode=4 flags=0x10 timeout=0 ***
ksqrcl: MR,2,2
ksqgtl *** TT-00000000-00000000 mode=4 flags=0x411 timeout=21474836 ***
(3),建立SYS_JOURNAL_63264表
create table "SYS"."SYS_JOURNAL_63264" (C0 VARCHAR2(30), opcode char(1), partno number, rid rowid, primary key( C0 , rid )) organization index TABLESPACE "SYSTEM"
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, :17)
-->f721,SYS_JOURNAL_63264
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
update con$ set con#=:3 where owner#=:1 and name=:2
ksqgtl *** TM-0000001c-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-00010019-000018c9 mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,10019,18c9
ksqrcl: TM,1c,0
(4),獲取SYS_JOURNAL_63264表mode=3 DL lock
ksqgtl *** DL-0000f721-00000000 mode=3 flags=0x11 timeout=0 ***
ksqgtl *** DL-0000f721-00000000 mode=3 flags=0x11 timeout=0 ***
ksqcmi: DL,f721,0 mode=3 timeout=0
ksqcmi: returns 0
CREATE UNIQUE INDEX "SYS"."SYS_IOT_TOP_63265" on "SYS"."SYS_JOURNAL_63264"("C0","RID") INDEX ONLY TOPLEVEL TABLESPACE "SYSTEM" NOPARALLEL
(5),獲取SYS_JOURNAL_63264表mode=4 TM lock
ksqgtl *** TM-0000f721-00000000 mode=4 flags=0x401 timeout=0 ***
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, :17)
-->f722,SYS_IOT_TOP_63265
ksqgtl *** TT-00000000-00000000 mode=4 flags=0x11 timeout=21474836 ***
ksqcmi: TT,0,0 mode=4 timeout=21474836
ksqcmi: returns 0
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** TX-00010015-000018c8 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** TS-00000000-0040fcd9 mode=6 flags=0x1 timeout=21474836 ***
ksqgtl *** MR-00000001-00000002 mode=4 flags=0x10 timeout=0 ***
ksqrcl: MR,1,2
insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,bitmapranges,hwmincr, spare1, scanhint) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,:16,DECODE(:17,0,NULL,:17),:18)
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,10015,18c8
ksqrcl: TM,e,0
ksqrcl: TT,0,10
ksqrcl: TT,0,0
ksqgtl *** HW-00000000-0040fcd9 mode=6 flags=0x11 timeout=21474836 ***
ksqrcl: HW,0,40fcd9
(6),釋放SYS_JOURNAL_63264表mode=3 DL lock
ksqrcl: DL,f721,0
ksqrcl: DL,f721,0
insert into icol$(obj#,bo#,intcol#,pos#,segcol#,segcollength,offset,col#,spare1,spare2)values(:1,:2,:3,:4,0,0,0,:5,:6,:7)
ksqgtl *** TM-00000014-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-0001000a-000018c8 mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,1000a,18c8
ksqgtl *** TX-00010004-000018ca mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,10004,18ca
insert into ind$(bo#,obj#,ts#,file#,block#,intcols,type#,flags,property,pctfree$,initrans,maxtrans,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,cols,analyzetime,samplesize,dataobj#,degree,instances,rowcnt,pctthres$,indmethod#,trunccnt,spare1,spare4,spare2,spare6)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,decode(:23,1,null,:23),decode(:24,1,null,:24),:25, :32*256+:26,:27,:28,:29,:30,:31,:33)
ksqgtl *** TM-00000013-00000000 mode=3 flags=0x401 timeout=21474836 ***
insert into tab$(obj#,ts#,file#,block#,bobj#,tab#,intcols,kernelcols,clucols,audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime,samplesize,cols,property,degree,instances,dataobj#,avgspc_flb,flbcnt,trigflag,spare1,spare6)values(:1,:2,:3,:4,decode(:5,0,null,:5),decode(:6,0,null,:6),:7,:8,decode(:9,0,null,:9),:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,decode(:26,1,null,:26),decode(:27,1,null,:27),:28,:29,:30,:31,:32,:33)
ksqgtl *** TM-00000004-00000000 mode=3 flags=0x401 timeout=21474836 ***
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
ksqgtl *** TM-00000015-00000000 mode=3 flags=0x401 timeout=21474836 ***
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into ccol$(con#,obj#,intcol#,pos#,col#,spare1) values(:1,:2,:3,decode(:4,0,null,:4),:5, :6)
ksqgtl *** TM-00000020-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-00010029-000018ca mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,10029,18ca
ksqgtl *** TX-00010025-000018ca mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,10025,18ca
insert into cdef$(obj#,con#,type#,intcols,condlength,condition,robj#,rcon#,match#,refact,enabled,cols,defer,mtime,spare1)values(:1,:2,:3,decode(:4,0,null,:4),decode(:5,0,null,:5),:6,decode(:7,0,null,:7),decode(:8,0,null,:8),decode(:9,0,null,:9),decode(:10,0,null,:10), decode(:11,0,null,:11),:12, decode(:13,0,null,:13),:14,:15)
ksqgtl *** TM-0000001f-00000000 mode=3 flags=0x401 timeout=21474836 ***
insert into icol$(obj#,bo#,intcol#,pos#,segcol#,segcollength,offset,col#,spare1,spare2)values(:1,:2,:3,:4,0,0,0,:5,:6,:7)
insert into ind$(bo#,obj#,ts#,file#,block#,intcols,type#,flags,property,pctfree$,initrans,maxtrans,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,cols,analyzetime,samplesize,dataobj#,degree,instances,rowcnt,pctthres$,indmethod#,trunccnt,spare1,spare4,spare2,spare6)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,decode(:23,1,null,:23),decode(:24,1,null,:24),:25, :32*256+:26,:27,:28,:29,:30,:31,:33)
insert into ind_online$ (obj#,type#,flags) values(:1,:2,:3)
ksqgtl *** TM-000002db-00000000 mode=3 flags=0x401 timeout=21474836 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
insert into con$(owner#,name,con#)values(:1,:2,:3)
ksqgtl *** TM-0000001c-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,10005,18c9
ksqrcl: TM,1c,0
ksqrcl: TM,e,0
ksqrcl: TM,2db,0
ksqrcl: TM,1f,0
ksqrcl: TM,20,0
ksqrcl: TM,15,0
ksqrcl: TM,4,0
ksqrcl: TM,13,0
ksqrcl: TM,14,0
(7),釋放SYS_JOURNAL_63264表mode=4 TM lock
ksqrcl: TM,f721,0
ksqrcl: TT,0,0
ksqrcl: TM,12,0
ksqrcl: TS,0,40fcd9
(8),獲取SYS_JOURNAL_63264表mode=4 TM lock
ksqgtl *** TM-0000f721-00000000 mode=4 flags=0x401 timeout=21474836 ***
(9),轉換TEST表為mode=4 TM lock
ksqcnv: TM-0000f71a,00000000 mode=4 timeout=21474836 -->convert lock
ksqcmi: TM,f71a,0 mode=4 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
(10),轉換TEST表為mode=2 TM lock
ksqcnv: TM-0000f71a,00000000 mode=2 timeout=21474836
ksqcmi: TM,f71a,0 mode=2 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
ksqgtl *** TT-00000000-00000000 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** TX-00010008-000018c9 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** TS-00000000-0040fef1 mode=6 flags=0x1 timeout=21474836 ***
insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,bitmapranges,hwmincr, spare1, scanhint) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,:16,DECODE(:17,0,NULL,:17),:18)
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,10008,18c9
ksqrcl: TM,e,0
ksqrcl: TT,0,10
ksqrcl: TT,0,0
ksqgtl *** HW-00000000-0040fef1 mode=6 flags=0x11 timeout=21474836 ***
ksqrcl: HW,0,40fef1
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** HW-00000000-0040fef1 mode=6 flags=0x411 timeout=21474836 ***
ksqgtl *** TX-00010023-000018c9 mode=6 flags=0x401 timeout=0 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,10023,18c9
ksqrcl: TM,e,0
ksqrcl: HW,0,40fef1
ksqrcl: TT,0,10
(11),轉換TEST表為mode=4 TM lock
ksqcnv: TM-0000f71a,00000000 mode=4 timeout=21474836
ksqcmi: TM,f71a,0 mode=4 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-0001001d-000018ca mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,1001d,18ca
ksqrcl: TM,e,0
insert into sys.wri$_optstat_ind_history(obj#,rowcnt,leafcnt,distkey, lblkkey, dblkkey,clufac,blevel,analyzetime,samplesize,guessq,cachedblk, cachehit,logicalread, savtime,flags) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16)
ksqgtl *** TM-00001064-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-00010026-000018c8 mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,10026,18c8
ksqrcl: TM,1064,0
drop table "SYS"."SYS_JOURNAL_63264" purge
xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name);
SELECT USER_ID FROM ALL_USERS WHERE USERNAME = :B1
ksqgtl *** SE-00000093-0000051f mode=4 flags=0x0 timeout=21474836 ***
ksqrcl: SE,93,51f
(12),轉換SYS_JOURNAL_63264表為mode=6 TM lock
ksqcnv: TM-0000f721,00000000 mode=6 timeout=0
ksqcmi: TM,f721,0 mode=6 timeout=0
ksqcmi: returns 0
ksqcnv: RETURNS 0
(13),獲取SYS_JOURNAL_63264表mode=6 MD lock
ksqgtl *** MD-0000f721-00000000 mode=6 flags=0x401 timeout=5 ***
delete from object_usage where obj# in (select a.obj# from object_usage a, ind$ b where a.obj# = b.obj# and b.bo# = :1)
ksqgtl *** TM-00000239-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from sys.cache_stats_1$ where dataobj# = :1
ksqgtl *** TM-00000317-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete com$ where obj#=:1
ksqgtl *** TM-00000061-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from hist_head$ where obj# = :1
ksqgtl *** TM-000000ff-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from dependency$ where d_obj#=:1
delete from source$ where obj#=:1
DELETE FROM sys.sumdelta$ sd WHERE sd.tableobj# = :1
ksqgtl *** TM-0000009d-00000000 mode=3 flags=0x401 timeout=21474836 ***
DELETE FROM sys.sumpartlog$ sp WHERE sp.bo# = :1
ksqgtl *** TM-0000009a-00000000 mode=3 flags=0x401 timeout=21474836 ***
DELETE FROM sys.snap_loadertime$ sd WHERE sd.tableobj# = :1
ksqgtl *** TM-000000a1-00000000 mode=3 flags=0x401 timeout=21474836 ***
(14),釋放TEST表DL lock
ksqrcl: DL,f71a,0
ksqrcl: DL,f71a,0
delete from idl_ub1$ where obj#=:1 and part=:2
ksqgtl *** TM-00000049-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from idl_char$ where obj#=:1 and part=:2
ksqgtl *** TM-0000004a-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from idl_ub2$ where obj#=:1 and part=:2
ksqgtl *** TM-0000004b-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from idl_sb4$ where obj#=:1 and part=:2
ksqgtl *** TM-0000004c-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
ksqgtl *** TM-000002d7-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from objauth$ where obj#=:1
ksqgtl *** TM-00000039-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from col$ where obj#=:1
ksqgtl *** TM-00000015-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from icol$ where bo#=:1
ksqgtl *** TM-00000014-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from icoldep$ where obj# in (select obj# from ind$ where bo#=:1)
ksqgtl *** TM-00000174-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from jijoin$ where obj# in ( select obj# from jijoin$ where tab1obj# = :1 or tab2obj# = :1)
ksqgtl *** TM-0000021c-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from jirefreshsql$ where iobj# in ( select iobj# from jirefreshsql$ where tobj# = :1)
ksqgtl *** TM-00000220-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from ccol$ where obj#=:1
ksqgtl *** TM-00000020-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from ind$ where bo#=:1
ksqgtl *** TM-00000013-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from cdef$ where obj#=:1
ksqgtl *** TM-0000001f-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from tab$ where obj#=:1
ksqgtl *** TM-00000004-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from col$ where obj#=:1
delete coltype$ where obj#=:1
ksqgtl *** TM-000000a8-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from subcoltype$ where obj#=:1
ksqgtl *** TM-000000ab-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete ntab$ where obj#=:1
ksqgtl *** TM-000000c6-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete lob$ where obj#=:1
ksqgtl *** TM-00000097-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete refcon$ where obj#=:1
ksqgtl *** TM-000000ca-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from opqtype$ where obj#=:1
ksqgtl *** TM-000000cd-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from cdef$ where obj#=:1
delete from objauth$ where obj#=:1
delete from ind_stats$ where obj#=:1
ksqgtl *** TM-000002c6-00000000 mode=3 flags=0x401 timeout=21474836 ***
update ind$ set ts#=:2,file#=:3,block#=:4,intcols=:5,type#=:6,flags=:7,property=:8,pctfree$=:9,initrans=:10,maxtrans=:11,blevel=:12,leafcnt=:13,distkey=:14,lblkkey=:15,dblkkey=:16,clufac=:17,cols=:18,analyzetime=:19,samplesize=:20,dataobj#=:21,degree=decode(:22,1,null,:22),instances=decode(:23,1,null,:23),rowcnt=:24,pctthres$=:31*256+:25, indmethod#=:26, trunccnt=:27,spare1=:28,spare4=:29,spare2=:30,spare6=:32where obj#=:1
delete from ind_online$ where obj#= :1
ksqgtl *** TM-000002db-00000000 mode=3 flags=0x401 timeout=21474836 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
delete from obj$ where obj# = :1
ksqgtl *** TM-00000012-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from con$ where owner#=:1 and name=:2
ksqgtl *** TM-0000001c-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from obj$ where obj# = :1
ksqgtl *** TT-00000001-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** HW-00000001-00800009 mode=6 flags=0x11 timeout=21474836 ***
ksqrcl: HW,1,800009
ksqrcl: TT,1,10
ksqrcl: TX,1001f,18c8
ksqrcl: TM,1c,0
ksqrcl: TM,12,0
ksqrcl: TM,e,0
ksqrcl: TM,2db,0
ksqrcl: TM,2c6,0
ksqrcl: TM,cd,0
ksqrcl: TM,ca,0
ksqrcl: TM,97,0
ksqrcl: TM,c6,0
ksqrcl: TM,ab,0
ksqrcl: TM,a8,0
ksqrcl: TM,4,0
ksqrcl: TM,1f,0
ksqrcl: TM,13,0
ksqrcl: TM,20,0
ksqrcl: TM,220,0
ksqrcl: TM,21c,0
ksqrcl: TM,174,0
ksqrcl: TM,14,0
ksqrcl: TM,15,0
ksqrcl: TM,39,0
ksqrcl: TM,2d7,0
ksqrcl: TM,4c,0
ksqrcl: TM,4b,0
ksqrcl: TM,4a,0
ksqrcl: TM,49,0
ksqrcl: TM,a1,0
ksqrcl: TM,9a,0
ksqrcl: TM,9d,0
ksqrcl: TM,ff,0
ksqrcl: TM,61,0
ksqrcl: TM,317,0
ksqrcl: TM,239,0
(15),釋放SYS_JOURNAL_63264表MD lock
ksqrcl: MD,f721,0
(16),釋放SYS_JOURNAL_63264表TM lock
ksqrcl: TM,f721,0
(17),釋放TEST表TM lock
ksqrcl: TM,f71a,0
ksqrcl: TS,0,40fef1
ksqgtl *** TS-00000000-0040fcd9 mode=6 flags=0x11 timeout=0 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl *** RO-00010012-00000001 mode=6 flags=0x11 timeout=21474836 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl *** RO-00010012-00000002 mode=1 flags=0x11 timeout=21474836 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqcnv: RO-00010012,00000001 mode=5 timeout=21474836
ksqcmi: RO,10012,1 mode=5 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
ksqcnv: RO-00010012,00000001 mode=6 timeout=21474836
ksqcmi: RO,10012,1 mode=6 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
ksqrcl: RO,10012,1
ksqcnv: RO-00010012,00000002 mode=1 timeout=21474836
ksqcmi: RO,10012,2 mode=1 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
ksqrcl: RO,10012,2
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-00020019-00001c18 mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,20019,1c18
ksqrcl: TM,e,0
ksqgtl *** TX-0008001a-00001d65 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** CI-00000001-00000005 mode=6 flags=0x10 timeout=21474836 ***
ksqrcl: CI,1,5
delete from seg$ where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,8001a,1d65
ksqrcl: TM,e,0
ksqrcl: TS,0,40fcd9
ksqrcl: TT,0,10
4,alter index rebuild online
==============================================================================
SQL> explain plan for alter index idx_test_object_id rebuild online;
SQL> select * from table(dbms_xplan.display());
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 57422 | 728K|
| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_OBJECT_ID | | |
| 2 | SORT CREATE INDEX | | 57422 | 728K|
| 3 | TABLE ACCESS FULL | TEST | 57422 | 728K|
---------------------------------------------------------------------
SQL> alter session set events '10704 trace name context forever,level 10';
Session altered.
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
alter index idx_test_object_id rebuild online;
-->ora10ctg_ora_27861_rebuild_index_online.trc
SELECT object_name, object_id, data_object_id
FROM dba_objects
WHERE wner = USER
AND object_name IN ('IDX_TEST_OBJECT_ID', 'TEST')
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- ---------- --------------
IDX_TEST_OBJECT_ID 63259 63261-->f71b,f71d
TEST 63258 63258-->f71a
alter session set events '10046 trace name context forever,level 12'
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqrcl: TT,0,10
(1),獲取TEST表mode=3 DL LOCK
ksqgtl *** DL-0000f71a-00000000 mode=3 flags=0x11 timeout=0 ***
ksqgtl *** DL-0000f71a-00000000 mode=3 flags=0x11 timeout=0 ***
ksqcmi: DL,f71a,0 mode=3 timeout=0
alter index idx_test_object_id rebuild online
(2),獲取TEST表mode=2 TM LOCK
ksqgtl *** TM-0000f71a-00000000 mode=2 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-0006000f-00001960 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** TT-00000000-00000000 mode=4 flags=0x411 timeout=21474836 ***
(3),建立SYS_JOURNAL_63259表
create table "SYS"."SYS_JOURNAL_63259" (C0 NUMBER, opcode char(1), partno number, rid rowid, primary key( C0 , rid )) organization index TABLESPACE "SYSTEM"
ksqgtl *** TX-0006002f-0000195f mode=6 flags=0x401 timeout=0 ***
ksqgtl *** MR-00000002-00000002 mode=4 flags=0x10 timeout=0 ***
ksqrcl: MR,2,2
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, :17)
ksqgtl *** TM-00000012-00000000 mode=3 flags=0x401 timeout=21474836 ***
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
update con$ set con#=:3 where owner#=:1 and name=:2
ksqgtl *** TM-0000001c-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-00060008-00001960 mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,60008,1960
ksqrcl: TM,1c,0
(4),獲取SYS_JOURNAL_63259表mode=3 DL LOCK
ksqgtl *** DL-0000f71e-00000000 mode=3 flags=0x11 timeout=0 ***
ksqgtl *** DL-0000f71e-00000000 mode=3 flags=0x11 timeout=0 ***
ksqcmi: DL,f71e,0 mode=3 timeout=0
ksqcmi: returns 0
ksqgtl: RETURNS 0
CREATE UNIQUE INDEX "SYS"."SYS_IOT_TOP_63262" on "SYS"."SYS_JOURNAL_63259"("C0","RID") INDEX ONLY TOPLEVEL TABLESPACE "SYSTEM" NOPARALLEL
(5),獲取SYS_JOURNAL_63259表mode=4 TM LOCK
ksqgtl *** TM-0000f71e-00000000 mode=4 flags=0x401 timeout=0 ***
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
ksqgtl *** TM-00000012-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqcmi: TM,12,0 mode=3 timeout=21474836
ksqcmi: returns 0
ksqgtl: RETURNS 0
ksqgtl *** TX-00060005-00001953 mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,60005,1953
ksqrcl: TM,12,0
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, :17)
ksqgtl *** TT-00000000-00000000 mode=4 flags=0x11 timeout=21474836 ***
ksqcmi: TT,0,0 mode=4 timeout=21474836
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** TX-0006001e-00001960 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** TS-00000000-0040fcd9 mode=6 flags=0x1 timeout=21474836 ***
ksqgtl *** MR-00000001-00000002 mode=4 flags=0x10 timeout=0 ***
ksqrcl: MR,1,2
insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,bitmapranges,hwmincr, spare1, scanhint) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,:16,DECODE(:17,0,NULL,:17),:18)
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,6001e,1960
ksqrcl: TM,e,0
ksqrcl: TT,0,10
ksqrcl: TT,0,0
ksqgtl *** HW-00000000-0040fcd9 mode=6 flags=0x11 timeout=21474836 ***
ksqrcl: HW,0,40fcd9
(6),釋放SYS_JOURNAL_63259表DL LOCK
ksqrcl: DL,f71e,0
ksqrcl: DL,f71e,0
insert into icol$(obj#,bo#,intcol#,pos#,segcol#,segcollength,offset,col#,spare1,spare2)values(:1,:2,:3,:4,0,0,0,:5,:6,:7)
ksqgtl *** TM-00000014-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-00060022-0000195f mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,60022,195f
insert into ind$(bo#,obj#,ts#,file#,block#,intcols,type#,flags,property,pctfree$,initrans,maxtrans,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,cols,analyzetime,samplesize,dataobj#,degree,instances,rowcnt,pctthres$,indmethod#,trunccnt,spare1,spare4,spare2,spare6)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,decode(:23,1,null,:23),decode(:24,1,null,:24),:25, :32*256+:26,:27,:28,:29,:30,:31,:33)
ksqgtl *** TM-00000013-00000000 mode=3 flags=0x401 timeout=21474836 ***
insert into tab$(obj#,ts#,file#,block#,bobj#,tab#,intcols,kernelcols,clucols,audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime,samplesize,cols,property,degree,instances,dataobj#,avgspc_flb,flbcnt,trigflag,spare1,spare6)values(:1,:2,:3,:4,decode(:5,0,null,:5),decode(:6,0,null,:6),:7,:8,decode(:9,0,null,:9),:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,decode(:26,1,null,:26),decode(:27,1,null,:27),:28,:29,:30,:31,:32,:33)
ksqgtl *** TM-00000004-00000000 mode=3 flags=0x401 timeout=21474836 ***
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
ksqgtl *** TM-00000015-00000000 mode=3 flags=0x401 timeout=21474836 ***
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
insert into ccol$(con#,obj#,intcol#,pos#,col#,spare1) values(:1,:2,:3,decode(:4,0,null,:4),:5, :6)
ksqgtl *** TM-00000020-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-00060019-00001960 mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,60019,1960
ksqgtl *** TX-00060018-0000195d mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,60018,195d
insert into cdef$(obj#,con#,type#,intcols,condlength,condition,robj#,rcon#,match#,refact,enabled,cols,defer,mtime,spare1)values(:1,:2,:3,decode(:4,0,null,:4),decode(:5,0,null,:5),:6,decode(:7,0,null,:7),decode(:8,0,null,:8),decode(:9,0,null,:9),decode(:10,0,null,:10), decode(:11,0,null,:11),:12, decode(:13,0,null,:13),:14,:15)
ksqgtl *** TM-0000001f-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from ind_stats$ where obj#=:1
ksqgtl *** TM-000002c6-00000000 mode=3 flags=0x401 timeout=21474836 ***
update ind$ set ts#=:2,file#=:3,block#=:4,intcols=:5,type#=:6,flags=:7,property=:8,pctfree$=:9,initrans=:10,maxtrans=:11,blevel=:12,leafcnt=:13,distkey=:14,lblkkey=:15,dblkkey=:16,clufac=:17,cols=:18,analyzetime=:19,samplesize=:20,dataobj#=:21,degree=decode(:22,1,null,:22),instances=decode(:23,1,null,:23),rowcnt=:24,pctthres$=:31*256+:25, indmethod#=:26, trunccnt=:27,spare1=:28,spare4=:29,spare2=:30,spare6=:32where obj#=:1
insert into ind_online$ (obj#,type#,flags) values(:1,:2,:3)
ksqgtl *** TM-000002db-00000000 mode=3 flags=0x401 timeout=21474836 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
insert into con$(owner#,name,con#)values(:1,:2,:3)
ksqgtl *** TM-0000001c-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,6002f,195f
ksqrcl: TM,1c,0
ksqrcl: TM,e,0
ksqrcl: TM,2db,0
ksqrcl: TM,2c6,0
ksqrcl: TM,1f,0
ksqrcl: TM,20,0
ksqrcl: TM,15,0
ksqrcl: TM,4,0
ksqrcl: TM,13,0
ksqrcl: TM,14,0
(7),釋放SYS_JOURNAL_63259表mode=4 TM LOCK
ksqrcl: TM,f71e,0
ksqrcl: TM,12,0
ksqrcl: TT,0,0
ksqrcl: TS,0,40fcd9
(8),獲取SYS_JOURNAL_63259表mode=4 TM LOCK
ksqgtl *** TM-0000f71e-00000000 mode=4 flags=0x401 timeout=21474836 ***
(9),轉換TEST表mode=4 TM LOCK
ksqcnv: TM-0000f71a,00000000 mode=4 timeout=21474836
ksqcmi: TM,f71a,0 mode=4 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
(10),轉換TEST表mode=2 TM LOCK
ksqcnv: TM-0000f71a,00000000 mode=2 timeout=21474836
ksqcmi: TM,f71a,0 mode=2 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
ksqgtl *** TT-00000000-00000000 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** TX-00060012-0000195b mode=6 flags=0x401 timeout=0 ***
ksqgtl *** TS-00000000-0040fce1 mode=6 flags=0x1 timeout=21474836 ***
insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,bitmapranges,hwmincr, spare1, scanhint) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,:16,DECODE(:17,0,NULL,:17),:18)
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,60012,195b
ksqrcl: TM,e,0
ksqrcl: TT,0,10
ksqrcl: TT,0,0
ksqgtl *** HW-00000000-0040fce1 mode=6 flags=0x11 timeout=21474836 ***
ksqrcl: HW,0,40fce1
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** HW-00000000-0040fce1 mode=6 flags=0x411 timeout=21474836 ***
ksqgtl *** TX-00060003-00001960 mode=6 flags=0x401 timeout=0 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,60003,1960
ksqrcl: TM,e,0
ksqrcl: HW,0,40fce1
ksqrcl: TT,0,10
--重複插入
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** HW-00000000-0040fce1 mode=6 flags=0x411 timeout=21474836 ***
ksqgtl *** TX-00060009-0000195d mode=6 flags=0x401 timeout=0 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,60009,195d
ksqrcl: TM,e,0
ksqrcl: HW,0,40fce1
ksqrcl: TT,0,10
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** HW-00000000-0040fce1 mode=6 flags=0x411 timeout=21474836 ***
ksqgtl *** TX-00060001-00001960 mode=6 flags=0x401 timeout=0 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,60001,1960
ksqrcl: TM,e,0
ksqrcl: HW,0,40fce1
ksqrcl: TT,0,10
(11),轉換TEST表mode=4 TM LOCK
ksqcnv: TM-0000f71a,00000000 mode=4 timeout=21474836
ksqcmi: TM,f71a,0 mode=4 timeout=21474836
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-00060025-00001960 mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,60025,1960
ksqrcl: TM,e,0
insert into sys.wri$_optstat_ind_history(obj#,rowcnt,leafcnt,distkey, lblkkey, dblkkey,clufac,blevel,analyzetime,samplesize,guessq,cachedblk, cachehit,logicalread, savtime,flags) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16)
ksqgtl *** TM-00001064-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-00060014-00001960 mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,60014,1960
ksqrcl: TM,1064,0
drop table "SYS"."SYS_JOURNAL_63259" purge
ksqgtl *** SE-00000093-0000051d mode=4 flags=0x0 timeout=21474836 ***
ksqrcl: SE,93,51d
(12),轉換SYS_JOURNAL_63259表mode=6 TM LOCK
ksqcnv: TM-0000f71e,00000000 mode=6 timeout=0
ksqcmi: TM,f71e,0 mode=6 timeout=0
ksqcmi: returns 0
ksqcnv: RETURNS 0
(13),獲取SYS_JOURNAL_63259表mode=6 MD LOCK
ksqgtl *** MD-0000f71e-00000000 mode=6 flags=0x401 timeout=5 ***
delete from object_usage where obj# in (select a.obj# from object_usage a, ind$ b where a.obj# = b.obj# and b.bo# = :1)
ksqgtl *** TM-00000239-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from sys.cache_stats_1$ where dataobj# = :1
ksqgtl *** TM-00000317-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete com$ where obj#=:1
ksqgtl *** TM-00000061-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from hist_head$ where obj# = :1
ksqgtl *** TM-000000ff-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from dependency$ where d_obj#=:1
delete from source$ where obj#=:1
DELETE FROM sys.sumdelta$ sd WHERE sd.tableobj# = :1
ksqgtl *** TM-0000009d-00000000 mode=3 flags=0x401 timeout=21474836 ***
DELETE FROM sys.sumpartlog$ sp WHERE sp.bo# = :1
ksqgtl *** TM-0000009a-00000000 mode=3 flags=0x401 timeout=21474836 ***
DELETE FROM sys.snap_loadertime$ sd WHERE sd.tableobj# = :1
ksqgtl *** TM-000000a1-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from sys.cache_stats_1$ where dataobj# = :1
(14),釋放TEST表DL LOCK
ksqrcl: DL,f71a,0
ksqrcl: DL,f71a,0
delete from idl_ub1$ where obj#=:1 and part=:2
ksqgtl *** TM-00000049-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from idl_char$ where obj#=:1 and part=:2
ksqgtl *** TM-0000004a-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from idl_ub2$ where obj#=:1 and part=:2
ksqgtl *** TM-0000004b-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from idl_sb4$ where obj#=:1 and part=:2
ksqgtl *** TM-0000004c-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
ksqgtl *** TM-000002d7-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from objauth$ where obj#=:1
ksqgtl *** TM-00000039-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from col$ where obj#=:1
ksqgtl *** TM-00000015-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from icol$ where bo#=:1
ksqgtl *** TM-00000014-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from icoldep$ where obj# in (select obj# from ind$ where bo#=:1)
ksqgtl *** TM-00000174-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from jijoin$ where obj# in ( select obj# from jijoin$ where tab1obj# = :1 or tab2obj# = :1)
ksqgtl *** TM-0000021c-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from jirefreshsql$ where iobj# in ( select iobj# from jirefreshsql$ where tobj# = :1)
ksqgtl *** TM-00000220-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from ccol$ where obj#=:1
ksqgtl *** TM-00000020-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from ind$ where bo#=:1
ksqgtl *** TM-00000013-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from cdef$ where obj#=:1
ksqgtl *** TM-0000001f-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from tab$ where obj#=:1
ksqgtl *** TM-00000004-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from col$ where obj#=:1
delete coltype$ where obj#=:1
ksqgtl *** TM-000000a8-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from subcoltype$ where obj#=:1
ksqgtl *** TM-000000ab-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete ntab$ where obj#=:1
ksqgtl *** TM-000000c6-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete lob$ where obj#=:1
ksqgtl *** TM-00000097-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete refcon$ where obj#=:1
ksqgtl *** TM-000000ca-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from opqtype$ where obj#=:1
ksqgtl *** TM-000000cd-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from cdef$ where obj#=:1
delete from objauth$ where obj#=:1
delete from ind_stats$ where obj#=:1
ksqgtl *** TM-000002c6-00000000 mode=3 flags=0x401 timeout=21474836 ***
update ind$ set ts#=:2,file#=:3,block#=:4,intcols=:5,type#=:6,flags=:7,property=:8,pctfree$=:9,initrans=:10,maxtrans=:11,blevel=:12,leafcnt=:13,distkey=:14,lblkkey=:15,dblkkey=:16,clufac=:17,cols=:18,analyzetime=:19,samplesize=:20,dataobj#=:21,degree=decode(:22,1,null,:22),instances=decode(:23,1,null,:23),rowcnt=:24,pctthres$=:31*256+:25, indmethod#=:26, trunccnt=:27,spare1=:28,spare4=:29,spare2=:30,spare6=:32where obj#=:1
delete from ind_online$ where obj#= :1
ksqgtl *** TM-000002db-00000000 mode=3 flags=0x401 timeout=21474836 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
delete from obj$ where obj# = :1
ksqgtl *** TM-00000012-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from con$ where owner#=:1 and name=:2
ksqgtl *** TM-0000001c-00000000 mode=3 flags=0x401 timeout=21474836 ***
delete from obj$ where obj# = :1
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
ksqrcl: TX,6000f,1960
ksqrcl: TM,1c,0
ksqrcl: TM,12,0
ksqrcl: TM,e,0
ksqrcl: TM,2db,0
ksqrcl: TM,2c6,0
ksqrcl: TM,cd,0
ksqrcl: TM,ca,0
ksqrcl: TM,97,0
ksqrcl: TM,c6,0
ksqrcl: TM,ab,0
ksqrcl: TM,a8,0
ksqrcl: TM,4,0
ksqrcl: TM,1f,0
ksqrcl: TM,13,0
ksqrcl: TM,20,0
ksqrcl: TM,220,0
ksqrcl: TM,21c,0
ksqrcl: TM,174,0
ksqrcl: TM,14,0
ksqrcl: TM,15,0
ksqrcl: TM,39,0
ksqrcl: TM,2d7,0
ksqrcl: TM,4c,0
ksqrcl: TM,4b,0
ksqrcl: TM,4a,0
ksqrcl: TM,49,0
ksqrcl: TM,a1,0
ksqrcl: TM,9a,0
ksqrcl: TM,9d,0
ksqrcl: TM,ff,0
ksqrcl: TM,61,0
ksqrcl: TM,317,0
ksqrcl: TM,239,0
(15),釋放SYS_JOURNAL_63259表MD LOCK
ksqrcl: MD,f71e,0
(16),釋放SYS_JOURNAL_63259表TM LOCK
ksqrcl: TM,f71e,0
(17),釋放TEST表TM LOCK
ksqrcl: TM,f71a,0
ksqrcl: TS,0,40fce1
ksqgtl *** TS-00000000-0040fef1 mode=6 flags=0x11 timeout=0 ***
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** RO-00010012-00000001 mode=6 flags=0x11 timeout=21474836 ***
ksqgtl *** RO-00010012-00000002 mode=1 flags=0x11 timeout=21474836 ***
ksqcnv: RO-00010012,00000001 mode=5 timeout=21474836
ksqcmi: RO,10012,1 mode=5 timeout=21474836
ksqcnv: RO-00010012,00000001 mode=6 timeout=21474836
ksqcmi: RO,10012,1 mode=6 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
ksqrcl: RO,10012,1
ksqrcl: returns 0
ksqcnv: RO-00010012,00000002 mode=1 timeout=21474836
ksqcmi: RO,10012,2 mode=1 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
ksqrcl: RO,10012,2
ksqrcl: returns 0
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-000a0025-00001ca8 mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,a0025,1ca8
ksqrcl: TM,e,0
ksqgtl *** TX-0007001c-00001b81 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** CI-00000001-00000005 mode=6 flags=0x10 timeout=21474836 ***
ksqrcl: CI,1,5
delete from seg$ where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,7001c,1b81
ksqrcl: TM,e,0
ksqrcl: TS,0,40fef1
ksqrcl: TT,0,10
ksqgtl *** TS-00000000-0040fcd9 mode=6 flags=0x11 timeout=0 ***
ksqgtl *** TT-00000000-00000010 mode=4 flags=0x11 timeout=21474836 ***
ksqgtl *** RO-00010012-00000001 mode=6 flags=0x11 timeout=21474836 ***
ksqgtl *** RO-00010012-00000002 mode=1 flags=0x11 timeout=21474836 ***
ksqcnv: RO-00010012,00000001 mode=5 timeout=21474836
ksqcmi: RO,10012,1 mode=5 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
ksqcnv: RO-00010012,00000001 mode=6 timeout=21474836
ksqcmi: RO,10012,1 mode=6 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
ksqrcl: RO,10012,1
ksqrcl: returns 0
ksqcnv: RO-00010012,00000002 mode=1 timeout=21474836
ksqcmi: RO,10012,2 mode=1 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
ksqrcl: RO,10012,2
ksqrcl: returns 0
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TX-00080028-00001d64 mode=6 flags=0x401 timeout=0 ***
ksqrcl: TX,80028,1d64
ksqrcl: TM,e,0
ksqgtl *** TX-00030007-00001c53 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** CI-00000001-00000005 mode=6 flags=0x10 timeout=21474836 ***
ksqrcl: CI,1,5
delete from seg$ where ts#=:1 and file#=:2 and block#=:3
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqrcl: TX,30007,1c53
ksqrcl: TM,e,0
ksqrcl: TS,0,40fcd9
ksqrcl: TT,0,10
11g下，在給表建立索引時如果加上online選項，不會阻塞同時進行的DML操作，相當給力的一個功能。
與不帶online的索引建立方式相比在鎖的申請與持有機制上有何區別，我們來比較一下
###建立測試表
sqlplus ad/Uiop246!
create table t0528_1 as select * from all_users;
select object_id from dba_objects where object_name='T0528_1';
OBJECT_ID
----------
17177
---session 1: update但不提交
select * from v$mystat where rownum=1;
SID STATISTIC# VALUE
---------- ---------- ----------
27 0 0
update t0528_1 set username=dbms_random.string('u',5) where user_id=0;
---session 2: create index(非online方式)
select * from v$mystat where rownum=1;
SID STATISTIC# VALUE
---------- ---------- ----------
100 0 0
create index ind_uname on t0528_1(username) tablespace ts_pub; <---直接報錯退出
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
alter session set ddl_lock_timeout=60; <---設定ddl timeout為60s，以便觀察到後面create index時請求的鎖模式
create index ind_uname on t0528_1(username); <---操作被掛起
---session 3: 觀察session 1持有的鎖、session 2請求的鎖
select sid,id1,id2,type,lmode,request from v$lock where sid in (27,100) order by sid;
SID ID1 ID2 TY LMODE REQUEST
---------- ---------- ---------- -- ---------- ----------
27 17177 0 TM 3 0 <---持有lockmode=3的鎖，即型別為row-X (SX)的鎖
27 100 0 AE 4 0
27 65566 340 TX 6 0
100 100 0 AE 4 0
100 17177 0 TM 0 4 <---請求lockmode=4的鎖，即型別為share (S)的鎖
以上資訊可以看出，針對t0528_1(object_id=17177)表，在session 1已經持有了SX鎖的情況下，session 2請求S鎖，由於S與SX鎖不相容，所以session 2會遇到ORA-00054
等待session 2超時後改用online方式create index
---session 2: create index ... online
alter session set ddl_lock_timeout=0; <---復位ddl timeout為0
create index ind_uname on t0528_1(username) online; <----操作掛起，等待session 1的事務結束
---session 3：觀察session 1、session 2上持有的鎖
select sid,id1,id2,type,lmode,request from v$lock where sid in (27,100) order by sid;
SID ID1 ID2 TY LMODE REQUEST
---------- ---------- ---------- -- ---------- ----------
27 100 0 AE 4 0
27 17177 0 TM 3 0 <---依舊持有lockmode=3的鎖，即型別為row-X (SX)的鎖
27 65566 340 TX 6 0
100 100 0 AE 4 0
100 17177 0 TM 2 0 <---轉而持有lockmode=2的鎖，即型別為row-S (SS)的鎖
100 17179 0 TM 4 0 <---object_id=17179指向新增的IOT表SYS_JOURNAL_17178
100 458752 331 TX 6 0
100 17177 0 OD 4 0 <---online模式下才有的OD型別的鎖，它代表online ddl
100 17177 0 DL 3 0 <---online模式下才有的DL型別的鎖，它代表direct loader index creation
100 17177 0 DL 3 0
100 65566 340 TX 0 4 <---在事務級請求持有share (S)鎖，需等待session 1持有的exclusive (X)級的事務鎖釋放，才能申請成功
col type format a5
col name format a40
col description format a60
set linesize 130
select type,name,description from v$lock_type where type in ('OD','DL');
TYPE NAME DESCRIPTION
----- ---------------------------------------- ------------------------------------------------------------
DL Direct Loader Index Creation Lock to prevent index DDL during direct load
OD Online DDLs Lock to prevent concurrent online DDLs
SQL> col object_name format a30
SQL> set linesize 100
SQL> select owner,object_name,object_id from dba_objects where object_id=17179
OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------------ ----------
SYS SYS_JOURNAL_17178 17179
SQL> select owner,table_name,iot_type from dba_tables where table_name='SYS_JOURNAL_17178';
OWNER TABLE_NAME IOT_TYPE
------------------------------ ------------------------------ ------------
SYS SYS_JOURNAL_17178 IOT
和前一次create index情況下持有及請求的鎖資源相比，create index ... online方式有以下一些改變：
(1) 過程中會持有OD(ONLINE DDL)、DL(Direct Loader Index Creation)兩種型別的鎖
(2) 表級鎖TM的持有模式為row-S (SS)，與row-X (SX)型別的鎖互相相容，因此不會在表級發生阻塞
(3) 阻塞發生在行級鎖申請階段，即請求的share (S)型別的鎖與執行DML的session已經持有的exclusive (X)鎖之間存在不相容的情況；相比非online方式的表級鎖，鎖的粒度上更加細化，副作用更小
(4) 新增以SYS_JOURNAL_為字首的IOT表，記錄與索引建立動作同時進行的其它DML操作修改過的記錄，等到索引建立完成前將IOT表裡的記錄合併至索引中
session 2等待期間如果再開一個session對t0528_1表進行dml操作，這個操作依然會成功
---session 4：insert into ...
select * from v$mystat where rownum=1;
SID STATISTIC# VALUE
---------- ---------- ----------
22 0 0
insert into t0528_1 values('AAA',999,to_Date('20160528','yyyymmdd')); <---注意這裡並沒有commit
---session 3：觀察session 1、session 2、session 4上持有和請求的鎖
SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (27,100,22) order by sid;
SID ID1 ID2 TYPE LMODE REQUEST
---------- ---------- ---------- ----- ---------- ----------
22 262163 345 TX 6 0
22 100 0 AE 4 0
22 17177 0 TM 3 0
27 65566 340 TX 6 0
27 17177 0 TM 3 0
27 100 0 AE 4 0
100 458752 331 TX 6 0
100 17179 0 TM 4 0
100 17177 0 TM 2 0
100 100 0 AE 4 0
100 17177 0 OD 4 0
100 17177 0 DL 3 0
100 17177 0 DL 3 0
100 65566 340 TX 0 4
現有的等待鏈有兩組：session 1(update)->session 2(create index online)和session 4(insert)->session 2(create index online)，可以看出並不因為session 4的insert比session 2的create index ... online晚發起而出現前者被後者阻塞的情況，所以create index online線上建立索引的方式對於DML操作不會產生干擾，但是如果併發的DML操作很多，會增加索引建立的耗時
消除這個等待鏈，只需分別在session 1、session 4執行commit
---session 1
commit;
---session 4
commit;
索引建立成功， SYS_JOURNAL_為字首的IOT表也已被清理
col table_name format a20
col column_name format a40
col index_name format a30
set linesize 130
select table_name,column_name,index_name from dba_ind_columns where table_name='T0528_1'
TABLE_NAME COLUMN_NAME INDEX_NAME
-------------------- ---------------------------------------- ------------------------------
T0528_1 USERNAME IND_UNAME
SQL> select * from sys.SYS_JOURNAL_17178;
select * from sys.SYS_JOURNAL_17178
*
ERROR at line 1:
ORA-00942: table or view does not exist
由於我們測試表過小所以create index很快結束，沒能觀察到SYS_JOURNAL_字首的表到底存放了哪些內容，下面再補充一個小測試
create table t0528_2 as select rownum rn,t.* from dba_tables t connect by level<3;
create index ind_rn on t0528_2(rn) online tablespace ts_pub;
在create index尚在執行時另開一session執行
update t0528_2 set rn=99999 where rn=1;
commit;
檢查IOT表
SELECT * FROM AD.SYS_JOURNAL_9845625;
C0 O PARTNO RID
---------- - ---------- ------------------
99999 I 0 D/////ANYAAC2CTAAA
1 D 0 D/////ANYAAC2CTAAA
存放形式有點類似與MV log，舊值1被標記為Delete，新值99999標記為Insert，唯一不同的是RID列記錄的並非是完整的rowid值
About Me
...............................................................................................................................
● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2128896/
● 本文部落格園地址：http://www.cnblogs.com/lhrbest/p/6091277.html
● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 聯絡我請加QQ好友(646634621)，註明新增緣由
● 於 2016-10-21 09:00 ~ 2016-11-22 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
...............................................................................................................................
拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-2151735/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 查詢鎖 && 解鎖Oracle
- Oracle查詢鎖、解鎖Oracle
- oracle的鎖Oracle
- ORACLE TX鎖Oracle
- oracle檢視被鎖的表和解鎖Oracle
- 檢視oracle死鎖程式並結束死鎖Oracle
- 檢視oracle被鎖的表是誰鎖的Oracle
- redis系列：分散式鎖Redis分散式
- Java彌散系列 - 樂觀鎖與悲觀鎖Java
- MySQL死鎖系列-常見加鎖場景分析MySql
- 圖解Janusgraph系列-併發安全：鎖機制（本地鎖+分散式鎖）分析圖解分散式
- MySQL鎖（讀鎖、共享鎖、寫鎖、S鎖、排它鎖、獨佔鎖、X鎖、表鎖、意向鎖、自增鎖、MDL鎖、RL鎖、GL鎖、NKL鎖、插入意向鎖、間隙鎖、頁鎖、悲觀鎖、樂觀鎖、隱式鎖、顯示鎖、全域性鎖、死鎖）MySql
- Oracle TX鎖的處理Oracle
- MySQL死鎖系列-線上死鎖問題排查思路MySql
- ORACLE基礎之oracle鎖（oracle lock mode）詳解Oracle
- Oracle-解鎖scott賬戶Oracle
- Oracle面試寶典-鎖篇Oracle面試
- 檢視ORACLE中鎖定物件Oracle物件
- Oracle死鎖一例(ORA-00060)，鎖表導致的業務死鎖問題Oracle
- 自旋鎖、阻塞鎖、可重入鎖、悲觀鎖、樂觀鎖、讀寫鎖、偏向所、輕量級鎖、重量級鎖、鎖膨脹、物件鎖和類鎖物件
- Java鎖？分散式鎖？樂觀鎖？行鎖？Java分散式
- Java鎖最全詳解：樂觀鎖/悲觀鎖+公平鎖/非公平鎖+獨享鎖/共享鎖Java
- S 鎖與 X 鎖的愛恨情仇《死磕MySQL系列 四》MySql
- Elasticsearch系列---實現分散式鎖Elasticsearch分散式
- 深入理解MySQL系列之鎖MySql
- Java 中15種鎖的介紹：公平鎖，可重入鎖，獨享鎖，互斥鎖，樂觀鎖，分段鎖，自旋鎖等等Java
- 全域性鎖、表鎖、行鎖
- MySQL鎖(樂觀鎖、悲觀鎖、多粒度鎖)MySql
- oracle鎖級別相關測試Oracle
- java 中的鎖 -- 偏向鎖、輕量級鎖、自旋鎖、重量級鎖Java
- InnoDB常用鎖總結（行鎖、間隙鎖、臨鍵鎖、表鎖）
- MySQL/InnoDB中，樂觀鎖、悲觀鎖、共享鎖、排它鎖、行鎖、表鎖、死鎖概念的理解MySql
- MySQL優化篇系列文章（二）——MyISAM表鎖與InnoDB鎖問題MySql優化
- mysql鎖之死鎖MySql
- 圖解AQS系列（上）--獨佔鎖圖解AQS
- java重入鎖、公平鎖和非公平鎖Java
- Lock鎖之重入鎖與讀寫鎖
- MySQL全域性鎖、表鎖以及行鎖MySql