undo还原数据查询
查询undo表空间的状态，方便进行日常管理

#查看undo数据统计状态
SYS@PROD> SELECT status, COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY status;

STATUS COUNT(*)
--------- ----------
UNEXPIRED 186
EXPIRED 10

SYS@PROD>

#查看undo表空间的名称，保存时间等参数
SYS@PROD> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 7200
undo_tablespace string UNDOTBS2
SYS@PROD>

#查看undo表空间的名称
SYS@PROD> SELECT tablespace_name, contents FROM dba_tablespaces WHERE contents = 'UNDO';

TABLESPACE_NAME CONTENTS
--------------- ---------
UNDOTBS1 UNDO
UNDOTBS2 UNDO

SYS@PROD>

#查看undo表空间大小
SYS@PROD> SELECT tablespace_name, SUM(bytes) / 1024 / 1024 mb
2 FROM dba_data_files
3 WHERE tablespace_name = 'UNDOTBS2'
4 GROUP BY tablespace_name;

TABLESPACE_NAME MB
--------------- ----------
UNDOTBS2 100

SYS@PROD>

#查看undo表空间使用情况
SYS@PROD> SELECT owner, segment_name, bytes / 1024 / 1024 mb
2 FROM dba_segments
3 WHERE tablespace_name = 'UNDOTBS2';

OWNER SEGMENT_NAME MB
------------------------------ ------------------------------ ----------
SYS _SYSSMU11_4271112908$ .125
SYS _SYSSMU12_4158484236$ .125
SYS _SYSSMU13_179378818$ .125
SYS _SYSSMU14_2272166171$ .125
SYS _SYSSMU15_1582425715$ .625
SYS _SYSSMU16_1838716447$ .125
SYS _SYSSMU17_1661673412$ .125
SYS _SYSSMU18_3870982799$ .125
SYS _SYSSMU19_2375724379$ .375
SYS _SYSSMU20_4278160693$ .125

10 rows selected.

SYS@PROD>

#查看占用undo的会话事务
SYS@PROD> SELECT s.sid,
2 s.serial#,
3 s.sql_id,
4 v.usn,
5 segment_name,
6 r.status,
7 v.rssize / 1024 / 1024 mb
8 FROM dba_rollback_segs r, v$rollstat v, v$transaction t, v$session s
9 WHERE r.segment_id = v.usn
10 AND v.usn = t.xidusn
11 AND t.addr = s.taddr
12 ORDER BY segment_name;

#查看undo表空间段的状态
SYS@PROD> SELECT segment_name,
2 tablespace_name,
3 r.status,
4 (next_extent / 1024) NextExtent,
5 max_extents,
6 v.curext CurExtent
7 FROM dba_rollback_segs r, v$rollstat v
8 WHERE r.segment_id = v.usn(+)
9 ORDER BY segment_name;

SEGMENT_NAME TABLESPACE_NAME STATUS NEXTEXTENT MAX_EXTENTS CUREXTENT
------------------------------ ------------------------------ ---------------- ---------- ----------- ----------
SYSTEM SYSTEM ONLINE 56 32765 1
_SYSSMU10_3112394656$ UNDOTBS1 OFFLINE 64 32765
_SYSSMU11_4271112908$ UNDOTBS2 ONLINE 64 32765 0
_SYSSMU12_4158484236$ UNDOTBS2 ONLINE 64 32765 0
_SYSSMU13_179378818$ UNDOTBS2 ONLINE 64 32765 0
_SYSSMU14_2272166171$ UNDOTBS2 ONLINE 64 32765 0
_SYSSMU15_1582425715$ UNDOTBS2 ONLINE 64 32765 8
_SYSSMU16_1838716447$ UNDOTBS2 ONLINE 64 32765 0
_SYSSMU17_1661673412$ UNDOTBS2 ONLINE 64 32765 0
_SYSSMU18_3870982799$ UNDOTBS2 ONLINE 64 32765 0
_SYSSMU19_2375724379$ UNDOTBS2 ONLINE 64 32765 4

SEGMENT_NAME TABLESPACE_NAME STATUS NEXTEXTENT MAX_EXTENTS CUREXTENT
------------------------------ ------------------------------ ---------------- ---------- ----------- ----------
_SYSSMU1_3514849120$ UNDOTBS1 OFFLINE 64 32765
_SYSSMU20_4278160693$ UNDOTBS2 ONLINE 64 32765 0
_SYSSMU2_3529848285$ UNDOTBS1 OFFLINE 64 32765
_SYSSMU3_2308188191$ UNDOTBS1 OFFLINE 64 32765
_SYSSMU4_1612181377$ UNDOTBS1 OFFLINE 64 32765
_SYSSMU5_2127501272$ UNDOTBS1 OFFLINE 64 32765
_SYSSMU6_692380719$ UNDOTBS1 OFFLINE 64 32765
_SYSSMU7_2536238160$ UNDOTBS1 OFFLINE 64 32765
_SYSSMU8_939244055$ UNDOTBS1 OFFLINE 64 32765
_SYSSMU9_636711886$ UNDOTBS1 OFFLINE 64 32765

21 rows selected.

SYS@PROD>

ITPub博客

undo还原数据查询

最新文章