Undo自动管理与手动管理
undo段自动管理
SQL> show parameter undo_management
将undo段改为手工管理
SQL> alter system set undo_management=manual scope=spfile;
SQL> startup force;
SQL> show parameter undo
SQL> select * from v$rollname;
SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/undotbs2.dbf' size 5m;
SQL> create rollback segment u1 tablespace undotbs2;
SQL> alter rollback segment u1 online;
SQL> select * from v$rollname;
SQL> set transaction use rollback segment u1;
监视还原
空间不足
SQL> create table big_t as select * from dba_objects;
SQL> insert into big_t select * from big_t;
/
/
SQL> commit;
SQL> select name from v$tablespace;
SQL> alter system set undo_tablespace='UNDOTBS2';
SQL> show user
SQL> delete big_t;
快照太旧
快照太旧错误实验：
当undo空间不足的时候,会覆盖commit事务所占用的undo数据,当进行查询的时候,会报ORA-1555,例如：
conn /as sysdba
grant select on dba_objects to scott;
grant select on v_$transaction to scott;
grant select on v_$session to scott;
grant select on v_$mystat to scott;
grant select on v_$database to scott;
将undo表空间的大小resize到30M
create undo tablespace undotbs3 datafile '/u01/app/oracle/oradata/orcl/undotbs3.dbf' size 30M;
alter system set undo_tablespace=undotbs3;
计算一下30M的undo表空间undo块的数量：
select (30*1024*1024)/8192 undo_num from dual;
UNDO_NUM
-----------------
3840
conn scott/tiger
create table t1 as select * from dba_objects;
查看当前系统的scn号：
select current_scn from v$database;
在696246时刻,t1表中的数量为：
select count(*) from t1;
将t1表中的数据删除：
delete t1;--------------先不要提交
查看当前会话的delete操作生成的undo块的数量：
select used_ublk from v$transaction where addr = (select taddr from v$session where sid = (select sid from v$mystat where rownum = 1));
commit;
这个时候,通过闪回查询来查看1127829这一点的数据：
select count(*) from t1 as of scn 1127829;
再开一个会话：
conn scott/tiger
create table t2 as select * from dba_objects;
delete t2;
查看当前会话的delete操作生成的undo块的数量：
select used_ublk from v$transaction where addr = (select taddr from v$session where sid = (select sid from v$mystat where rownum = 1));
commit;
这个时候,通过闪回查询来查看1127829这一点的数据：
先清一下内存：
conn / as sysdba
alter system flush buffer_cache;
alter system flush shared_pool;
查看1127829这一点的数据：
select count(*) from t1 as of scn 1127829;
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 2 with name
"_SYSSMU2_967517682$" too small
报错,由于undo表空间太小,第一个会话中的undo数据被覆盖,所以报快照太旧错误
undo_retention
1、对于UNDO表空间的数据文件属性为autoextensible,则undo_retenion参数必须设置
UNDO信息将至少保留至undo_retention参数设定的值内，但UNDO表空间将会自动扩展
2、对于固定UNDO表空间，将会通过表空间的剩余空间来最大限度保留UNDO信息。如果FIXED UNDO
表空间没有对保留时间作GUARANTEE（alter tablespace xxx retention guarantee;），则undo_retention
参数将不会起作用（警告：如果设置UNDO表空间为retention guarantee，则未过期的数据不会被复写，如
果表空间不够则会导致DML操作失败或者transation挂起）
Guaranteeing Undo Retention
SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
观察undo数据块信息，业务高峰期时的数据
alter session set nsl_date_format='yyyy-mm-dd hh24:mi:ss';
select begin_time,end_time,undoblks from v$undostat;
UNDO:
不能直接查询undo块，可以查询CR块
操作块+Undo块=CR块(读一致性块)
grant select on v$database to scott;
conn scott/tiger;
create table t(id number);
insert into d values(1);
select * from d;
select current_scn from v$database;
insert into d values(2);
select * from d;
select * from d as of scn 1604457;
conn / as sysdba;
select tablespace_name, contents from dba_tablespaces;
conn scott/tiger;
create table tt tablespace undotbs1 as select * from dept;--error
conn / as sysdba;
show parameter undo_retention