Overview

Undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued. Automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing “snapshot too old” errors on long running queries.

This is done by setting the UNDO_RETENTION parameter. The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time.

Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy. All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.

With the below information gathered by SQL query, you may have a idea to prevent undo space is running out so quickly.
Continue reading “How to determine undo usage in Oracle”

Check each schema usage on tablespaces.

set linesize 300
set pagesize 20
set feedback off

select sysdate, a.owner username, a.tablespace_name, round(b.total_space/1024/1024,2) "Total (MB)", round(sum(a.bytes)/1024/1024,2) "Used (MB)", round(sum(a.bytes/b.total_space)*100,2) "% Used"
from dba_segments a, (select tablespace_name, sum(bytes) total_space
                      from dba_data_files
                      group by tablespace_name) b
where a.tablespace_name not in ('SYSAUX', 'SYSTEM', 'UNDOTBS1', 'UNDOTBS2')
and a.tablespace_name = b.tablespace_name
group by a.tablespace_name, a.owner, b.total_space/1024/1024
order by a.tablespace_name, a.owner;

Check overall tablespaces usage.

set linesize 300
set pagesize 20
set feedback off

select t1.tsp_name, sysdate,
       round(t1.total_space/1024/1024,2) "Total (MB)",
       round((t1.total_space-t2.free_bytes)/1024/1024,2) "Used (MB)",
       round(t2.free_bytes/1024/1024,2) "Free (MB)",
       round(t2.free_bytes/t1.total_space*100,2) "% Free",
       decode(greatest(round(t2.free_bytes/t1.total_space*100,2),20),20,'*',' ') D,
       decode(greatest(round(t2.free_bytes/t1.total_space*100,2),15),15,'*',' ') E
from (select tablespace_name tsp_name, sum(bytes) total_space
      from sys.dba_data_files
      where tablespace_name not in ('UNDOTBS1', 'UNDOTBS2')
      group by tablespace_name) t1,
     (select tablespace_name tsp_name, sum(bytes) free_bytes
      from sys.dba_free_space
      where tablespace_name not in ('UNDOTBS1', 'UNDOTBS2')
      group by tablespace_name) t2
where t1.tsp_name = t2.tsp_name
order by tsp_name;

Everyone has a favorite script to find the active SQL statements. Here is one of mine, it’s RAC friendly too..

     set serverout on size 999999
     declare
     begin
     dbms_output.put_line(' ');
     dbms_output.put_line('************* Start report for WAITING sessions with current SQL ***************');
     for x in (select vs.inst_id, vs.sid || ',' || vs.serial# sidser, vs.sql_address, vs.sql_hash_value,
     vs.last_call_et, vsw.seconds_in_wait, vsw.event, vsw.state
     from gv$session_wait vsw, gv$session vs
     where vsw.sid = vs.sid
     and vsw.inst_id = vs.inst_id
     and vs.type <> 'BACKGROUND'
     and vsw.event NOT IN ('rdbms ipc message'
     ,'smon timer'
     ,'pmon timer'
     ,'SQL*Net message from client'
     ,'lock manager wait for remote message'
     ,'ges remote message'
     ,'gcs remote message'
     ,'gcs for action'
     ,'client message'
     ,'pipe get'
     ,'Null event'
     ,'PX Idle Wait'
     ,'single-task message'
     ,'PX Deq: Execution Msg'
     ,'KXFQ: kxfqdeq - normal deqeue'
     ,'listen endpoint status'
     ,'slave wait'
     ,'wakeup time manager'))
     loop
     begin
     dbms_output.put_line('Event WaitState InstID SidSerial LastCallEt SecondsInWait');
     dbms_output.put_line('************************* ******************** ****** *********** ********** *************');
     dbms_output.put_line(rpad(x.event,25) ||' '|| rpad(x.state,20) ||' '|| lpad(x.inst_id,6) ||' '|| lpad(x.sidser,11) ||'
     '|| lpad(x.last_call_et,10) ||' '|| lpad(x.seconds_in_wait,13));
     dbms_output.put_line(' SQLText ');
     dbms_output.put_line('****************************************************************');
     for y in (select sql_text
     from gv$sqltext
     where address = x.sql_address
     and hash_value = x.sql_hash_value
     and inst_id = x.inst_id
     order by piece)
     loop
     dbms_output.put_line(y.sql_text);
     end loop;
     end;
     end loop;
     dbms_output.put_line('************** End re! port for sessions waiting with current SQL ****************');
     dbms_output.put_line(' ');
     end;
     /

http://www.oracle.com/technology/oramag/code/tips2004/062104.html