oracle 11g不同會話產生的事務會使用相同的undo segment嗎--undo系列之一
背景
不同會話產生的事務會不會同時使用相同的undo segment呢，本文我們測試這個問題，加深對於undo概念的理解。結論
1,dba_rollback_segs初始時即資料庫啟動有建立產生11個回滾段，後期隨著資料庫事務的增加，會動態調整增加但是如果已經建立的回滾段，不會因為事務已提交而回收
2,不同的事務會分配不同的undo segment,而不會出現多個事務分配相同的undo segment
3,隱含引數_rollback_segment_count不是控制或限制資料庫建立多少個回滾段
4,對於undo機制的理解仍停留在表層，還要很多工作和知識要補充
測試
資料庫版本
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
1,資料庫目前的回滾段資訊，共計11個
SQL> select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU31_4292867569$
_SYSSMU30_4054687248$
_SYSSMU29_193706344$
_SYSSMU28_1986380219$
_SYSSMU27_3263502189$
_SYSSMU26_2187890310$
_SYSSMU25_848072715$
_SYSSMU24_2463137615$
_SYSSMU23_2210640156$
_SYSSMU22_185849310$
11 rows selected.
會話1
SQL> update scott.t_undo set a=3 where rownum=1;
1 row updated.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU31_4292867569$
會話2
SQL> create table t_oun(a int);
Table created.
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU24_2463137615$
會話3
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU27_3263502189$
會話4
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU28_1986380219$
會話5
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU29_193706344$
會話6
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU26_2187890310$
會話7
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU23_2210640156$
會話8
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU22_185849310$
會話9
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU30_4054687248$
會話10
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU25_848072715$
會話11
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU1_2507315526$
可見undo segment的數量是隨著資料庫的事務情況動態變化的，不會一成不變
SQL> select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------------------------------------
SYSTEM
_SYSSMU31_4292867569$
_SYSSMU30_4054687248$
_SYSSMU29_193706344$
_SYSSMU28_1986380219$
_SYSSMU27_3263502189$
_SYSSMU26_2187890310$
_SYSSMU25_848072715$
_SYSSMU24_2463137615$
_SYSSMU23_2210640156$
_SYSSMU22_185849310$
SEGMENT_NAME
------------------------------------------------------------
_SYSSMU2_995037188$
_SYSSMU1_2507315526$
13 rows selected.
會話12
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU2_995037188$
會話13
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU3_2358976981$
SQL> select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------------------------------------
SYSTEM
_SYSSMU31_4292867569$
_SYSSMU30_4054687248$
_SYSSMU29_193706344$
_SYSSMU28_1986380219$
_SYSSMU27_3263502189$
_SYSSMU26_2187890310$
_SYSSMU25_848072715$
_SYSSMU24_2463137615$
_SYSSMU23_2210640156$
_SYSSMU22_185849310$
SEGMENT_NAME
------------------------------------------------------------
_SYSSMU3_2358976981$
_SYSSMU2_995037188$
_SYSSMU1_2507315526$
14 rows selected.
回滾所有上述的13個會話的事務，發現已產生的14個回滾段沒有回收
SQL> select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU31_4292867569$
_SYSSMU30_4054687248$
_SYSSMU29_193706344$
_SYSSMU28_1986380219$
_SYSSMU27_3263502189$
_SYSSMU26_2187890310$
_SYSSMU25_848072715$
_SYSSMU24_2463137615$
_SYSSMU23_2210640156$
_SYSSMU22_185849310$
SEGMENT_NAME
------------------------------
_SYSSMU3_2358976981$
_SYSSMU2_995037188$
_SYSSMU1_2507315526$
14 rows selected.
獲取與udno segment相關的隱含引數
NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
transactions_per_rollback_segment 5 number of active transactions per rollback segment
_rollback_segment_count 0 number of undo segments
可見每個undo segment目前配置最大的活動事務個數為5個，而可以建立的undo segment個數為0，即表示不受限制
當前佔用回滾段個數為13，剛好和dba_rollback_segs一致（因為還有有一個system rollback segment）
SQL> select status,count(*) from dba_undo_extents group by status;
STATUS COUNT(*)
------------------ ----------
UNEXPIRED 13
EXPIRED 34
SQL> select count(*) from dba_rollback_segs;
COUNT(*)
----------
14
顯式配置_rollback_segment_count為5個,限制回滾段個數
SQL> alter system set "_rollback_segment_count"=5;
System altered.
會話1
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU2_995037188$
會話2
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU1_2507315526$
會話3
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU27_3263502189$
會話4
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU28_1986380219$
會話5
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU29_193706344$
會話6
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU26_2187890310$
會話7
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU23_2210640156$
會話8
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU22_185849310$
會話9
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU3_2358976981$
會話10
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU30_4054687248$
會話11
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU25_848072715$
會話12
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU24_2463137615$
會話13
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU31_4292867569$
會話14
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU4_2259594378$
會話15
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU5_3454406355$
可見隱含引數_rollback_segment_count不能控制或限制資料庫建立多少個回滾段
SQL> select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------------------------------------
SYSTEM
_SYSSMU31_4292867569$
_SYSSMU30_4054687248$
_SYSSMU29_193706344$
_SYSSMU28_1986380219$
_SYSSMU27_3263502189$
_SYSSMU26_2187890310$
_SYSSMU25_848072715$
_SYSSMU24_2463137615$
_SYSSMU23_2210640156$
_SYSSMU22_185849310$
SEGMENT_NAME
------------------------------------------------------------
_SYSSMU5_3454406355$
_SYSSMU4_2259594378$
_SYSSMU3_2358976981$
_SYSSMU2_995037188$
_SYSSMU1_2507315526$
16 rows selected.
個人簡介
8年oracle從業經驗，具備豐富的oracle技能，目前在國內北京某專業oracle服務公司從事高階技術顧問。
服務過的客戶：
中國電信
中國移動
中國聯通
中國電通
國家電網
四川達州商業銀行
湖南老百姓大藥房
山西省公安廳
中國郵政
北京302醫院
河北廊坊新奧集團公司
專案經驗:
中國電信3G專案AAA系統資料庫部署及最佳化
中國聯通4G資料庫效能分析與最佳化
中國聯通4G資料庫效能分析與最佳化
中國聯通CRM資料庫效能最佳化
中國移動10086電商平臺資料庫部署及最佳化
湖南老百姓大藥房ERR資料庫sql最佳化專案
四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化
四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化
北京高鐵訊號監控系統RAC資料庫部署及最佳化
河南宇通客車資料庫效能最佳化
中國電信電商平臺核心採購模組表模型設計及最佳化
中國郵政儲蓄系統資料庫效能最佳化及sql最佳化
北京302醫院資料庫遷移實施
河北廊坊新奧data guard部署及最佳化
山西公安廳身份證審計資料庫系統故障評估
國家電網上海災備專案4 node rac+adg
貴州移動crm及客服資料庫效能最佳化專案
貴州移動crm及客服務資料庫sql稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案
貴州移動crm及客服資料庫效能最佳化專案
貴州移動crm及客服務資料庫sql稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案
聯絡方式：
手機：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub部落格名稱：wisdomone1 http://blog.itpub.net/9240380/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-1814233/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 不同insert操作產生的undo的測試
- 28、undo_1_2（undo引數、undo段、事務）
- oracle的redo和undoOracle
- Oracle Redo and UndoOracle Redo
- 關於oracle中的undoOracle
- 淺析MySQL事務中的redo與undoMySql
- oracle undo分配規則Oracle
- Oracle OCP(48)：UNDO TABLESPACEOracle
- 【UNDO】Oracle undo表空間使用率過高，因為一個查詢Oracle
- [20211021]關於undo段頭事務表.txt
- Oracle：select 或 inactive 會話語句產生鎖？Oracle會話
- Oracle常見UNDO等待事件Oracle事件
- 【REDO】Oracle redo undo 學習Oracle Redo
- Oracle 12c 新特性之臨時Undo--temp_undo_enabledOracle
- 2.5.5 使用自動Undo管理: 建立 Undo 表空間
- MySQL必知必會：簡介undo log、truncate、以及undo log如何幫你回滾事物MySql
- Innodb undo之 undo物理結構的初始化
- impdp ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
- Oracle 面試寶典-UNDO篇Oracle面試
- 18_深入解析Oracle undo原理(2)_undo表空間使用率100%問題處理Oracle
- Oracle 12C R2新特性-本地UNDO模式（LOCAL_UNDO_ENABLED）Oracle模式
- Innodb undo之 undo結構簡析
- undo_retention的作用
- MySQL undoMySql
- Sqlserver沒有單獨的undo檔案，使用tempdb和redo log來存放undo資料SQLServer
- Oracle undo保留時間的幾個相關引數Oracle
- 17_深入解析Oracle undo原理(1)_transactionOracle
- ORACLE線上切換undo表空間Oracle
- 【UNDO】Oracle系統回滾段說明Oracle
- oracle的會話如何自殺？Oracle會話
- Oracle undo 表空間資料檔案丟失強制啟動資料庫（沒有未提交的事務）Oracle資料庫
- Oracle切換undo表空間操作步驟Oracle
- MySQL purge 清理undoMySql
- 切換UNDO(zt)
- InnoDB undo log原理
- ?ORACLE會話超時Oracle會話
- oracle中undo表空間丟失處理方法Oracle
- 19_深入解析Oracle undo原理(3)_ktuxe詳解OracleUX
- 20_深入解析Oracle undo原理(4)_ktuxc詳解OracleUX