第1步修改undo表空间大小
create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/ocp/undotbs2.dbf' size 10M;alter system set undo_tablespace=undotbs2;
alter system set undo_retention=2 scope=both;
第2步、session1: 目标是让b表报快照过旧的报错
conn gyj/gyj
create table a (id int,cc varchar2(10));
insert into a values(1,'hello');
commit;
create table b(id int,cc varchar2(10));
insert into b values(10,'AAAAAA');
commit;
select * from a;
select * from b;
var x refcursor;
exec open :x for select * from b;
第3步、session2:修改b表,字段cc前镜像"OK"保存在UDNO段中
update b set cc='BBBBBB' where id= 10;
commit;
第4步、session 3:该条语句就是刷新缓存
conn / as sysdba
SQL> alter session set events = 'immediate trace name flush_cache'; --9i提供强制刷缓存
(alter system flush buffer_cache;--10g提供的一种刷缓存方法)
第5步、 session2: 在A表上行大的事务,多运行几次以确保,回滚段被覆盖
begin
for i in 1..20000 loop
update a set cc='HELLOWWWW';
commit;
end loop;
end;
/
第6步、session 1:在B表上执行查询(第一步的查询)
SQL> print :x
ERROR:
ORA-01555: snapshot too old: rollback segment number 21 with name "_SYSSMU21$" too small
**********本博客所有内容均为原创,如有转载请注明作者和出处!!!**********
Name: guoyJoe
QQ: 252803295
Email: oracledba_cn@hotmail.com
Blog: http://blog.csdn.net/guoyJoe
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM: http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________
加群验证问题:哪些SGA结构是必需的,哪些是可选的?否则拒绝申请!!!
答案在:http://blog.csdn.net/guoyjoe/article/details/8624392
Oracle@Paradise 总群:127149411
Oracle@Paradise No.1群:177089463(已满)
Oracle@Paradise No.2群:121341761
Oracle@Paradise No.3群:140856036
转载于:https://blog.51cto.com/guoyjoe/1429073