ORACLE_UNDO表空间的管理总结

undo表空间的管理：1、undo作用2、undo管理模式3、undo参数4、undo表空间的建立删除作用：事务回退、事务恢复、读一致性、闪回数据读一致性：用户看到的所有数据都是别人已经提交的或者是自己操作过但还没提交的事务恢复、前滚：oracle重启时会恢复到没有commit的状态，系统监控进程读取undo表空间段头查找已提交事务，在日志文件中找到记录，然后实例恢复。

事务回退：用户可以用roolback回滚没有commit的数据闪回（flash back）：任何操作都会把数据的操作保存到undo，运用undo表空间的记录，来恢复近期的数据。

9i以后版本才支持管理模式：现在版本一般用自动管理新手最好自动管理show parameter undo_m参数中的undo_management 定义了是否自动管理。

show parameter undo_t查看undo_tablespace参数指定用的是哪个表空间undo表空间中的段分三种：系统段----------系统表空间镜像数据非系统段-----------非系统表空间的镜像数据deferred段---------临时段---任何表空间脱机后，所有变化的数据存入undo临时段，当再次联机时在从undo写入查看段：SQL> select * from v$rollname;USN NAME---------- ------------------------------0 SYSTEM1 _SYSSMU1$2 _SYSSMU2$3 _SYSSMU3$4 _SYSSMU4$5 _SYSSMU5$6 _SYSSMU6$7 _SYSSMU7$8 _SYSSMU8$9 _SYSSMU9$10 _SYSSMU10$其中0为系统段，其他为非系统段，分公有还是私有默认都是公有，oracle中至少要有一个非系统段。

查看文件状态SQL> select status,enabled from v$datafile;查看deferred段select segment_name,segment_type from dba_segments where segment_type like '%defe%' 只有某个表空间脱机时，就会产生deferred段，一旦online后deferred段消失参数：SQL> show parameter undo_NAME TYPE VALUE------------------------------------ ----------- ------------------------------undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1parameter_undo_tablespaceundo表空间设置，默认存放1个系统段，10个非系统段，还有表空间在offline时system 表空间去生成一个类型为deferred rollback段，online时自动消失。

parameter_undo_retention 单位是秒设置undo保存数据的最多时间，默认是900秒（15分钟）；undo表空间的建立删除：在建数据库时：create databaseundo tablespace <> datafile <> size <>;以后建立时：create smallfile|bigfile undo tablespace <> datafile <> size <> <>;例子：create undo tablespace undo datafile '/u01/oracle/undo.dbf' size 10m autoextend on;不过这个表空间没有用删除：undo_tablespace 参数指定的表空间是不能删除的。

要想删除必须先修改undo_tablespace参数；且undo中的保留的段必须脱机，然后才能删除（需要重启）；更改undo表空间create undo tablespace undo datafile '/u01/oracle/undo.dbf' size 10m autoextend on;alter system set undo_tablespace =undo;默认时参数文件也已经修改，不需要改参数文件undo大小估算：undo space =（undo_retention *(undo blocks per second * db block size )）+db block_size查询所需参数：undo blocks per second这个值需要查询v$undostat----------undoblksselect sum(undoblks)/sum((end_time-begin_time)*10800) from v$undostat;SUM(UNDOBLKS)/SUM((END_TIME-BEGIN_TIME)*10800)----------------------------------------------.190424863SQL> show parameter undo_reNAME TYPE VALUE------------------------------------ ----------- ------------------------------undo_retention integer 900SQL> show parameter block_sNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_block_size integer 8192最后用着几个值来算。

也可以用下面这一个语句来计算：select (rt*(ups*bl)+bl)/1024/1024/1024 GB from (select value rt from v$parameter where name =’undo_retention’),(select (sum(undoblks)/sum((end_time-begin_time)*10800)) ups from v$undostat),(select value bl from v$parameter where name=’db_block_size’)查看当前undo表空间已分配的大小undo表空间默认的id为2查看区的分配已经分配到哪里select max(block_id) from dba_extents where file_id=2;MAX(BLOCK_ID)-------------2185结果为一个数字然后乘以块的大小SQL> show parameter db_bNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_block_buffers integer 0db_block_checking string FALSEdb_block_checksum string TRUEdb_block_size integer 8192SQL> select 2185*8/1024 from dual;2185*8/1024-----------17.0703125减小undo表空间alter database datafile 2 resize 300m;应大于默认的250m；undo的查询视图：v$rollstat v$rollname dba_segments dba_rollback_segs v$transactionv$rollname:当前正在用的dba_rollback_segs：系统中所有的查询回滚段的增用情况：命中率计算，自动管理不需考虑select ,b.gets,b.waits, (b.waits/b.gets)*100 ratio from v$rollname a,v$rollstat b where n=n;若ratio 有大于2的代表有争用现象，需要增加undo的段，若是自动管理则问题不大。

事务回退只能从point s3但之前的状态，而不能以后的时间，事务提交DML---- ---------人为commitDDL -------------自动结束事务如果执行了insert update delete 后马上执行了DDL DCL 则insert update 和delete也会自动提交事务开始-------------》是在一个会话产生的事务结束-------------》是在当前会话下碰到自动或手工结束事务操作就结束已经存在的事务了闪回数据delete empcommit查询1秒前的数据select * from emp as of timestamp sysdate-1/1400;insert into emp select * from emp as of timestamp sysdate-1/1400;undo备份热备或rman备份alter tablespace undo start backup;alter tablespace undo end backup;介质恢复则日志必须完整undo损坏处理1.系统中有其他的undo则修改为其他undo 把损坏的脱机如果有人正在用undo则此方法不能成功SQL> alter system set undo_tablespace=undo scope=spfile;SQL> alter database datafile 2 offline;startup2.系统中没有其他的undo 则使用隐藏参数，让undo脱机，打开数据库，删除undo建立undo查询隐藏参数：select ksppinm from x$ksppi where ksppinm like '%roll%'KSPPINM--------------------------------------------------------------------------------transactions_per_rollback_segment rollback_segments_rollback_segment_initial_rollback_segment_count_offline_rollback_segments_corrupted_rollback_segments_cleanup_rollback_entries_rollback_stopatfast_start_parallel_rollback_mv_rolling_inv10 rows selected.当其中的_offline_rollback_segments 为true时就可以了SQL> alter system set "_offline_rollback_segments"=true scope=spfile;shutdown immediatestartup mount把undo改为手动SQL> alter system set undo_management=manual scope=spfile;shutdown immediatestartup mount查看数据文件状态：SQL> select name,status,enabled from v$datafile;SQL> select segment_name,status from dba_rollback_segs;SEGMENT_NAME STATUS------------------------------ ----------------SYSTEM ONLINE_SYSSMU1$ NEEDS RECOVERY_SYSSMU2$ NEEDS RECOVERY_SYSSMU3$ NEEDS RECOVERY_SYSSMU4$ NEEDS RECOVERY_SYSSMU5$ NEEDS RECOVERY_SYSSMU6$ NEEDS RECOVERY_SYSSMU7$ NEEDS RECOVERY_SYSSMU8$ NEEDS RECOVERY_SYSSMU9$ NEEDS RECOVERY_SYSSMU10$ NEEDS RECOVERY介质恢复shutdown immediatestartup mount去掉隐藏参数：SQL> alter system reset "_offline_rollback_segments" scope=spfile sid='*';注意：undo要备份undo最好有一个备用的表空间介质恢复media recoverrecover tablespace undo。