一次事务hang住的前因后果分析

想测试下ora-01555错误，于是把undo_retention设成100。之后执行
conn scott/tiger
create table t as select * from dept;
不停的insert后发现
SQL> insert into t select * from t;

57344 rows created.

SQL> insert into t select * from t;
事务hang住了

查询v$transaction也是同样hang住

怀疑是undo表空间不足的关系，查询undo的使用率
select tot.tablespace_name,tot.total,free.total,100*(tot.total-free.total)/tot.total||'%' from
(select tablespace_name,sum(bytes) as total from dba_data_files group by tablespace_name) tot,
(select tablespace_name,sum(bytes) as total from dba_free_space group by tablespace_name) free
where tot.tablespace_name=free.tablespace_name
order by tot.tablespace_name
发现undo的使用率才0.6%

那么是哪里出了问题呢？

==================================================
this is the anwser

查看警告日志
Mon Jul 19 14:05:35 2010
Thread 1 cannot allocate new log, sequence 47
All online logs needed archiving
  Current log# 1 seq# 46 mem# 0: D:\ORACLE\ORADATA\FOX\REDO01.LOG

再查看
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Disabled

一切都要浮出水面了

SQL> set linesize 150
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         46   10485760          1 NO  CURRENT                2220444 19-JUL-10
         2          1         44   10485760          1 NO  INACTIVE               1980422 09-JUL-10
         3          1         45   10485760          1 NO  INACTIVE               2000756 12-JUL-10

注意看ARC列，都是NO。设置成归档模式，但是却手工归档，所有日志都需要归档，无法切换日志，导致事务hang住了

执行SQL> alter system switch logfile;也hang住了，证实了这个猜测

对系统做个归档
SQL> archive log all;
2 logs archived.

这下前面hang住的事务都释放了

闹了半天，原来不关undo_retention的事，而是归档日志在作怪。