#####added by hurp for undo analyze ##############
10 * * * * sh /oracle/hurp/undo_analyze.sh >/oracle/hurp/undo_analyze.log 2>&1 &

[oracle@c4oyy1a] /oracle> more /oracle/hurp/undo_analyze.sh 
#!/usr/sbin
. /oracle/.profile
$ORACLE_HOME/bin/sqlplus / as sysdba <<EOF
exec shsnc.undo_analyze('SHSNC','UNDO_ANALYZE_TABLE','D_MT_01','UNDO1');
EXIT;   
EOF


create or replace procedure       undo_analyze(owner_name varchar2,tb_name varchar2,tbs_name varchar2,tbs_undo varchar2) is
sesundo_size number;
tbs_usedpercent_count number;
t_count number;
v_sql varchar2(4000);
begin
select count(1) into tbs_usedpercent_count from dba_tablespace_usage_metrics where tablespace_name=tbs_undo AND USED_PERCENT>90;


if tbs_usedpercent_count=1 then
select count(1) into t_count from USER_tables where table_name=tb_name;
if t_count=0 then
v_sql:='create table '||owner_name||'.'||tb_name||'(sid number,
        program varchar(48),
        machine VARCHAR2(64),
        tx_addr varchar(60),
        status varchar(16),
        start_time varchar(20),
        tablespace_name varchar2(30),
        undo_size_mb number,
        used_urec number) tablespace '||tbs_name;
execute immediate v_sql;
v_sql:='insert into  '||owner_name||'.'||tb_name||'   select s.sid,
       substr(s.program, 1, 15) program,
       s.machine,
       t.xidusn ||''.''||  t.xidslot ||''.'' || t.xidsqn tx_addr,
       t.status,
       t.start_time,
       tbs.tablespace_name,
       round(t.used_ublk * tbs.block_size / 1024 / 1024, 2) undo_size_mb,
       t.used_urec
  from v$transaction t, v$session s, v$parameter p, dba_tablespaces tbs
 where t.ses_addr = s.saddr
   and p.name = ''undo_tablespace''
   and p.value = tbs.tablespace_name
   and round(t.used_ublk * tbs.block_size / 1024 / 1024, 2) >100
 order by t.used_ublk desc ';
execute immediate v_sql;
 commit;
 else
 v_sql:=' insert into  '||owner_name||'.'||tb_name||'   select s.sid,
       substr(s.program, 1, 15) program,
       s.machine,
       t.xidusn || ''.'' || t.xidslot || ''.'' || t.xidsqn tx_addr,
       t.status,
       t.start_time,
       tbs.tablespace_name,
       round(t.used_ublk * tbs.block_size / 1024 / 1024, 2) undo_size_mb,
       t.used_urec
  from v$transaction t, v$session s, v$parameter p, dba_tablespaces tbs
 where t.ses_addr = s.saddr
   and p.name = ''undo_tablespace''
   and p.value = tbs.tablespace_name
   and round(t.used_ublk * tbs.block_size / 1024 / 1024, 2) >100
 order by t.used_ublk desc';
 execute immediate v_sql;
 commit;
 end if;
 end if;
 end;