BOSCO 蜻蜓点水,一点一滴

On a new journey……

Oracle UNDO表空间日常维护查看操作

 

undo表空间中常用的操作:

(1)查看undo表空间undotbs1的属性:
SQL> select b.tablespace_name,AUTOEXTENSIBLE,RETENTION  from dba_tablespaces a,dba_data_files b
         where a.TABLESPACE_NAME=b.TABLESPACE_NAME and b.TABLESPACE_NAME='UNDOTBS1';

TABLESPACE_NAME  AUT  RETENTION
----------------------     ---   ----------------
UNDOTBS1               NO  NOGUARANTEE

(2)查看各个回退段的使用信息:
select a.name,b.extents,b.rssize,b.writes,b.xacts,b.wraps
from v$rollname a,v$rollstat b where a.usn=b.usn;

(3)确定哪些用户正在使用undo段:
SQL> select a.username,b.name,c.used_ublk from v$session a,v$rollname b,v$transaction c
         where a.saddr=c.ses_addr and b.usn=c.xidusn; 

USERNAME  NAME                USED_UBLK
----------      -----------------------          ----------
NDMC       _SYSSMU1_1255220753$       1
NDMC       _SYSSMU5_1255220754$       1

(4)每秒生成的UNDO量,可以通过如下SQL得出:
SQL> SELECT (SUM(undoblks))/ SUM((end_time - begin_time) * 86400) FROM v$undostat;

(SUM(UNDOBLKS))/SUM((END_TIME-BEGIN_TIME)*86400)
--------------------------------------------------------------
          7.97590055
或者可以看下AWR报告中的Undo Statistics部分。

(5)当前undo表空间使用状态:
SQL> SELECT DISTINCT STATUS,SUM(BYTES),COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

STATUS  SUM(BYTES)   COUNT(*)
---------             ----------------   ----------
UNEXPIRED        3225157632   5667
EXPIRED            1063518208   1588
ACTIVE             1048576        1 

(6)查看活动事务v$transaction
SQL> SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK 
         FROM V$SESSION A, V$TRANSACTION B WHERE A.SADDR=B.SES_ADDR;

       SID  USERNAME   XIDUSN    USED_UREC USED_UBLK
---------- ------------ ---------- ---------- ----------
       407   NDMC       15         3        1

SQL> SELECT XID AS "txn_id", XIDUSN AS "undo_seg", USED_UBLK "used_undo_blocks",
                     XIDSLOT AS "slot", XIDSQN AS "seq", STATUS AS "txn_status"
         FROM V$TRANSACTION;

txn_id         undo_seg   used_undo_blocks    slot      seq     txn_status
---------------- ---------- ---------------- ---------- --------- -------------
14001600733A0C00    20           1             22      801395      ACTIVE

 

阅读更多
版权声明:本文为博主hw_libo原创文章,未经博主允许不得转载。 https://blog.csdn.net/HW_LiBo/article/details/6888940
个人分类: Oracle
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭