Identify active transactions in undo and rollback segments

col o format a10 col u format a10 select osuser o, username u, sid, segment_name s, substr(sa.sql_text,1,200) txt from v$session s, v$transaction t, dba_rollback_segs r, v$sqlarea sa where s.taddr=t.addr and t.xidusn=r.segment_id(+) and s.sql_address=sa.address(+) And substr(sa.sql_text,1,200) is not null order by 3; col name format a8 col username format a8 col osuser format a8 col start_time […]

Determing the optimal UNDO tablespace size

SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]“,        SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]“,        (TO_NUMBER(e.value) * TO_NUMBER(f.value) *        g.undo_block_per_sec) / (1024*1024)       “NEEDED UNDO SIZE [MByte]“   FROM (        SELECT SUM(a.bytes) undo_size          FROM v$datafile a,               v$tablespace b,               dba_tablespaces c         WHERE c.contents = ‘UNDO’           AND c.status = ‘ONLINE’           AND […]

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?