Hi,

I am trying to change the undo tablespace in DB instance, unsuccessfully.

Approach 1:

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL>
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = FUSION_UNDOTS scope=BOTH;

System altered.

SQL> show parameter undo

NAME TYPE VALUE
------------------------- ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string FUSION_UNDOTS
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 9898557440 bytes
Fixed Size 3720936 bytes
Variable Size 3858762008 bytes
Database Buffers 6006243328 bytes
Redo Buffers 29831168 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL>


Approach 2:

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL>
SQL> create pfile='/tmp/initnew.ora' from spfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[oracle@abcdef tmp]$
[oracle@abddef tmp]$ vi initnew.ora #changing ORCL1.undo_tablespace='FUSION_UNDOTS'
[oracle@slc03phf tmp]$ $ORACLE_HOME/bin/sqlplus / as sysdba
SQL> startup pfile='/tmp/initnew.ora'
ORACLE instance started.

Total System Global Area 9898557440 bytes
Fixed Size 3720936 bytes
Variable Size 3858762008 bytes
Database Buffers 6006243328 bytes
Redo Buffers 29831168 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string FUSION_UNDOTS
SQL>
SQL> create spfile from pfile='/tmp/initnew.ora'
2 ;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 9898557440 bytes
Fixed Size 3720936 bytes
Variable Size 3858762008 bytes
Database Buffers 6006243328 bytes
Redo Buffers 29831168 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL>


Can anyone please help?
Thanks,
Johnnie