Undo Management

※※

--used for undo information
/******
sqlplus
set timing on
set pagesize 400
set linesize 7000
col USERNAME format a10
col OSUSER format a20
col MACHINE format a20
col PROGRAM format a20
col SEGMENT_NAME format a20
*/
SELECT s.username,
       s.sid,
       pr.PID,
       s.OSUSER,
       s.MACHINE,
       s.PROGRAM,
       rs.segment_id,
       r.usn,
       rs.segment_name,
       r.rssize / 1024 / 1024 as rssize_M,
       sq.sql_text
  FROM v$transaction     t,
       v$session         s,
       v$rollstat        r,
       dba_rollback_segs rs,
       v$sqltext         sq,
       v$process         pr
 WHERE s.saddr = t.ses_addr
   AND t.xidusn = r.usn
   AND rs.segment_id = t.xidusn
   AND s.sql_address = sq.address
   AND s.sql_hash_value = sq.hash_value
   AND s.PADDR = pr.ADDR
 ORDER BY t.used_ublk DESC, sq.PIECE;

 

***********************************************

■Parameters for Undo Management and Retention Guarantee

・UNDO_MANAGEMENT    auto/manual  This parameter is static

・UNDO_TABLESPACE　               The other parameters are dynamic

・UNDO_RETENTION　                The other parameters are dynamic

Retention Guarantee clause.

all queries will complete successfully, provided they finish within the undo retention　time; you will never have “snapshot too old” errors again. The downside is that transactions may fail for lack of undo space because Oracle cannot　overwrite committed undo data until the retention time has passed

sql:

・CREATE UNDO TABLESPACE GAU01R DATAFILE 'gau01r0d1.dbf' SIZE 50M AUTOEXTEND OFF

・alter tablespace GAU01R retention guarantee;

・alter tablespace GAU01R retention noguarantee;

・alter system set undo_tablespace=GAU01R; 

■

・It is possible for one undo segment to protect many transactions,though Oracle will try to prevent it. 

・Oracle will create undo segments as needed for concurrent transactions

in an attempt to ensure that it is never necessary for transactions to share an　undo segment.