Oracle-undo

Oracle-undo

查看磁盘空间

undo表空间使用率

select
e.tablespace_name,
max(total) total,
sum(decode(e.status,'ACTIVE',G,0)) active,
sum(decode(e.status,'UNEXPIRED',G,0)) unexpired,
sum(decode(e.status,'EXPIRED',G,0)) expired,
round((sum(decode(e.status,'ACTIVE',G,0))+sum(decode(e.status,'UNEXPIRED',G,0)))/max(total)*100,2) real_use_pct
from
(select tablespace_name,round(sum(bytes)/1024/1024/1024,2) total
from dba_data_files
group by tablespace_name) f,
(SELECT tablespace_name,status,round(SUM(bytes)/1024/1024/1024,2) G
from dba_undo_extents
group by tablespace_name,status) e
where f.tablespace_name=e.tablespace_name
group by e.tablespace_name;

undo_retention 参数修改

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_in_memory_undo boolean FALSE
undo_management string AUTO
undo_retention integer 14400
undo_tablespace string PSAPUNDO

SQL> alter system set undo_retention=10800 scope=both;

System altered.

undo占用语句查询

select distinct  s.machine,s.program,s.sid,round(t.used_ublk*8/1024/1024,2)  undo_GB,used_urec undo_records,s.status,l.sql_text
from v$transaction t,gv$session s,v$sqlstats l
where t.ses_addr=s.saddr
and s.sql_id=l.sql_id(+)
order by undo_GB desc ;

undo表空间扩容

sqlplus -S / as sysdba >undo.log  <<EOF

alter tablespace PSAPUNDO add datafile '/oradata6/EP1/sapdata13/undo.data27' size 30G ;
alter tablespace PSAPUNDO add datafile '/oradata6/EP1/sapdata13/undo.data28' size 30G ;
alter tablespace PSAPUNDO add datafile '/oradata6/EP1/sapdata13/undo.data29' size 30G ;
alter tablespace PSAPUNDO add datafile '/oradata6/EP1/sapdata13/undo.data30' size 30G ;
alter tablespace PSAPUNDO add datafile '/oradata6/EP1/sapdata13/undo.data31' size 30G ;
alter tablespace PSAPUNDO add datafile '/oradata6/EP1/sapdata13/undo.data32' size 30G ;
alter tablespace PSAPUNDO add datafile '/oradata6/EP1/sapdata13/undo.data33' size 30G ;
alter tablespace PSAPUNDO add datafile '/oradata6/EP1/sapdata13/undo.data34' size 30G ;

exit
EOF

undo隐藏参数调整

SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
from x$ksppi a,x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '%_undo_autotune%';

NAME
--------------------------------------------------------------------------------
VALUE
----------------------------------------------------------------------------------------------------
DESCRIPTION
----------------------------------------------------------------------------------------------------
_undo_autotune
TRUE
enable auto tuning of undo_retention

SQL> alter system set "_undo_autotune" = false;

创建undo表空间

create undo tablespace UNDOTBS1 datafile '/opt/oracle/oradata/CMCCWAP/undotbs01.dbf' size 200m autoextend on;

undo相关视图

select * from v$undostat;
select * from dba_hist_undostat;