UNDO Tablespace. Move from one location to another; Add Datafile.

=======================
General
=======================
Due to space shortage on disk need to move UNDO Tablespace to another location.

=======================
Steps
=======================

To add datafile to UNDO TBS:

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/oracle_db/db1/db_igt/data/ora_undotbs_02.dbf' SIZE 1000M AUTOEXTEND ON NEXT 1M MAXSIZE 32000M; 

Increase size of existing datafile

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_undotbs_01.dbf' AUTOEXTEND ON MAXSIZE 4000M;

=======================
Steps
=======================

See current status

show parameter undo
NAME                                 TYPE                 VALUE
------------------------------------ -------------------- ----------
undo_management                      string               AUTO
undo_retention                       integer              3600
undo_tablespace                      string               UNDOTBS

Create a new UNDO Tablespace, alter system to use it,and from the old one
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/oracle_db/db2/db_igt/igt/ora_undotbs2_01.dbf' SIZE 10000M;

ALTER DATABASE DATAFILE '/oracle_db/db2/db_igt/igt/ora_undotbs2_01.dbf' AUTOEXTEND ON MAXSIZE 30000M;

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;

Now, either update SPFILE to use the new name UNDOTBS2 via:
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

Or move UNDO tablespace back to the old name

CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/oracle_db/db2/db_igt/tbs/ora_undotbs_01.dbf' SIZE 10000M;

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS;

DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

=======================
Additional Info
=======================
Inside spfile, the UNDO Tablespace name is stored, but not the actual file location.

oracle@some_server:/software/oracle/admin/igt/pfile>% strings spfileigt.ora
igt.__db_cache_size=3489660928
igt.__java_pool_size=67108864
igt.__large_pool_size=67108864
igt.__oracle_base='/software/oracle'#ORACLE_BASE set from environment
igt.__pga_aggregate_target=2952790016
igt.__sga_target=5637144576
igt.__shared_io_pool_size=0
igt.__shared_pool_size=2013265920
igt.__streams_pool_size=67108864
*.archive_lag_target=1800
*.audit_file_dest='/software/oracle/admin/igt/adump'
*.audit_trail='none'
*.compatible='11.1.0.0.0'
*.control_files='/oracle_db/db1/db_igt/
ora_control_01.ctl','/oracle_db/db1/db_igt/ora_control_02.ctl','/oracle_db/db1/db_igt/ora_control_03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='igt'
*.diagnostic_dest='/software/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=igtXDB)'
*.fast_start_mttr_target=180
*.filesystemio_options='asynch'
*.log_archive_dest_1='location=/oracle_db/db2/db_igt/arch'
*.log_archive_format='arch%T_%s_%r.arc'
*.memory_target=8589934592
*.nls_length_semantics='char'
*.open_cursors=300
*.open_li
nks=12
*.os_authent_prefix=''
*.processes=600
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.SESSION_CACHED_CURSORS=100
*.undo_retention=3600

*.undo_tablespace='UNDOTBS'

Error - UNDO Segment cannot be dropped

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'

         ); 

 

NAME                           STATUS

------------------------------ ------------------------------

_SYSSMU7_3211463042$           PENDING OFFLINE

_SYSSMU18_1767712819$          PENDING OFFLINE

_SYSSMU21_4188676727$          PENDING OFFLINE

SELECT name, xacts active_transactions

FROM  v$rollname, v$rollstat 

WHERE status = 'PENDING OFFLINE' 

  AND v$rollname.usn = v$rollstat.usn; 

NAME                           ACTIVE_TRANSACTIONS

------------------------------ -------------------

_SYSSMU7_3211463042$                             0

_SYSSMU18_1767712819$                            0

_SYSSMU21_4188676727$                            0

In case there were active transactions - this SL would have shown the active sid+serial# to be killed.

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 = 'UNDOTBS1'

         ); 

NAME                  STATUS          USERNAME       SID    SERIAL#

--------------------- --------------- ----------- ------ ----------

_SYSSMU46_132405334$  PENDING OFFLINE LAB_USER      3988      18531

ALTER SYSTEM KILL SESSION '3988,18531' IMMEDIATE;

>System altered.

DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;

>Tablespace dropped.

See current UNDO usage

SELECT * FROM V$UNDOSTAT;

SELECT * FROM V$TRANSACTION;

SELECT * FROM DBA_UNDO_EXTENTS;

SELECT * FROM DBA_HIST_UNDOSTAT;

See current UNDO info

SELECT tablespace_name, contents 

FROM DBA_TABLESPACES 

WHERE contents = 'UNDO';

SELECT tablespace_name, SUBSTR(file_name,1,60) 

  FROM DBA_DATA_FILES 

WHERE tablespace_name LIKE 'UNDO%' order by 1,2;

SELECT

  DDF.tablespace_name,

  SUM(DDF.bytes)/(1024*1024) total_space_MB,

  round(FREE_SPACE.free,2) free_space_mb,

  round(FREE_SPACE.free/(sum(DDF.bytes)/(1024*1024))* 100,2) pct_free

 FROM DBA_DATA_FILES DDF,

  (select tablespace_name,SUM(bytes)/(1024*1024) free  

     FROM DBA_FREE_SPACE GROUP BY tablespace_name) FREE_SPACE

 WHERE DDF.tablespace_name = FREE_SPACE.tablespace_name(+)

   AND DDF.tablespace_name LIKE 'UNDO%'

GROUP BY DDF.tablespace_name,FREE_SPACE.free;

SELECT tablespace_name, status, COUNT(segment_name), SUM(bytes/1024/1024)

FROM DBA_UNDO_EXTENTS 

GROUP BY tablespace_name, status;