insert：undo中只记录了rowid,如果回退，只需要通过rowid删除即可
update: undo中只记录了旧值，如果回退，通过反向操作恢复其值
delete: undo中记录整行记录,delete操作是最占用undo空间的

1.1 查看undo相关参数
SYS@PROD1>show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

1.2 切换表空间
SYS@PROD1>create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/PROD1/undotbs2_01.dbf' size 100m autoextend on next 10m;

Tablespace created.

SYS@PROD1>alter system set undo_tablespace=UNDOTBS2;

System altered.

SYS@PROD1>show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2

--删除已经无用的undo表空间UNDOTBS1
SYS@PROD1>drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

1.3 设置UNDO数据保留期限及强制保留

1)更改undo表空间，保存2小时
SYS@PROD1>show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SYS@PROD1>alter system set undo_retention=7200;

System altered.

SYS@PROD1>show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 7200
undo_tablespace string UNDOTBS2

2) 查看目前undo表空间的保留模式(默认为非强制保留)
SYS@PROD1>select tablespace_name,retention from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME RETENTION
------------------------------ -----------
UNDOTBS2 NOGUARANTEE

3) 打开undo表空间的确保保留期(guarantee以后，就要确保7200的数据必须在undo中)
SYS@PROD1>alter tablespace undotbs2 retention guarantee;

Tablespace altered.

SYS@PROD1>select tablespace_name,retention from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME RETENTION
------------------------------ -----------
UNDOTBS2 GUARANTEE

SYS@PROD1>alter tablespace undotbs2 retention noguarantee;

Tablespace altered.

SYS@PROD1>select tablespace_name,retention from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME RETENTION
------------------------------ -----------
UNDOTBS2 NOGUARANTEE

注：确保保留期的开启与关闭，只适合undo表空间
SYS@PROD1>alter tablespace example retention guarantee;
alter tablespace example retention guarantee
*
ERROR at line 1:
ORA-30044: 'Retention' can only specified for undo tablespace

1.4 常用的查询语句
SYS@PROD1>select status,count(*) from dba_undo_extents group by status;

STATUS COUNT(*)
--------- ----------
UNEXPIRED 51
EXPIRED 7

查看undo表空间的名称
SYS@PROD1>show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 7200
undo_tablespace string UNDOTBS2

SYS@PROD1>select tablespace_name,contents from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME CONTENTS
------------------------------ ---------
UNDOTBS2 UNDO

查看undo表空间大小
SYS@PROD1>select tablespace_name,sum(bytes)/1024/1024 mb from dba_data_files
2 where tablespace_name='UNDOTBS2'
3 group by tablespace_name;

TABLESPACE_NAME MB
------------------------------ ----------
UNDOTBS2 100

查看undo表空间使用情况
SYS@PROD1>set linesize 100;
SYS@PROD1>select owner,segment_name,bytes/1024/1024 mb from dba_segments
2 where tablespace_name='UNDOTBS2';

OWNER SEGMENT_NAME MB
------------------------------ -------------------------------------------------- ----------
SYS _SYSSMU20_3531206572$ .4375
SYS _SYSSMU19_2733375791$ .3125
SYS _SYSSMU18_1890620786$ .625
SYS _SYSSMU17_557643568$ .3125
SYS _SYSSMU16_219122963$ .4375
SYS _SYSSMU15_914861755$ .375
SYS _SYSSMU14_2992680802$ .5
SYS _SYSSMU13_749134222$ .3125
SYS _SYSSMU12_2714715247$ .375
SYS _SYSSMU11_266491658$ .3125

5) 哪些会话的事务占用了undo表空间
SYS@PROD1>select s.sid,s.serial#,s.sql_id,v.usn,segment_name,r.status,v.rssize/1024/1024 m
2 from dba_rollback_segs r,v$rollstat v,v$transaction t,v$session s
3 where r.segment_id=v.usn
4 and v.usn=t.xidusn and t.addr=s.taddr
5 order by segment_name;

no rows selected

6)UNDO表空间的状态
SYS@PROD1>set linesize 200;
SYS@PROD1>select segment_name,tablespace_name,r.status,(next_extent/1024) NEXTEXTENT,max_extents,v.curext CUREXTENT
2 from dba_rollback_segs r,v$rollstat v
3 where r.segment_id=v.usn(+)
4 order by segment_name;

SEGMENT_NAME TABLESPACE_NAME STATUS NEXTEXTENT MAX_EXTENTS CUREXTENT
-------------------------------------------------- ------------------------------ ---------------- ---------- ----------- ----------
SYSTEM SYSTEM ONLINE 56 32765 4
_SYSSMU11_266491658$ UNDOTBS2 ONLINE 64 32765 7
_SYSSMU12_2714715247$ UNDOTBS2 ONLINE 64 32765 11
_SYSSMU13_749134222$ UNDOTBS2 ONLINE 64 32765 14
_SYSSMU14_2992680802$ UNDOTBS2 ONLINE 64 32765 9
_SYSSMU15_914861755$ UNDOTBS2 ONLINE 64 32765 8
_SYSSMU16_219122963$ UNDOTBS2 ONLINE 64 32765 16
_SYSSMU17_557643568$ UNDOTBS2 ONLINE 64 32765 14
_SYSSMU18_1890620786$ UNDOTBS2 ONLINE 64 32765 15
_SYSSMU19_2733375791$ UNDOTBS2 ONLINE 64 32765 8
_SYSSMU20_3531206572$ UNDOTBS2 ONLINE 64 32765 8

11 rows selected.

ITPub博客

UNDO管理

最新文章