13.Oracle Undo表空间管理

前言

一、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$';