ORACLE【Undo Tablespace】

 --查看unco tablespace使用情况的语句
SELECT B.TABLESPACE_NAME,
       B.TOTAL_MB - NVL(A.FREE_MB, 0) USED_MB,
       B.TOTAL_MB TOTAL_MB,
       ROUND((B.TOTAL_MB - NVL(A.FREE_MB, 0)) / B.TOTAL_MB * 100, 2) USED_PERCENT,
       B.TOTAL_ALLOCATE_MB + NVL(C.NONAUTO, 0) MAX_ALLOCATE_MB,
       NVL(B.TOTAL_ALLOCATE_MB, 0) AUTO_MB,
       NVL(C.NONAUTO, 0) NONAUTO_MB
  FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1048576) FREE_MB
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / 1048576) TOTAL_MB,
               ROUND(SUM(MAXBYTES) / 1048576) TOTAL_ALLOCATE_MB
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) B,
       (SELECT SUM(BYTES) / 1048576 NONAUTO, TABLESPACE_NAME
          FROM DBA_DATA_FILES
         WHERE MAXBYTES = '0'
         GROUP BY TABLESPACE_NAME) C
 WHERE A.TABLESPACE_NAME(+) = B.TABLESPACE_NAME
   AND C.TABLESPACE_NAME(+) = B.TABLESPACE_NAME
   AND B.TABLESPACE_NAME LIKE 'UNDO%'
 ORDER BY USED_PERCENT DESC;

 

--看undo真实使用情况
select ((select (nvl(sum(bytes), 0))
           from dba_undo_extents
          where tablespace_name like 'UNDO%'
            and status in ('ACTIVE', 'UNEXPIRED')) * 100) /
       (select sum(bytes)
          from dba_data_files
         where tablespace_name like 'UNDO%') "PCT_INUSE"
  from dual;

 

--查看是否过期的部分
SELECT DISTINCT STATUS, SUM(BYTES) / 1024 / 1024, COUNT(*)
  FROM DBA_UNDO_EXTENTS
 GROUP BY STATUS;

 

--看自动调整成多少
SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
       TUNED_UNDORETENTION
  FROM V$UNDOSTAT;

 

每个undo extent可以有三种状态: 

active:有活动事务在此extent上 

expired:已结束的事务，undo 信息超过undo_retention时间限制 

unexpired:已经结束的事务，undo 信息未达到undo_retention时间限制 

 

过程

1.先去搜索拥有非active extent的undo segment，如果没有发现，那么会去创建新的undo segment,如果空间不够不能创建，将返回错误。

2.如果有一个undo segment被选中，但是其中free的undo block并不足以存储该事务的undo 信息，那么它将尝试创建extent，如果没有空间，那么将会进入下一步。 

3.如果创建新extent失败，它将会搜索其他undo segment中expired extent并重用。 

4.如果其他undo segment中没有expired extent可使用，那么它会继续搜索其他undo segment中unexpired extent并重用。 

5.如果经过以上尝试还没有可用空间，将会返回错误。

 

--查看回滚段的使用情况，哪个用户正在使用回滚段的资源，如果有用户最好更换时间（特别是生产环境）
select s.username, u.name
  from v$transaction t,
       v$rollstat    r,
       v$rollname    u,
       v$session     s
           　　where s.taddr = t.addr and t.xidusn = r.usn and r.usn = u.usn
 order by s.username;

 

--检查UNDO Segment状态
select usn,
       xacts,status,
       rssize / 1024 / 1024 / 1024,
       hwmsize / 1024 / 1024 / 1024,
       shrinks
  from v$rollstat
 order by rssize;

 

-- 删除原有的UNDO表空间；
drop tablespace undotbs1 including contents;

 

--查看undo数据文件是否自动扩展，undo表空间是否处于gurantee状态。 
select AUTOEXTENSIBLE, RETENTION
  from dba_tablespaces, dba_data_files
 where dba_data_files.TABLESPACE_NAME = dba_tablespaces.TABLESPACE_NAME
   and dba_data_files.TABLESPACE_NAME like 'UNDOTB%'

 

SQL> Alter tablespace undotbs1 retention guarantee;

--如果想禁止undo 表空间retention guarantee
SQL> Alter tablespace undotbs1 retention noguarantee;

 

1. 增加数据文件
ALTER TABLESPACE undotbs_01
ADD DATAFILE '/u01/oracle/rbdb1/undo0102.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

 

2. 重命名数据文件

ALTER TABLESPACE undotbs_01 RENAME DATAFILE '/u01/oracle/rbdb1/undo0102.dbf' TO '/u01/oracle/rbdb1/undo0101.dbf';

 

3. 使数据文件online或者offline
ALTER TABLESPACE undotbs_01 online|offline;

 

4. 开始或者结束一个联机备份
ALTER TABLESPACE undotbs_01 BEGIN|END BACKUP;

 

5. 删除undo tablespace
Drop tablespace undotbs_01;

Drop undo表空间的时候必须是在未使用的情况下才能进行。如果undo表空间正在使用（例如事务失败，但是还没有恢复成功），那么drop表空间命令将失败。在drop表空间的时候可以使用including contents。


6. 切换undo tablespace
切换undo表空间有两种方式：

1. 使用命令动态修改；

2. 修改初始化参数后重新启动数据库。

 

Alter system set undo_tablespace=undotbs1;

当切换命令完成后，所有的事务就会在新的回滚表空间内进行。

 

以下几种情况会导致切换命令失败：

1.  表空间不存在；
2.. 表空间不是一个回滚段表空间；
3.  表空间已经被另一个实例使用。



注意：切换的操作不等待旧undo表空间的事务提交。如果旧undo表空间有事务未提交，那么旧的undo表空间进入pending offline状态，在这种模式下所有的事务能够继续进行，但是undo表空间不能被其他实例使用，也不能被删除，直到所有的事务提交后， undo表空间才进入offline模式。

 

7. 设置undo_retention
dba可以设置undo_retention初始化参数指定undo回滚表空间保留undo信息的时间。在设置好这个参数时，系统会保留undo信息在指定的时间断后才收回这个空间。
一般情况下，系统会保留undo信息到指定的时间后才回收空间，但是，如果系统
存在大量的事务，也会将未到期的undo空间回收，以供使用。


8. Undo 表空间大小的设计规范的计算公式
Undospace = UR * UPS *db_block_size+ 冗余量
UR： 表示在undo中保持的最长时间数（秒），由数据库参数UNDO_RETENTION值决定。
UPS：表示在undo中，每秒产生的数据库块数量。


和undo有关的动态性能视图v$undostat 包含undo的统计信息。使用这张视图可以估计系统当前所需的undo大小。
v$rollstat 是undo模式的视图。是undo表空间的undo segments的统计信息
v$transaction 包含undo segments的信息。
dba_undo_extents 包含undo表空间中每一个范围的提交时间。

 

和回滚段相关的性能视图


DBA_ROLLBACK_GEGS 描述回滚段的信息，包含回滚段的名字和表空间；
DBA_SEGMENTS 描述回滚段的附加信息；
V$ROLLNAME 列出在线回滚段的名称
V$ROLLSTAT 包含回滚段的统计信息
V$TRANSACTION 包含撤销的统计信息

 

select * from v$rollstat;
select * from dba_rollback_segs;
alter rollbck segment xxx shrink to xxM;

  

在undo tablespace中不能创建数据库对象，这是因为这个表空间是为数据库recover而准备的。