oracle管理之 undo tablespace(server.102 b14231)

--undo作用
1、Roll back transactions when a ROLLBACK statement is issued
2、Recover the database
3、Provide read consistency
Analyze data as of an earlier point in time by using Oracle Flashback Query
4、Recover from logical corruptions using Oracle Flashback features

--init 引數
UNDO_MANAGEMENT auto/manual
UNDO_TABLESPACE undotbs_1
UNDO_RETENTION in seconds
alter tablespace *** RETENTION GUARANTEE

--Migrating to Automatic Undo Management
DECLARE
utbsiz_in_MB NUMBER;
BEGIN
utbsiz_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
end;
/

--undo 動態效能檢視
V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION
DBA_UNDO_EXTENTS
DBA_HIST_UNDOSTAT

總結：undo tablespace建議UNDO_MANAGEMENT =auto；undo分為expired(committed),unexpired(committed),uncommited,當表空間不是自動擴充套件時，會先重用expired，然後再用unexpired,此時UNDO_RETENTION不起作用，UNDO_RETENTION只在空間充足的情況下有效。

undo表空間的大小要根據負載量和要求的UNDO_RETENTION來決定。可以根據 DBMS_UNDO_ADV的建議來確定

[@more@]