management undo tablespaces
UNDO的作用：
1 回滚事务
2 恢复数据
3提供一致性读
4 使用FLASHBACK 恢复逻辑错误
关于UNDO的管理，一般自动
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
UNDO_RETENTION是UNDO数据保存的时间
UNDO_TABLESPACE是数据库当前使用的UNDO表空间
在设置UNDO大小时，要考虑两方面的数据：
1. 最长的查询需要多久：
2你需要最长闪回的时间
UNDO表空间的创建一个是在创建数据库时创建，一个临时添加的
比如说：
CREATE UNDO TABLESPACE DATAFILE ” SIZE 大小 REUSE AUTOEXTEND ON
你可以创建多个UNDO表空间，但是某个时间内只有一个是活动的
向UNDO表空间里添加数据文件
ALTER TABLESPACE undotbs_01
ADD DATAFILE ‘/u01/oracle/rbdb1/undo0102.dbf’ AUTOEXTEND ON NEXT 1M
MAXSIZE UNLIMITED;
You can use the ALTER DATABASE...DATAFILE statement to resize or extend a datafile.
删除UNDO表空间
DROP TABLESPACE undotbs_01;
切换UNDO表空间：
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;
有关UNDO的表空间：
View Description
V$UNDOSTAT Contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. The database also uses this information to help tune undo usage in the system. This view is meaningful only in automatic undo management mode.
V$ROLLSTAT For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace
V$TRANSACTION Contains undo segment information
DBA_UNDO_EXTENTS Shows the status and size of each extent in the undo tablespace.
DBA_HIST_UNDOSTAT
SELECT TO_CHAR(BEGIN_TIME, ‘MM/DD/YYYY HH24:MI:SS’) BEGIN_TIME,
TO_CHAR(END_TIME, ‘MM/DD/YYYY HH24:MI:SS’) END_TIME,
UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS “MAXCON”
FROM v$UNDOSTAT WHERE rownum <= 144;
BEGIN_TIME END_TIME UNDOTSN UNDOBLKS TXNCOUNT MAXCON
——————- ——————- ———- ———- ———- ———-
10/28/2004 14:25:12 10/28/2004 14:32:17 8 74 12071108 3
10/28/2004 14:15:12 10/28/2004 14:25:12 8 49 12070698 2
10/28/2004 14:05:12 10/28/2004 14:15:12 8 125 12070220 1
10/28/2004 13:55:12 10/28/2004 14:05:12 8 99 12066511 3
…
10/27/2004 14:45:12 10/27/2004 14:55:12 8 15 11831676 1
10/27/2004 14:35:12 10/27/2004 14:45:12 8 154 11831165 2
144 rows selected.
