Oracle修改undo表空间

查看当前使用的undo表空间信息:

SQL> show parameter undo_tablespace

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1

 

Oracle修改undo表空间
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
Oracle修改undo表空间

 

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状态

Oracle修改undo表空间
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
Oracle修改undo表空间

 

(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)

复制代码

Oracle修改undo表空间
 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 
Oracle修改undo表空间

 

(1)设置UNDOTBS1为默认undo表空间

1 SQL> alter system set undo_tablespace = undotbs1 scope=both;
2 
3 System altered.

 

(2)此时检查UNDOTBS1处于ONLINE状态

复制代码

Oracle修改undo表空间
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 
Oracle修改undo表空间

 

(3)删除

Oracle修改undo表空间
 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
Oracle修改undo表空间

(4)至此,undo表空间由UNDOTBS2又成功切换为UNDOTBS1

Oracle修改undo表空间

上一篇:Oracle编译失效对象


下一篇:Oracle修改数据文件路径