select a.tablespace_name, a.TOTAL_GB total_GB, b.Used_GB Used_GB, round(((b.Used_GB/a.TOTAL_GB)*100),2) Percent_USED
from
(select tablespace_name, sum(bytes)/1024/1024/1024 TOTAL_GB from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024/1024 Used_GB from DBA_UNDO_EXTENTS group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
--1/Create undo tablespace
CREATE SMALLFILE UNDO TABLESPACE "TUNDOTBS1" DATAFILE
'/u03/oracle/oradata/DBAViet/tundo1_01.dbf' SIZE 10G REUSE ,
'/u03/oracle/oradata/DBAViet/tundo1_02.dbf' SIZE 10G REUSE ,
'/u03/oracle/oradata/DBAViet/tundo1_03.dbf' SIZE 10G REUSE ,
'/u03/oracle/oradata/DBAViet/tundo1_04.dbf' SIZE 10G REUSE ,
'/u03/oracle/oradata/DBAViet/tundo1_05.dbf' SIZE 10G REUSE ,
'/u03/oracle/oradata/DBAViet/tundo1_06.dbf' SIZE 10G REUSE ,
'/u03/oracle/oradata/DBAViet/tundo1_07.dbf' SIZE 10G REUSE ,
'/u03/oracle/oradata/DBAViet/tundo1_08.dbf' SIZE 10G REUSE
CREATE SMALLFILE UNDO TABLESPACE "TUNDOTBS2" DATAFILE
'/u03/oracle/oradata/DBAViet/tundo2_01.dbf' SIZE 10G REUSE ,
'/u03/oracle/oradata/DBAViet/tundo2_02.dbf' SIZE 10G REUSE ,
'/u03/oracle/oradata/DBAViet/tundo2_03.dbf' SIZE 10G REUSE ,
'/u03/oracle/oradata/DBAViet/tundo2_04.dbf' SIZE 10G REUSE ,
'/u03/oracle/oradata/DBAViet/tundo2_05.dbf' SIZE 10G REUSE ,
'/u03/oracle/oradata/DBAViet/tundo2_06.dbf' SIZE 10G REUSE ,
'/u03/oracle/oradata/DBAViet/tundo2_07.dbf' SIZE 10G REUSE ,
'/u03/oracle/oradata/DBAViet/tundo2_08.dbf' SIZE 10G REUSE
-- 2/ Set new undo tablespace for each intance
ALTER SYSTEM SET undo_tablespace = TUNDOTBS1 SID='DBAViet1'
ALTER SYSTEM SET undo_tablespace = TUNDOTBS2 SID='DBAViet2'
-- 3/ DROP TABLESPACE UNDOTBS1_old including contents;
DROP TABLESPACE UNDOTBS1 including contents and datafiles;
DROP TABLESPACE UNDOTBS2 including contents and datafiles;