测试环境,11G R2 RAC。
有个脚本执行的时候,挂住了。检查一下:
SQL> select * from GV$SESSION_WAIT where sid = 2848 ;
INST_ID SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE WAIT_TIME_MICRO TIME_REMAINING_MICRO TIME_SINCE_LAST_WAIT_MICRO
---------- ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ------------- ----------- ---------------------------------------------------------------- ---------- --------------- ------------------- --------------- -------------------- --------------------------
1 2848 85 gc current request file# 4 0000000000000004 block# 128 0000000000000080 id# 16777253 0000000001000025 3871361733 11 Cluster 0 767 WAITING 766899151 -1 0
2 2848 17 enq: US - contention name|mode 1431502854 0000000055530006 undo segment # 17 0000000000000011 0 0 00 1893977003 0 Other 0 139240 WAITING 139240348081 -1 0
SQL>
第一个事件:
SQL> select name,parameter1,parameter2,parameter3 from v$event_name where name='gc current request';
NAME PARAMETER1 PARAMETER2 PARAMETER3
---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------------------------------------------------------------
gc current request file# block# id#
SQL> SELECT tablespace_name, file_name FROM dba_data_files WHERE file_id = 4;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------------------------------------
UNDOTBS2 +XXX/xxx/undotbs02.dbf
SQL>
第二个也是说明UNDO的争用。
SQL> col file_name format a50
SQL> set lines 100
SQL> select file_name,bytes/1024/1024 from dba_data_files order by file_name;
那就扩空间吧。