Oracle修改undo表空间
查看当前使用的undo表空间信息：
SQL> show parameter undo_tablespace NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace string UNDOTBS1
1 SQL> col FILE_NAME format a100 2 SQL> select tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space from dba_data_files where tablespace_name='UNDOTBS1'; 3 4 TABLESPACE_NAME FILE_ID FILE_NAME TOTAL_SPACE 5 -------------------- ---------- ---------------------------------------------------------------------------------------------------- ----------- 6 UNDOTBS1 4 /u01/app/oracle/oradata/orcl/undotbs01.dbf 220
1、数据库状态静止时（无DML操作期间）执行UNDO表空间切换（由UNDOTBS1切换为UNDOTBS2）
（1）创建新的undo表空间UNDOTBS2
1 SQL> create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 10M; 2 3 Tablespace created.
（2）切换UNDOTBS2为新的undo表空间
1 SQL> alter system set undo_tablespace = undotbs2 scope=both; 2 3 System altered.
（3）此时数据库处于静止状态，无任何DML操作，查看UNDOTBS1已经处于OFFLINE状态
1 SQL> select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status; 2 3 TABLESPACE_NAME STATUS COUNT(*) 4 -------------------- ---------------- ---------- 5 UNDOTBS1 OFFLINE 10 6 SYSTEM ONLINE 1 7 UNDOTBS2 ONLINE 11
（4）检查确认UNDOTBS1中没有ONLINE的segment
1 SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';
2
3 no rows selected
（5）删除旧的UNDOTBS1
1 SQL> Drop tablespace UNDOTBS1 including contents and datafiles; 2 3 Tablespace dropped.
（6）至此，undo表空间由UNDOTBS1成功切换为UNDOTBS2.
2、数据库中有DML操作期间，切换UNDO表空间步骤（由UNDOTBS2切换为UNDOTBS1）
复制代码
1 SQL> create undo tablespace UNDOTBS1 datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' size 220M; 2 3 Tablespace created. 4 5 SQL> select tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space from dba_data_files; 6 7 TABLESPACE_NAME FILE_ID FILE_NAME TOTAL_SPACE 8 -------------------- ---------- ---------------------------------------------------------------------------------------------------- ----------- 9 SYSTEM 1 /u01/app/oracle/oradata/orcl/system01.dbf 10 SYSAUX 3 /u01/app/oracle/oradata/orcl/sysaux01.dbf 11 USERS 6 /u01/app/oracle/oradata/orcl/users01.dbf 12 UNDOTBS2 11 /u01/app/oracle/oradata/orcl/undotbs02.dbf 13 UNDOTBS1 12 /u01/app/oracle/oradata/orcl/undotbs01.dbf
（1）设置UNDOTBS1为默认undo表空间
1 SQL> alter system set undo_tablespace = undotbs1 scope=both; 2 3 System altered.
（2）此时检查UNDOTBS1处于ONLINE状态
复制代码
1 SQL> select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status; 2 3 TABLESPACE_NAME STATUS COUNT(*) 4 -------------------- ---------------- ---------- 5 UNDOTBS1 ONLINE 6 SYSTEM ONLINE 7 UNDOTBS2 OFFLINE
（3）删除
1 SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS2';
2
3 no rows selected
4
5 SQL> Drop tablespace UNDOTBS2 including contents and datafiles;
6
7 Tablespace dropped.
8
9 SQL> show parameter undo
10
11 NAME TYPE VALUE
12 ------------------------------------ ----------- ------------------------------
13 temp_undo_enabled boolean FALSE
14 undo_management string AUTO
15 undo_retention integer 900
16 undo_tablespace string UNDOTBS1
（4）至此，undo表空间由UNDOTBS2又成功切换为UNDOTBS1
