[转载]oracle回滚段的一些常用sql

原文地址：oracle回滚段的一些常用sql作者：人生笑笑

关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,
       (SELECT tablespace_name, SUM(bytes_cached) bytes
          FROM v$temp_extent_pool
         GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = t.tablespace_name(+)
   AND d.extent_management LIKE 'LOCAL'
   AND d.contents LIKE 'TEMPORARY'
ORDER BY "Used %" DESC