Oracle ora-01555解决方法
发现大量ORA-01555发现大量ORA-01555
ORA-01555 caused by SQL statement below (SQL>
ORA-01555 caused by SQL statement below (SQL>
ORA-01555 caused by SQL statement below (SQL> 一般Alert日志里面发现这种，就是回滚段太小了，一般不用管，如果确实影响到业务，可以杀一些占用资源搞的回滚或查询进程。
1.查看大事务 gv$transaction
SQL> select inst_id,addr,used_urec,used_ublk from gv$transaction;
SQL> select start_date,inst_id,addr,used_urec,used_ublk from gv$transaction;
START_DATEINST_ID ADDRUSED_UREC USED_UBLK
----------------- ---------- ---------------- ---------- ----------
20121019 06:08:532 07000001FCC35D0811
20121019 07:20:152 07000001FB884A7891
20121019 07:20:152 07000001FDF215A061
20121019 07:20:152 07000001FDF7681891
20121019 06:42:421 07000001FDF06398101
20121019 07:16:531 07000001FB8A166061
20121019 06:40:281 07000001FCC6EC3861
20121019 07:20:151 07000001F9B42A6891
20121019 05:13:541 07000001FDF79338 42976487 693162
2.根据上面gv$transaction中ADDR地址与v$session中的taddr字段匹配查找sid,serial#
SQL> select sql_child_number,
sid,
sql_id,last_call_et,
blocking_session,
blocking_instance,
state,
event,
p1,
p2,
seconds_in_wait
from v$session
where
taddr='07000001FDF79338';
3.根据sid可以查找spid
查询得到该session对应的OS进程号:
SQL> select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=&sid);
4.监控undo 使用情况
CLEAR COLUMNS BREAKS COMPUTES
COLUMN tablespace FORMAT a20HEADING 'Tablespace Name'
COLUMN>
COLUMN> COLUMN status FORMAT a12HEADING 'Status'
COLUMN pctFORMAT a8HEADING 'Used Pct'
CLEAR COMPUTES BREAKS
BREAK ON tablespace on report
COMPUTE sum LABEL "Total: " OF> select a.tablespace_name tablespace
,b.status status
,b.bytes >
,a.bytes > ,round(100*(b.bytes/a.bytes),2)||'%' pct
from
,sum(bytes)/1024/1024 bytes
from dba_data_files
group by tablespace_name) a,
,sum(bytes)/1024/1024 bytes
from dba_undo_extents
group by tablespace_name,status) b
where a.tablespace_name=b.tablespace_name
order by 1,2;
SQL> select tablespace_name,status,sum(bytes)/1024/1024/1024 from dba_undo_extents group by tablespace_name,status order by 1,3;
5.可以考虑使用
kill -9 spid
或alter system kill session '986,16161';的方式
6.检查会话是否已经被杀掉
select a.sid,a.serial#,b.spid,a.status from v$session a,v$process b where a.paddr=b.addr and a.taddr='07000001FDF79338';
7.事务回滚监控
SQL> select * from v$fast_start_transactions;
USN SLT SEQ STATEUNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XIDPXIDRCVSERVERS
---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ----------
1038 4314095 RECOVERING 50906028674200 0 000A00260041D3EF 000000000000000032
SQL> select file_id,block_id from dba_rollback_segs where SEGMENT_ID=10;
FILE_ID BLOCK_ID
---------- ----------
2 153
页:
[1]