本文共 5839 字，大约阅读时间需要 19 分钟。

一.shared undo 转换为local undo模式

SQL> select property_name, property_value from   database_properties where  property_name = 'LOCAL_UNDO_ENABLED';no rows selectedSQL> select con_id, tablespace_name from   cdb_tablespaces where  tablespace_name like 'UNDO%' order by con_id;    CON_ID TABLESPACE_NAME---------- ------------------------------	 1 UNDOTBS1SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/shiyu/system01.dbf/u01/app/oracle/oradata/shiyu/pdbseed/system01.dbf/u01/app/oracle/oradata/shiyu/sysaux01.dbf/u01/app/oracle/oradata/shiyu/pdbseed/sysaux01.dbf/u01/app/oracle/oradata/shiyu/undotbs01.dbf/u01/app/oracle/oradata/shiyu/users01.dbf/u01/app/oracle/oradata/shiyu/orclpdb/system01.dbf/u01/app/oracle/oradata/shiyu/orclpdb/sysaux01.dbf/u01/app/oracle/oradata/shiyu/orclpdb/users01.dbfSQL> alter session set container=ORCLPDB;                                                                                                  Session altered.SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/shiyu/undotbs01.dbf/u01/app/oracle/oradata/shiyu/orclpdb/system01.dbf/u01/app/oracle/oradata/shiyu/orclpdb/sysaux01.dbf/u01/app/oracle/oradata/shiyu/orclpdb/users01.dbf

2.切换为local undo 模式

SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup upgradeORACLE instance started.Total System Global Area  843055104 bytesFixed Size		    8798360 bytesVariable Size		  591400808 bytesDatabase Buffers	  239075328 bytesRedo Buffers		    3780608 bytesDatabase mounted.Database opened.SQL> alter database local undo on;Database altered.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area  843055104 bytesFixed Size		    8798360 bytesVariable Size		  591400808 bytesDatabase Buffers	  239075328 bytesRedo Buffers		    3780608 bytesDatabase mounted.Database opened.SQL> show pdbs    CON_ID CON_NAME			  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------	 2 PDB$SEED			  READ ONLY  NO	 3 ORCLPDB			  MOUNTEDSQL> alter session set container=ORCLPDB;Session altered.SQL> alter database open;Database altered.SQL> show pdbs    CON_ID CON_NAME			  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------	 3 ORCLPDB			  READ WRITE NO

3.验证：

SQL> col property_name for a25;SQL>col property_value for a25;SQL> select property_name,property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';PROPERTY_NAME		  PROPERTY_VALUE------------------------- -------------------------LOCAL_UNDO_ENABLED	  TRUESQL> select con_id, tablespace_name from cdb_tablespaces where  tablespace_name like 'UNDO%' order by con_id;    CON_ID TABLESPACE_NAME---------- ------------------------------	 1 UNDOTBS1	 3 UNDO_1SQL> alter session set container=ORCLPDB;Session altered.SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/shiyu/orclpdb/system01.dbf/u01/app/oracle/oradata/shiyu/orclpdb/sysaux01.dbf/u01/app/oracle/oradata/shiyu/orclpdb/users01.dbf/u01/app/oracle/oradata/shiyu/orclpdb/system01_i1_undo.dbf

二、local undo 转换为shared undo模式

1.查看当前模式

SQL> col property_name for a25;SQL>col property_value for a25;SQL> select property_name,property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';PROPERTY_NAME		  PROPERTY_VALUE------------------------- -------------------------LOCAL_UNDO_ENABLED	  TRUE

2.查看ROOT和自己定义的pdb对应的undo表空间

SQL> select con_id, tablespace_name from cdb_tablespaces where  tablespace_name like 'UNDO%' order by con_id;    CON_ID TABLESPACE_NAME---------- ------------------------------	 1 UNDOTBS1	 3 UNDO_1

3.切换成shared undo模式

SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup upgradeORACLE instance started.Total System Global Area  843055104 bytesFixed Size		    8798360 bytesVariable Size		  591400808 bytesDatabase Buffers	  239075328 bytesRedo Buffers		    3780608 bytesDatabase mounted.Database opened.SQL> alter database local undo off;Database altered.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup ORACLE instance started.Total System Global Area  843055104 bytesFixed Size		    8798360 bytesVariable Size		  591400808 bytesDatabase Buffers	  239075328 bytesRedo Buffers		    3780608 bytesDatabase mounted.Database opened.

4.验证

QL> col property_name for a25;SQL> col property_value for a25;SQL> select property_name,property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';PROPERTY_NAME		  PROPERTY_VALUE------------------------- -------------------------LOCAL_UNDO_ENABLED	  FALSE

注意：虽然已经不是本地undo模式了，但是之前存在的undo表空间不会自动删除。如果碍事，要手动删除。

SQL> select con_id, tablespace_name from   cdb_tablespaces  where  tablespace_name like 'UNDO%' order by con_id;    CON_ID TABLESPACE_NAME---------- ------------------------------	 1 UNDOTBS1	 3 UNDO_1

删除多余的undo表空间

SQL> alter session set container=ORCLPDB;Session altered.SQL> select file_name from dba_data_files where  tablespace_name = 'UNDOTBS1';no rows selectedSQL> select file_name from dba_data_files where  tablespace_name = 'UNDO_1';FILE_NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/shiyu/orclpdb/system01_i1_undo.dbfSQL> drop tablespace undo_1;Tablespace dropped.SQL> select file_name from dba_data_files where  tablespace_name = 'UNDO_1';no rows selected

转载地址：http://cdhji.baihongyu.com/