Saturday, October 12, 2019

Moving UNDO datafiles Oracle

SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN (
  SELECT segment_name
  FROM dba_segments
 WHERE tablespace_name = 'RBS'
);

create undo tablespace UNDOTBS2 datafile '/dba0B/oradata/ORAP/UNDOTBS01.DBF' size 5000M;

select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=BOTH;

select * from dba_rollback_segs group by tablespace_name, status;


select count(segment_name),sum(bytes/1024/1024),status from dba_undo_extents group by status;


To do
1. after rbs is offline drop it
2. create new undo tablespace /dba0a
3. switch to undotbs
4. drop unotbs2

No comments:

Post a Comment