Undo Tablespaces

Change

ALTER SYSTEM SET UNDO_TABLESPACE=<NewUndoTSName> SCOPE=both;

ALTER SYSTEM SET UNDO_TABLESPACE=undo1 SCOPE=both;

Comments

• If RAC do for each node logged into each node to be done:
  MYDB1> ALTER SYSTEM SET UNDO_TABLESPACE = undo1 SCOPE=BOTH SID='mydb1';
MYDB2> ALTER SYSTEM SET UNDO_TABLESPACE = undo1 SCOPE=BOTH SID='mydb2';
• For RAC the instance name is CASE specific!
  Use the value from here: SELECT instance_name FROM v$instance;
• Always check after change to ensure changed:
  show parameter undo_tablespace;

Create

You can create more than one undo tablespace but only one of them can be active at any one time.

CREATE BIGFILE UNDO TABLESPACE undo2
DATAFILE '/u01/oracle/rbdb1/undo2.dbf'
SIZE 5g REUSE AUTOEXTEND ON NEXT 25m MAXSIZE UNLIMITED;

Display

show parameter undo_tablespace;

SELECT name, value FROM v$spparameter WHERE value !='NULL' ORDER BY name;

show parameter undo

SELECT name, value FROM v$spparameter WHERE value LIKE '%UNDO%'  ORDER BY name;

-- Session Level Undo Usage

select s.sid, 
       s.username,
       sum(ss.value) / 1024 / 1024 as undo_size_mb
from  v$sesstat ss
  join v$session s on s.sid = ss.sid
  join v$statname stat on stat.statistic# = ss.statistic#
where stat.name = 'undo change vector size'
and s.type <> 'BACKGROUND'
and s.username IS NOT NULL
group by s.sid, s.username;

column name format a10
SELECT a.name,b.status 
FROM   v$rollname a,v$rollstat b
WHERE  a.usn = b.usn
AND    a.name IN ( 
       SELECT segment_name
       FROM dba_segments 
       WHERE tablespace_name = 'UNDOTBS1'
);

-- Retention

SELECT to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
       to_char(end_time,   'DD-MON-RR HH24:MI') end_time,
       tuned_undoretention
FROM v$undostat order by end_time;

RAC Example Session

The following creates a new UNDO tablespace for a 2-node RAC.

 -- Create UNDO TS for Each Node
 CREATE BIGFILE UNDO TABLESPACE undo1 
 DATAFILE '+DATA' 
 SIZE 25g AUTOEXTEND ON NEXT 64m MAXSIZE 100g;
    Ex File Created: +DATA/TEST/DATAFILE/undo1.317.955888451

 CREATE BIGFILE UNDO TABLESPACE undo2 
 DATAFILE '+DATA' 
 SIZE 25g AUTOEXTEND ON NEXT 64m MAXSIZE 100g;
    Ex File Created: +DATA/TEST/DATAFILE/undo2.318.955888567

 -- Make Them Active in the Corresponding Node
 ALTER SYSTEM SET UNDO_TABLESPACE = undo1 SCOPE=BOTH SID='TEST1';
 ALTER SYSTEM SET UNDO_TABLESPACE = undo2 SCOPE=BOTH SID='TEST2';

 -- Drop Old UNDO Tablespaces
 ALTER TABLESPACE UNDOTBS1 OFFLINE;
 ALTER TABLESPACE UNDOTBS2 OFFLINE;

 DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
 DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

SID='TSNAME' value is case sensitive.

Migrate to New UNDO TS Session

COL name  FORMAT a25
COL VALUE FORMAT a25
SELECT name, value FROM v$spparameter WHERE value LIKE '%UNDO%'  ORDER BY name

CREATE BIGFILE UNDO TABLESPACE undo1 DATAFILE '/u01/oradata/dnasbox/dbf/undo1.dbf' SIZE 2g AUTOEXTEND ON NEXT 64m MAXSIZE UNLIMITED;
CREATE BIGFILE UNDO TABLESPACE undo2 DATAFILE '/u01/oradata/dnasbox/dbf/undo2.dbf' SIZE 2g AUTOEXTEND ON NEXT 64m MAXSIZE UNLIMITED;
CREATE BIGFILE UNDO TABLESPACE undo3 DATAFILE '/u01/oradata/dnasbox/dbf/undo3.dbf' SIZE 2g AUTOEXTEND ON NEXT 64m MAXSIZE UNLIMITED;
CREATE BIGFILE UNDO TABLESPACE undo4 DATAFILE '/u01/oradata/dnasbox/dbf/undo4.dbf' SIZE 2g AUTOEXTEND ON NEXT 64m MAXSIZE UNLIMITED;

ALTER TABLESPACE undo1 RESIZE 25g;
ALTER SYSTEM SET UNDO_TABLESPACE=undo1 SCOPE=both;
show parameter undo_tablespace;

ALTER TABLESPACE undotbs1 OFFLINE;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

ALTER TABLESPACE undotbs2 OFFLINE;
DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES;

ALTER TABLESPACE undotbs3 OFFLINE;
DROP TABLESPACE undotbs3 INCLUDING CONTENTS AND DATAFILES;

ALTER TABLESPACE undotbs4 OFFLINE;
DROP TABLESPACE undotbs4 INCLUDING CONTENTS AND DATAFILES;