小工具知识网

当前位置: 首页 > 行业知识 > UNDO表空间使用情况分析

行业知识

UNDO表空间使用情况分析

2026-11-18 14:59:43 来源:互联网转载

UNDO表空间使用情况分析

  • 用量分析
  • 扩容UNDO
  • 追踪���户SQL

    用量分析

    检查表空间用量:

    ()
    select a.tablespace_name, round((a.max - (a.alloc - nvl (b.free, 0)))/1024/1024/1024, 2) free_gb, round ((a.alloc - nvl(b.free,0)) / decode (a.max,0,1,a.max) * 100) percent_used, round(a.max/1024/1024/1024,2) max_gb from (select t.tablespace_name, sum(bytes) alloc,   sum(     case       when autoextensible = 'YES' then maxbytes       when autoextensible = 'NO' then bytes     end) max   from dba_data_files f join dba_tablespaces t   on (f.tablespace_name = t.tablespace_name and t.contents in ('PERMANENT','UNDO'))   group by t.tablespace_name) a,   (select ts.name tablespace_name,   sum (fs.blocks) * ts.blocksize free   from dba_lmt_free_space fs, sys.ts$ ts   where ts.ts# = fs.tablespace_id group by ts.name, ts.blocksize) b where a.tablespace_name = b.tablespace_name(+);

    扩容UNDO

    检查UNDO表空间管理模式:

    show parameter undo

    如果undo_management值为AUTO或者NULL,表示启用了自动undo管理;undo_retention表示undo数据保留的最短时间(秒)。

    ()

    查看UNDO表空间文件以及是否自动扩展:

    set lines 200 col file_name for a80 select file_name,tablespace_name,bytes/1024/1024 total_mb,maxbytes/1024/1024 max_mb,autoextensible from dba_data_files where tablespace_name like 'UNDOTBS%';

    扩容undo表空间:

    alter tablespace undotbs1 add datafile;

    追踪用户SQL

    统计当前正在使用回滚段的用户SQL:

    set lines 200 col username for a20 col program for a20 col machine for a30 select s.username,s.sql_id,s.sql_hash_value, substr(s.program,1,25) program,s.machine, u.name as rollback_segment_name, r.rssize/1024/1024 as rollback_segment_mb, r.status 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,r.rssize desc;

    查看占用回滚段最多的SQL文本:

    select sql_fulltext from v$sqlarea where sql_id='xxxxxx';

上一篇:dell2950服务器怎么调试?

下一篇:网签备案合同日期怎么查看