ORA-1555 の再現ケース
事前準備
自動拡張 OFF の10MB のUNDO表領域を予め作成しておく
データ作成
SQL> drop table t1 purge; SQL> create table t1 (c1 number(10), c2 char(500)); SQL> insert into t1 select level, 'AAA' from dual connect by level <=10000; SQL> commit; SQL> select segment_name, bytes/1024/1024 MB from user_segments; SEGMENT_NA MB ---------- ---------- T1 6
1つめのセッションから 時間のかかる select 発行
14:57:43 SQL> set time on timing on 14:57:44 SQL> select 14:57:44 2 /*+ LEADING(a b) USE_NL(b) */ 14:57:44 3 a.c1, a.c2 14:57:44 4 from t1 a, t1 b 14:57:44 5 order by a.c1, b.c1 14:57:44 6 ;
select 実行中に2つめのセッションからT1 を更新
SQL> update t1 set c2 = 'XXX'; select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status; commit; select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status; update t1 set c2 = 'YYY'; select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status; commit; select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status; update t1 set c2 = 'ZZZ'; select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status; commit; 10000 rows updated. Elapsed: 00:00:00.74 14:57:56 SQL> STATUS MB --------- ---------- UNEXPIRED 2.375 EXPIRED .125 ACTIVE 6.5 Elapsed: 00:00:00.00 14:57:56 SQL> Commit complete. Elapsed: 00:00:00.00 14:57:56 SQL> STATUS MB --------- ---------- UNEXPIRED 8.6875 EXPIRED .125 ACTIVE .1875 Elapsed: 00:00:00.00 14:57:56 SQL> 10000 rows updated. Elapsed: 00:00:00.87 14:57:57 SQL> STATUS MB --------- ---------- UNEXPIRED 2.375 EXPIRED .0625 ACTIVE 6.5625 Elapsed: 00:00:00.00 14:57:57 SQL> Commit complete. Elapsed: 00:00:00.01 14:57:57 SQL> STATUS MB --------- ---------- UNEXPIRED 8.75 EXPIRED .0625 ACTIVE .1875 Elapsed: 00:00:00.00 14:57:57 SQL> 10000 rows updated. Elapsed: 00:00:00.50 14:57:57 SQL> STATUS MB --------- ---------- UNEXPIRED 2.4375 EXPIRED .0625 ACTIVE 6.5 Elapsed: 00:00:00.00 14:57:57 SQL> Commit complete. Elapsed: 00:00:00.01 14:57:57 SQL>
1つめのセッションにエラーが返る
14:57:43 SQL> set time on timing on 14:57:44 SQL> select 14:57:44 2 /*+ LEADING(a b) USE_NL(b) */ 14:57:44 3 a.c1, a.c2 14:57:44 4 from t1 a, t1 b 14:57:44 5 order by a.c1, b.c1 14:57:44 6 ; from t1 a, t1 b * ERROR at line 4: ORA-01555: snapshot too old: rollback segment number 2 with name "_SYSSMU2_2715918981$" too small Elapsed: 00:00:13.03 14:57:57 SQL> 14:57:57 SQL>
