前言
一、UNDO表空间管理
1.还原段的作用
(1).实例恢复
(2).读一致性
(3).事务回滚
2.讨论undo_retention参数
这个参数以秒为单位，表示当事务提交或回滚以后，该事务所使用的undo块里的数据需要保留多长时间。
当保留的时间超过undo_retention,该undo块才能够被其他事务覆盖。
3.查看undo参数
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
4.更改参数undo_retention的值
SQL> alter system set undo_retention = 1200;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 1200
undo_tablespace string UNDOTBS1
SQL>
注意：如果保留时间过长，而且修改数据的事务很多，
则往往造成还原表空间的不足，所以根据业务需要和还原表空间的大小做出判断。
5.通过数据字典v$parameter查询undo_retention值
select name,value from v$parameter where name = 'undo_retention';
6.修改还原表空间
alter system set undo_tablespace=undo_tablespace_name;
7.创建还原表空间
create undo tablespace mlbdb1_undo01
datafile '/oradata01/mlbdb1/mlbdb1_undo01.dbf'
size 500m
autoextend on;
8.查询当前undo表空间
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 1200
undo_tablespace string UNDOTBS1
SELECT t.tablespace_name, sum(bytes / 1024 / 1024)
FROM dba_data_files t
WHERE t.tablespace_name = 'UNDOTBS1'
GROUP BY tablespace_name;
9.查看还原表空间的信息
SELECT t.tablespace_name,
t.extent_management,
t.contents,
t.logging,
t.status
FROM dba_tablespaces t
WHERE t.contents = 'UNDO';
10.查看关于还原表空间的数据文件信息,默认为自动扩展
SELECT T.file_name,
T.tablespace_name,
T.bytes / 1024 / 1024 MB,
T.autoextensible
FROM dba_data_files t;
二、维护还原表空间
1.重命名还原表空间
alter tablespace UNDOTBS1 rename to MLBDB1_UNDOTBS1;
alter tablespace MLBDB1_UNDOTBS1 rename to UNDOTBS1;
2.向还原表空间中添加数据文件
alter tablespace MLBDB1_UNDO01 add datafile '/oradata01/mlbdb1/mlbdb1_undo02.dbf' size 500m;
SELECT T.file_name,
T.tablespace_name,
T.bytes / 1024 / 1024 MB,
T.autoextensible
FROM dba_data_files t;
3.将数据文件修改为自动扩展方式
alter database datafile '/oradata01/mlbdb1/mlbdb1_undo02.dbf' autoextend on;
三、切换还原表空间
1.查看当前还原表空间的信息
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 1200
undo_tablespace string UNDOTBS1
SQL>
2.切换到新建的还原表空间mlbdb1_undo01
alter system set undo_tablespace = mlbdb1_undo01;
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 1200
undo_tablespace string MLBDB1_UNDO01
SQL>
四、删除undo表空间实例
1.创建表空间undotbs2
create undo tablespace untotbs2
datafile '/oradata01/mlbdb1/undotbs02.dbf'
size 10m
autoextend on
maxsize 100m
retention guarantee;
2.启动一个事务
conn scott/oracle
create table t (id number,name varchar2(20));
insert into t values (1,‘lianhuichen’);
3.切换undo表空间
alter system set undo_tablespace=untotbs2;
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 1200
undo_tablespace string UNTOTBS2
SQL>
4.删除旧的表空间mlbdb1_undo01
drop tablespace mlbdb1_undo01;
drop tablespace my_undo including contents;
SQL> drop tablespace mlbdb1_undo01;
drop tablespace mlbdb1_undo01
ORA-30013: 还原表空间 'MLBDB1_UNDO01' 当前正在使用中
5.设置表空间undotbs1属性
设置
alter tablespace undotbs1 retention guarantee;
SQL> alter tablespace undotbs1 retention guarantee;
Tablespace altered
注意：为了避免undo_retention 参数不能保证UNDO信息保留足够的时间，Oracle 只是尽量地保证undo数据块不被覆盖掉，
当空间不够的时候，Oracle还是会将保留时间小于undo_retention的UNDO数据覆盖掉。
设置表空间undotbs1属性retention guarantee
查询表空间undotbs1属性
SELECT b.tablespace_name, AUTOEXTENSIBLE, RETENTION
FROM dba_tablespaces a, dba_data_files b
WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME
AND b.TABLESPACE_NAME = 'UNDOTBS1';
6.dba_undo_extents数据字典
6.1.查询当前的undo表空间
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 1200
undo_tablespace string UNTOTBS2
SQL>
6.2.查询回滚段的名字
create table t (id number,name varchar2(20));
insert into t values (1,'lianhuichen');
SELECT a.username, b.name, c.used_ublk
FROM v$session a, v$rollname b, v$transaction c
WHERE a.saddr = c.ses_addr
AND b.usn = c.xidusn;
6.3.使用dba_undo_extents,根据_SYSSMU27_3471509305$回滚段名字，查询所在的表空间以及区段信息
SELECT t.segment_name, t.tablespace_name, t.extent_id
FROM dba_undo_extents t
WHERE t.segment_name = '_SYSSMU27_3471509305$';
