Steps for dropping and creating undo tablespace:

Comment out the undo setting in the init.ora. Shutdown the database normal and startup. Drop the UNDO tablespace, remove the UNDO datafiles and recreate with five datafilies with initial size of 2048M for a total of 10GB. Uncomment the undo parameters and restart the instance.

su - opreprod
sqlplus '/as sysdba';

set lines 1000
column file_name format a50
set pages 100

1.select FILE_NAME,TABLESPACE_NAME from dba_data_files where tablespace_name like 'APPS_UNDOTS1';

2. Create a new undo tablespace:

create undo tablespace second_undo datafile '/d03/opreprod/preproddata/s_undo01.dbf' size 100M autoextend on maxsize 2000M;

3. Make this tablespace default undo tablespace:

alter system set undo_tablespace= second_undo;

4. Drop the old undo tablespace which came from production:

drop tablespace APPS_UNDOTS1 including contents and datafiles;

(check whether all the datafiles that are returned by select statement in step 1 are deleted from their respective locations, if not delete them manually)

5.Recreate the tablespace APPS_UNDOTS1 with required space.

create undo tablespace APPS_UNDOTS1 datafile '/d03/opreprod/preproddata/undo01.dbf' size 500M autoextend on maxsize 8000M;

ALTER TABLESPACE APPS_UNDOTS1 ADD datafile '/d03/opreprod/preproddata/undo02.dbf' size 500M autoextend on maxsize 8000M;

ALTER TABLESPACE APPS_UNDOTS1 ADD datafile '/d03/opreprod/preproddata/undo03.dbf' size 500M autoextend on maxsize 8000M;

ALTER TABLESPACE APPS_UNDOTS1 ADD datafile '/d03/opreprod/preproddata/undo04.dbf' size 500M autoextend on maxsize 8000M;

ALTER TABLESPACE APPS_UNDOTS1 ADD datafile '/d03/opreprod/preproddata/undo05.dbf' size 500M autoextend on maxsize 8000M;

ALTER TABLESPACE APPS_UNDOTS1 ADD datafile '/d03/opreprod/preproddata/undo06.dbf' size 500M autoextend on maxsize 8000M;

ALTER TABLESPACE APPS_UNDOTS1 ADD datafile '/d03/opreprod/preproddata/undo07.dbf' size 500M autoextend on maxsize 8000M;

6. make APPS_UNDOTS1 undo tablespace a default one:

alter system set undo_tablespace= APPS_UNDOTS1;

7. drop ‘second_undo’ undo tablespace:

drop tablespace second_undo including contents and datafiles;