【技术文章】ORA-01555快照过旧

select value from table where name=’a’

SELECT *
FROM v$undostat u
WHERE 1=1
and  u.ssolderrcnt > 0

SQL> show parameters UNDO_
NAME TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean     FALSE--用于临时表的UNDO写入临时表空间中，而不是UNDO表空间
undo_management string      AUTO
undo_retention integer     900
undo_tablespace string      APPS_UNDO_TEST

1.
create undo tablespace apps_undo_test
datafile '/usr/local/oracle19c/oradata/ORCL/APPS_UNDO_TEST_TABLESPACE.dbf'--此路径可能和你们测试环境不一致，请用dba_data_files查询应该放置在哪
size 100k--实测最小100多k
autoextend off;--禁止扩展，即使我的undo_retention是900秒，在非自动扩展的undo表空间，undo_retention设置失效


2.
show parameters UNDO_TABLESPACE--事先记录下当前的表空间是什么，方便实验结束恢复
alter system set undo_tablespace =apps_undo_test;--设置为较小的undo表空间


3.
--查看undo数据文件信息
SELECT SUM(bytes) / 1024 / 1024 || 'm'
FROM dba_data_files
WHERE tablespace_name = 'APPS_UNDO_TEST';




4.
创建索引，使得update的时候产生更多的undo,更好模拟快照过旧
Create table dba_objects_test as select * from dba_objects;
Create index dba_objects_test_n1 on dba_objects_test(object_id);
CREATE OR REPLACE FUNCTION sleep_time(p_time IN NUMBER) RETURN NUMBER IS
BEGIN


dbms_lock.sleep(p_time);
RETURN p_time;
END;


一个窗口下执行查询
DECLARE
CURSOR a_cur IS
SELECT *
FROM dba_objects_test t
WHERE 1 = 1
-- AND ROWID = 'AAAWVDAAFAAAAaTAAA'--这个注释去掉或者不去都会产生快照过旧错误，测试的时候请自行替换自己系统的ROWID
AND sleep_time(30) = 30;--模拟长时间运行的SQL，这个游标30秒后返回数据，但取出来的数据还是30秒前的，所以假如你们测试系统不能自己创建undo表空间并切换表空间，可以尝试把这个 AND sleep_time(30) = 30 改成 AND sleep_time(1000) =1000
i NUMBER := 0;
BEGIN
FOR a_rec IN a_cur
LOOP
NULL;
END LOOP;
END;


--另一个窗口下执行修改
DECLARE
g_total_undo_size NUMBER := 0;


CURSOR a_cur IS
SELECT ROWID FROM dba_objects_test;


PROCEDURE print_undo_size IS
l_undo_size NUMBER;
BEGIN
BEGIN
SELECT t.used_ublk * 8 --返回KB
INTO l_undo_size
FROM v$session     s
,v$transaction t
WHERE s.saddr = t.ses_addr
AND s.sid = userenv('SID');
EXCEPTION
WHEN OTHERS THEN
l_undo_size := 0;
END;
g_total_undo_size := g_total_undo_size + l_undo_size;
dbms_output.put_line(l_undo_size);
EXCEPTION


WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;


BEGIN
FOR a_rec IN a_cur
LOOP
COMMIT; --如果去掉这个commit，将会报错ora-01552或ORA-30036因为为了保证能够回滚，原有undo段是不能被覆盖的，而我们undo只有1m,又不能自动扩展
--如果不能保证undo段全部被覆盖，那么不一定能产生快照过旧错误
BEGIN
--打印此事务产生的undo
--  print_undo_size;--commit之后肯定是0，或者自行修改，获取所有的事务的undo
UPDATE dba_objects_test
SET object_id = dbms_random.value
WHERE ROWID = a_rec.rowid;
print_undo_size;


EXCEPTION
WHEN OTHERS THEN
print_undo_size;
dbms_output.put_line(SQLERRM);


RETURN;
END;


END LOOP;
dbms_output.put_line(g_total_undo_size);
END;


5.
实验完毕恢复修改
alter system set undo_tablespace =UNDOTBS1;--恢复原始的undo表空间，原始的表空间是什么请事先在command 中执行，show parameters UNDO_TABLESPACE，如果 恢复不了，按上面的步骤再创建一个自定义的undo表空间。