原文地址: 作者:
关Undo表空间与回滚段的一些查询语句记录
--与回滚段相关的几个系统参数
transactions_per_rollback_segment transactions max_rollback_segments rollback_segments 回滚段类型为public则与该参数无关 --相关的几个视图: DBA_UNDO_EXTENTS GV$UNDOSTAT V$UNDOSTAT DBA_ROLLBACK_SEGS GV$ROLLSTAT V$ROLLNAME V$ROLLSTAT ---分析 UNDO 的使用情况 SELECT TABLESPACE_NAME,STATUS,TRUNC(SUM(BLOCKS) * 8 / 1024) AS "Size M", COUNT(*) Undo_Extent_Num FROM DBA_UNDO_EXTENTS GROUP BY TABLESPACE_NAME, STATUS---监控undo表空间
SELECT BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON" FROM V$UNDOSTAT; --查询是否有回滚段的争用 select * from v$waitstat;SELECT name, waits, gets, waits/gets "Ratio"
FROM v$rollstat a, v$rollname b WHERE a.usn = b.usn; ---查看回滚段的统计信息: SELECT n.name, s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts, s.status FROM v$rollname n, v$rollstat s WHERE n.usn = s.usn; --查看回滚段的使用情况,哪个用户正在使用回滚段的资源: SELECT s.username, u.name FROM v$transaction t, v$rollstat r, v$rollname u, v$session s WHERE s.taddr = t.addr AND t.xidusn = r.usn AND r.usn = u.usn ORDER BY s.username; --查询回滚段的事务回退率 transaction rollbacks/(transaction rollbacks+user commits) select name,value from v$syssstat where name in('user commits','transaction rollbacks'); --查询获取回滚段数据的时候数据缓冲区中copy的数据块的数量 select count(*) from x$bh where state=3; --查询在SGA中回滚段的块的数量USN=n,则回滚段头class为11+2n,回滚段块为12+2n select usn from v$rollstat; select class,count(*) from x$bh where class>10 group by class; --查询数据库的的回滚段情况 select segment_id,segment_name from dba_rollback_segs; --指定使用某个回滚段 set transaction use rollback segment _SYSSMU4$ --查询回滚段在使用,扩展,回缩的时候extent在循环的次数 select usn,wraps from v$rollstat; --查询回滚段收缩的情况 select usn,optsize,shrinks from v$rollstat; --切换undo表空间到新的表空间(注意修改pfile或者spfile参数) alter system set undo_tablespace=UNDOTBS1 scope=both; ---创建undo表空间 CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'E:ORACLEORA92ORCL9UNDOTBS02.DBF' size 11M reuse AUTOEXTEND ON; ---改变(Altering) an Undo Tablespace Adding a datafile Renaming a datafile Bringing a datafile online or taking it offline Beginning or ending an open backup on a datafile ---增加数据文件 ALTER TABLESPACE UNDOTBS2 ADD DATAFILE 'E:ORACLEORA92ORCL9UNDOTBS03.DBF' size 2M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED; --drop undo表空间 DROP TABLESPACE UNDOTBS2; ---INCLUDING CONTENTS. --不指定undo表空间 ALTER SYSTEM SET UNDO_TABLESPACE = ''; --设置retention值: ALTER SYSTEM SET UNDO_RETENTION = 5; ---计算undo表空间的大小(计算机数据可以从V$UNDOSTAT 得到) UndoSpace = UR * UPS + overhead UndoSpace is the number of undo blocks UR is UNDO_RETENTION in seconds UPS is undo blocks for each second overhead is the small overhead for metadata (transaction tables, bitmaps, and so forth) 例如:UNDO_RETENTION 2 hours, transaction rate (UPS) 200 undo blocks for each second, with a 4K block size则undo空间为: (2 * 3600 * 200 * 4K) = 5.8GBs. ---使用Oracle提供的报表查看锁的情况: @$ORACLE_HOMErdbmsadminutllockt.sql ---查询当前某个session的事务所使用的回滚段大小 select b.sid,a.xidusn,a.used_ublk from v$transaction a,v$session b where a.addr=b.taddr; ---如果要介质恢复一个数据文件,先offline再恢复(在归档模式) alter database datafile 'E:ORACLEORA92ORCL9UNDOTBS03.DBF' offline recover datafile 'E:ORACLEORA92ORCL9UNDOTBS03.DBF' -- -----查看Undo的大小 set line 1000; set pages 1000; SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management", to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') "Total Size (M)", to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') "Used (M)", to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') "Free (M)", to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %" FROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY') UNION ALL SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management", to_char(nvl(a.bytes / 1024 / 1024, 0), '99999990.900') "Size (M)", to_char(nvl(t.bytes, 0) / 1024 / 1024, '99999999.999') "Used (M)", to_char((nvl(a.bytes / 1024 / 1024, 0)) - (nvl(t.bytes, 0) / 1024 / 1024), '99999999.999') "Free (M)", to_char(nvl(t.bytes / a.bytes * 100, 0), '990.00') "Used %" FROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_temp_files GROUP BY tablespace_name) a,