Oracle 19c更换undo表空间操作步骤

1、查看undo表空间

SQL> show parameter undo

NAME TYPE VALUE

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

temp_undo_enabled boolean FALSE

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDO_2

SQL> show parameter name

SQL> show parameter undo

NAME TYPE VALUE

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

temp_undo_enabled boolean FALSE

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

2、新建2个节点的undo表空间

SQL> create undo tablespace undo1 datafile '+DATA' size 100M;

Tablespace created.

SQL> create undo tablespace undo2 datafile '+data' SIZE 100M;

Tablespace created.

3、修改实例指向新的undo表空间（2个节点）

SQL> alter system set undo_tablespace=undo1 scope=both sid='ora19c1';

SQL> alter system set undo_tablespace=undo2 scope=both sid='ora19c2';

4、删除老的undo表空间

SQL> drop tablespace UNDO_2 including contents and datafiles;

Tablespace dropped.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

5、查询 验证

TABLESPACE_NAME      TABLESPACE_T SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
-------------------- ------------ ------------ ---------- ------------- ------------ -------------
SYSTEM		     PERMANENT		   220	    28160	    220        99.80		 0
SYSAUX		     PERMANENT		   165	    21120	    141        85.27		24
USERS		     PERMANENT		     5	      640	      1        21.20		 4
TEST		     PERMANENT		    10	     1280	      1        10.60		 9
UNDO1		     UNDO		   100	    12800	      2 	2.25		98
UNDO2		     UNDO		   100	    12800	      2 	2.25		98
TEMPTBS 	     TEMPORARY		    30	     3840			 .00