oracle10g undo tablespace相關知識
undo tablespace
概念：
1，用於恢復事務
2，一致性查詢
3，oracle閃回查詢及閃回事務
如果資料庫沒有undo tablespace，資料庫可以啟動，但會使用system表空間儲存undo data;
undo_rerention
概念：
1，用於oracle閃回查詢及閃回事務
2，長事務查詢，因為會用到一致性塊；（注：雖然事務已提交）
3，undo_retention儘量大一些
如果開啟自動撤消管理模式，即存在一個undo retention period;
undo retention period：
概念：
1，單位是秒
2，重寫撤消空間之前撤消資料的最小儲存時間；
3，基於這個時間，如果已commit的撤消資料老於當前的undo retention period，則其狀態為：expired
4, 同上，如少於當前的undo retention period，則狀態為unexpired;
5，它的大小，oracle會自動根據undo tablespace大小和資料庫活動情況自動進行調節；
當然，也可以手工以undo_rentention指定最小的undo retention period;
6,只要新的事務能從undo tablespace分配到空間，oracle會盡力基於undo retention period儲存undo data；
如果undo tablespace空間不足，先重寫expired undo data,發現空間仍不足，重寫unexpired undo data;
以上2者還是不能解決空間問題，報snapshot too old錯誤
與undo_retention引數有關的幾個要點：
1，如果undo tablespace不是自動擴充套件，即固定大小的檔案，資料庫如空間不足，直接重寫unexpired undo data;
2,如果undo tablespace自動擴充套件，會根據undo_retention盡力儲存undo data;如空間不足，則先自動擴充套件；
如指定了maxsize，且達到maxsize，則重寫unexpired undo data;
retention guarantee
概念：
1，為了保證一些長查詢或者閃回操作的順利進行，可以啟動此功能
2，啟用此功能後，即便undo tablespace空間不足，也不會重寫unexpired undo data,一般預設不開啟；
3，如果啟用此功能，則可能導致很多dml失敗；所以使用一定要小心
4，有多種方式啟用此功能：
1，create database
2,create undo tablespace
3,alter tablespace
5，用dba_tablespaces可以檢視此功能是否啟用
1,dba_tablespace的retention列：
值為：not apply用於非undo tablespace
guarantee用於undo tablespace
noguarantee同上
---undo tablespace資料檔案是自動擴充套件
select file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name='UNDOTBS1'
FILE_NAME TABLESPACE_NAME AUT
-------------------------------------------------- ------------------------------ ---
/oracle10g/oradata/first/undotbs01.dbf UNDOTBS1 YES
SQL> select tablespace_name,retention from dba_tablespaces;
TABLESPACE_NAME RETENTION
------------------------------ -----------
SYSTEM NOT APPLY
UNDOTBS1 NOGUARANTEE
SYSAUX NOT APPLY
TEMP NOT APPLY
USERS NOT APPLY
undo retention自動調節
1,如undo tablespace固定大小非自動擴充套件，資料庫會根據系統負荷及undo tablespace大小，調節undo retention大小；
調節的undo retention明顯要大於undo_retention引數的值
2,如undo tablespace自動擴充套件，資料庫會基於系統當前時間最長查詢來調節undo retention，其undo retention的值稍大於最長查詢所佔用的
時間；而且，調節的undo retention的值可能大於undo_retenion引數的值
3，此功能不適用於lob資料型別，因為lob列把undo儲存在它獨立的segment，而不會儲存在undo tablespace中；
對於lob,oracle盡力根據undo_retention儲存undo data,但若空間不足，會重寫lob unexpired undo data;
調節的undo retention值可從如下檢視獲知，取樣間隔為10分鐘；僅儲存4天的資料，若超過4天，則儲存在dba_hist_undostat中
SQL> select to_char(begin_time,'yyyymmdd hh24:mi:ss') begin_time,to_char(end_time,'yyyymmdd hh24:mi:ss') end_time,tuned_undoretention from v$undostat order by 1;
BEGIN_TIME END_TIME TUNED_UNDORETENTION
----------------- ----------------- -------------------
20130708 10:17:03 20130708 10:27:03 900
20130708 10:27:03 20130708 10:37:03 900
20130708 10:37:03 20130708 10:47:03 900
20130708 10:47:03 20130708 10:57:03 900
20130708 10:57:03 20130708 11:07:03 900
20130708 11:07:03 20130708 11:17:03 900
20130708 11:17:03 20130708 11:27:03 900
20130708 11:27:03 20130708 11:37:03 900
20130708 11:37:03 20130708 11:47:03 900
20130708 11:47:03 20130708 11:57:03 900
20130708 11:57:03 20130708 12:07:03 900
BEGIN_TIME END_TIME TUNED_UNDORETENTION
----------------- ----------------- -------------------
20130708 12:07:03 20130708 12:17:03 900
20130708 12:17:03 20130708 13:17:03 0
20130708 13:17:03 20130708 13:27:03 900
20130708 13:27:03 20130708 13:37:03 900
20130708 13:37:03 20130708 13:47:03 900
20130708 13:47:03 20130708 14:07:03 0
20130708 14:07:03 20130708 14:17:03 900
20130708 14:17:03 20130708 14:27:03 900
20130708 14:27:03 20130708 14:37:03 900
20130708 14:37:03 20130708 14:47:03 900
20130708 14:47:03 20130708 14:57:03 900
BEGIN_TIME END_TIME TUNED_UNDORETENTION
----------------- ----------------- -------------------
20130708 14:57:03 20130708 15:07:03 900
20130708 15:07:03 20130708 15:17:03 900
20130708 15:17:03 20130708 15:27:03 900
20130708 15:27:03 20130708 15:37:03 900
20130708 15:37:03 20130708 15:47:03 900
20130708 15:47:03 20130708 15:57:03 900
20130708 15:57:03 20130708 16:05:51 900
29 rows selected.
SQL>
如配置undo_retention，則當前的undo retention值動態調整後不能小於undo_retention
但如啟用retention guarantee，如空間不足，則當前的undo retention值動態調整後可能小於undo_retention
--undo_retention修改後直接生效，它以undo tablespace為基礎，即使你配置過大，還是以undo tablespace size為準
SQL> show parameter undo_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_retention=1000 scope=memory;
System altered.
SQL> show parameter undo_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 1000
undo_tablespace string UNDOTBS1
如何定義undo tablespace的大小
1，針對固定大小非自動擴充套件，採用oem或undo advisor即dbms_advisor來調節undo tablespace的大小
2，而對於自動擴充套件，由oracle自動調節其大小即可
undo advisor
1,它取決於awr收集資訊是否充足
2,awr收集的間隔
為了使用undo advisor
1，首先配置2個值，最大查詢所用的時間
2，最長閃回查詢的時間
取上述最大，即為固定大小undo tablespace大小
undo advisor使用
1，具體用法參考：Oracle Database 2 Day DBA
2，示例：
DECLARE
tid NUMBER;
tname VARCHAR2(30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 1);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2);
DBMS_ADVISOR.SET_TASK_PARAMETER(name, 'INSTANCE', 1);
DBMS_ADVISOR.execute_task(tname);
end;
/
執行結果可從oem或者從dba_advisor相關字典查詢
建立undo tablespace的方法：
1，直接在建資料庫透過create database構建undo tablespace
示例：
create database test
.
.
.
undo tablespace undotbs1 datafile '/oracle10g/undotbs1.dbf';
2,資料庫已存在情況下，create undo tablespace亦可建立undo tablespace
示例：
create undo tablespace undotbs1 datafile '/oracle10g/undotbs1.dbf' size 2m reuse autoextend on;
變更undo tablespace的幾種情況：
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 25 32767.9844 YES
SQL> !ls -lh /oracle10g/oradata/first/undotbs01.dbf
-rw-r----- 1 oracle10g oinstall 26M Jul 8 17:05 /oracle10g/oradata/first/undotbs01.dbf
*
-----啟用undo tablespace自動擴充套件且最大值為50m
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' autoextend on maxsize 50m;
Database altered.
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 25 50 YES
--啟用undo tablespace自動擴充套件且最大值為unlimited
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' autoextend on maxsize unlimited;
Database altered.
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 25 32767.9844 YES
SQL>
---重定義undo tablespace大小
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' resize 35m;
Database altered.
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 35 32767.9844 YES
--新增資料檔案到undo tablespace
SQL> alter tablespace undotbs1 add datafile '/oracle10g/oradata/first/undotbs01_second.dbf' size 10m;
Tablespace altered.
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 35 32767.9844 YES
UNDOTBS1 /oracle10g/oradata/first/undotbs01_second.dbf 10 0 NO
--從undo tablespace刪除資料檔案
SQL> alter tablespace undotbs1 drop datafile '/oracle10g/oradata/first/undotbs01_second.dbf';
Tablespace altered.
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 35 32767.9844 YES
SQL>
---直接離線undo tablespace不可以
SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace
---直接離線undo tablespace對應的資料檔案可以
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' offline;
alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' offline
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
－－－離線undo tablespace後alert大量報錯如下
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/oracle10g/oradata/first/undotbs01.dbf'
Mon Jul 8 17:20:44 2013
Errors in file /oracle10g/admin/first/bdump/first_smon_2589.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/oracle10g/oradata/first/undotbs01.dbf'
Mon Jul 8 17:20:45 2013
alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' online
Mon Jul 8 17:20:45 2013
Completed: alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' online
---離線undo tablespace的資料檔案後再次線上報錯，需要介質恢復
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' online;
alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' online
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/oracle10g/oradata/first/undotbs01.dbf'
--介質恢復undo tablespace datafile 2
SQL> recover datafile 2;
ORA-00603: ORACLE server session terminated by fatal error
--線上undo tablespace datafile 2
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' online;
Database altered.
--新增一個檔案到undo tablespace
SQL> alter tablespace undotbs1 add datafile '/oracle10g/oradata/first/undotbs01_second.dbf' size 10m;
Tablespace altered.
--離線新增資料檔案
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' offline;
Database altered.
--alert內容如下：
Mon Jul 8 17:27:00 2013
alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' offline
Mon Jul 8 17:27:00 2013
Completed: alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' offline
---線上資料檔案提示介質恢復
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' online;
alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/oracle10g/oradata/first/undotbs01_second.dbf'
---alert內容如下：
Mon Jul 8 17:28:54 2013
alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' online
Mon Jul 8 17:28:54 2013
ORA-1113 signalled during: alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' online...
--介質恢復
SQL> recover datafile 5;
Media recovery complete.
--alert內容如下：
Mon Jul 8 17:30:25 2013
ALTER DATABASE RECOVER datafile 5
Mon Jul 8 17:30:25 2013
Media Recovery Start
Mon Jul 8 17:30:25 2013
Recovery of Online Redo Log: Thread 1 Group 1 Seq 35 Reading mem 0
Mem# 0 errs 0: /oracle10g/oradata/first/redo01.log
Mon Jul 8 17:30:25 2013
Media Recovery Complete (first)
Completed: ALTER DATABASE RECOVER datafile 5
--再次線上成功
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' online;
Database altered.
---alert內容如下
Mon Jul 8 17:31:44 2013
alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' online
Mon Jul 8 17:31:44 2013
Completed: alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' online
---undo tablespace的undo retention屬性列guarantee
SQL> select tablespace_name,status,retention from dba_tablespaces where tablespace_name='UNDOTBS1';
TABLESPACE_NAME STATUS RETENTION
------------------------------ --------- -----------
UNDOTBS1 ONLINE NOGUARANTEE
---由noguarantee變更為guarantee
SQL> alter tablespace undotbs1 retention guarantee;
Tablespace altered.
SQL> select tablespace_name,status,retention from dba_tablespaces where tablespace_name='UNDOTBS1';
TABLESPACE_NAME STATUS RETENTION
------------------------------ --------- -----------
UNDOTBS1 ONLINE GUARANTEE
---在sqlplus下不能熱備一個undo tablespace的資料檔案
SQL> alter database file '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup;
alter database file '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
SQL> alter datafile '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup;
alter datafile '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> alter datafile '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup;
alter datafile '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup;
alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup
*
ERROR at line 1:
ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected
---重合名undo tablespace datafile
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 35 32767.9844 YES
UNDOTBS1 /oracle10g/oradata/first/undotbs01_second.dbf
--必須先複製一個重新命名的資料檔案到目標目錄
SQL> alter database rename file '/oracle10g/oradata/first/undotbs01_second.dbf
2 ' to '/oracle10g/oradata/first/undotbs01_secondnewly.dbf';
alter database rename file '/oracle10g/oradata/first/undotbs01_second.dbf
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, datafile, or tempfile "/oracle10g/oradata/first/undotbs01_second.dbf
"
---複製一個重新命名的資料檔案到目標目錄
SQL> !cp -p /oracle10g/oradata/first/undotbs01_second.dbf /oracle10g/oradata/first/undotbs01_secondnewly.dbf
SQL> alter database rename file '/oracle10g/oradata/first/undotbs01_second.dbf to '/oracle10g/oradata/first/undotbs01_secondnewly.dbf';
alter database rename file '/oracle10g/oradata/first/undotbs01_second.dbf to '/oracle10g/oradata/first/undotbs01_secondnewly.dbf'
*
ERROR at line 1:
ORA-00946: missing TO keyword
---rename成功
SQL> alter database rename file '/oracle10g/oradata/first/undotbs01_second.dbf' to '/oracle10g/oradata/first/undotbs01_secondnewly.dbf';
Database altered.
--查詢rename成果
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 35 32767.9844 YES
UNDOTBS1 /oracle10g/oradata/first/undotbs01_secondnewly.dbf
SQL>
---如undo tablespace還被例項使用，不能刪除，如果有多個undo tablespace，刪除其中之一時，確保沒有包含unexpired undo data;
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
--新增一個undo tablespace
SQL> create undo tablespace undotbs2 datafile '/oracle10g/oradata/first/undo2.dbf' size 10m;
Tablespace created.
--新建的undo tablespace狀態為offline,即未使用
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS2 _SYSSMU20$ OFFLINE
UNDOTBS2 _SYSSMU19$ OFFLINE
UNDOTBS2 _SYSSMU18$ OFFLINE
UNDOTBS2 _SYSSMU17$ OFFLINE
UNDOTBS2 _SYSSMU16$ OFFLINE
UNDOTBS2 _SYSSMU15$ OFFLINE
UNDOTBS2 _SYSSMU14$ OFFLINE
UNDOTBS2 _SYSSMU13$ OFFLINE
UNDOTBS2 _SYSSMU12$ OFFLINE
UNDOTBS2 _SYSSMU11$ OFFLINE
21 rows selected.
切換undo tablespace
1,此動作可以馬上生效
示例：
SQL> alter system set undo_tablespace=undotbs2;
System altered.
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU8$ OFFLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU2$ OFFLINE
UNDOTBS1 _SYSSMU1$ OFFLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS2 _SYSSMU20$ ONLINE
UNDOTBS2 _SYSSMU19$ ONLINE
UNDOTBS2 _SYSSMU18$ ONLINE
UNDOTBS2 _SYSSMU17$ ONLINE
UNDOTBS2 _SYSSMU16$ ONLINE
UNDOTBS2 _SYSSMU15$ ONLINE
UNDOTBS2 _SYSSMU14$ ONLINE
UNDOTBS2 _SYSSMU13$ ONLINE
UNDOTBS2 _SYSSMU12$ ONLINE
UNDOTBS2 _SYSSMU11$ ONLINE
21 rows selected.
---變更預設undo tablespace之前
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS2 _SYSSMU20$ OFFLINE
UNDOTBS2 _SYSSMU19$ OFFLINE
UNDOTBS2 _SYSSMU18$ OFFLINE
UNDOTBS2 _SYSSMU17$ OFFLINE
UNDOTBS2 _SYSSMU16$ OFFLINE
UNDOTBS2 _SYSSMU15$ OFFLINE
UNDOTBS2 _SYSSMU14$ OFFLINE
UNDOTBS2 _SYSSMU13$ OFFLINE
UNDOTBS2 _SYSSMU12$ OFFLINE
UNDOTBS2 _SYSSMU11$ OFFLINE
21 rows selected.
--不提交產生一個dml
[oracle10g@rhel5 bdump]$ sqlplus scott/system@first
SQL> update dept set loc=loc;
4 rows updated.
---即便要切換的undo tablespace包含未提交事務，也可以切換到另一個undo tablespace
SQL> alter system set undo_tablespace=undotbs2;
System altered.
---刪除切換之前的undo tablespace報錯，因為它還包含未提交的事務
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
---如下查詢也證實undotbs1的_SYSSMU8$段包含活動事務
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU2$ OFFLINE
UNDOTBS1 _SYSSMU1$ OFFLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS2 _SYSSMU20$ ONLINE
UNDOTBS2 _SYSSMU19$ ONLINE
UNDOTBS2 _SYSSMU18$ ONLINE
UNDOTBS2 _SYSSMU17$ ONLINE
UNDOTBS2 _SYSSMU16$ ONLINE
UNDOTBS2 _SYSSMU15$ ONLINE
UNDOTBS2 _SYSSMU14$ ONLINE
UNDOTBS2 _SYSSMU13$ ONLINE
UNDOTBS2 _SYSSMU12$ ONLINE
UNDOTBS2 _SYSSMU11$ ONLINE
21 rows selected.
--查詢當前在用的undo tablespace
SQL> show parameter undo_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 100000
undo_tablespace string UNDOTBS1
--指定不存在的undo tablespace直接報錯
SQL> alter system set undo_tablespace=undotbs8;
alter system set undo_tablespace=undotbs8
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-30012: undo tablespace 'UNDOTBS8' does not exist or of wrong type
--把當前在用的重新指定使用也是ok的
SQL> alter system set undo_tablespace=undotbs1;
System altered.
---指定非undo tablespace直接報錯
SQL> alter system set undo_tablespace=users;
alter system set undo_tablespace=users
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-30012: undo tablespace 'USERS' does not exist or of wrong type
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
11 rows selected.
SQL>
---檢視當前使用的undo tablespace
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
11 rows selected.
---以空格方式指定當前使用的undo tablespace
SQL> alter system set undo_tablespace='';
System altered.
---空格指定後undotbs1狀態變為offline
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU8$ OFFLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU2$ OFFLINE
UNDOTBS1 _SYSSMU1$ OFFLINE
11 rows selected.
---空格指定後undotbs1也變為空
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2022144 bytes
Variable Size 184550656 bytes
Database Buffers 348127232 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.
--重啟資料庫後又恢復到之前的配置
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
11 rows selected.
SQL>
遷移到自動undo管理模式
示例：
--返回大小相關資訊
declare
x number;
begin
x:＝dbms_undo_adv.rbu_migration;
end;
與undo tablespace相關的字典
v$undostat 管理undo usage相關資訊
dba_hist_undostat 為上述的歷史資訊
v$rollstat 包含undo segment相關資訊
v$transaction 與undo segment相關的資訊
dba_undo_extents 包含undo tablespace每個extent相關資訊
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-765813/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle OCP(48)：UNDO TABLESPACEOracle
- MySQL學習總結：提問式回顧 undo log 相關知識MySql
- .net相關知識
- Shell相關知識
- RPM相關知識
- /proc的相關知識
- redis相關知識點Redis
- Git相關知識點Git
- 音訊相關知識音訊
- Redis的相關知識Redis
- SSL相關知識科普
- Elasticsearch——search相關知識Elasticsearch
- 相機成像相關知識總結
- CT校正相關知識整理
- CAP 與 Raft 相關知識Raft
- RTMP協議相關知識協議
- Vlan相關知識雜記
- LR模型相關知識點模型
- 【Java】容器相關知識點Java
- ivar layout 相關知識點
- WEB相關背景知識（新手）Web
- 2.6.8.2 UNDO_TABLESPACE 初始化引數
- React相關知識點：關於ReduxReactRedux
- 關於Python Number 相關的知識！Python
- 信管知識梳理（三）軟體工程相關知識軟體工程
- 座標系相關知識科普
- hadoop一些相關知識Hadoop
- 事務相關知識集錦
- Java容器相關知識點整理Java
- 總結 MySQL 相關知識點MySql
- wifi認證的相關知識WiFi
- JVM相關知識點總結JVM
- Android進階知識：Handler相關Android
- 【雜談】FilterChain相關知識整理FilterAI
- 【詳解】WebSocket相關知識整理Web
- 資料結構相關知識資料結構
- 對JAVAWEB相關知識的學些JavaWeb
- clickhouse的一些相關知識
- 資料庫相關知識點提要資料庫