Oracle中undo表空间的切换
查看操作系统: SQL>? !cat /etc/redhat-release SQL> set linesize 400 SQL> select * from v$version; BANNER CON_ID ---------------------------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 TNS for Linux: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - Production 0 查看当前使用的undo表空间信息: SQL> show parameter undo_tablespace NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace string UNDOTBS1 SQL> col FILE_NAME format a100 SQL> select tablespace_name,file_id,file_name,round (bytes / (1024 * 1024),0) total_space from dba_data_files where tablespace_name=‘UNDOTBS1‘; TABLESPACE_NAME FILE_ID FILE_NAME TOTAL_SPACE -------------------- ---------- ---------------------------------------------------------------------------------------------------- ----------- UNDOTBS1 4 /u01/app/oracle/oradata/orcl/undotbs01.dbf 220 1、数据库状态静止时(无DML操作期间)执行UNDO表空间切换(由UNDOTBS1切换为UNDOTBS2) (1)创建新的undo表空间UNDOTBS2 SQL> create undo tablespace UNDOTBS2 datafile ‘/u01/app/oracle/oradata/orcl/undotbs02.dbf‘ size 10M; Tablespace created. (2)切换UNDOTBS2为新的undo表空间 SQL> alter system set undo_tablespace = undotbs2 scope=both; System altered. (3)此时数据库处于静止状态,无任何DML操作,查看UNDOTBS1已经处于OFFLINE状态 SQL> select tablespace_name,status,count(*) from dba_rollback_segs group by tablespace_name,status; TABLESPACE_NAME STATUS COUNT(*) -------------------- ---------------- ---------- UNDOTBS1 OFFLINE 10 SYSTEM ONLINE 1 UNDOTBS2 ONLINE 11 (4)检查确认UNDOTBS1中没有ONLINE的segment SQL> select status,segment_name from dba_rollback_segs where status not in (‘OFFLINE‘) and tablespace_name=‘UNDOTBS1‘; no rows selected (5)删除旧的UNDOTBS1 SQL> Drop tablespace UNDOTBS1 including contents and datafiles; Tablespace dropped. (6)至此,undo表空间由UNDOTBS1成功切换为UNDOTBS2. ? 2、数据库中有DML操作期间,切换UNDO表空间步骤(由UNDOTBS2切换为UNDOTBS1) SQL> create undo tablespace UNDOTBS1 datafile ‘/u01/app/oracle/oradata/orcl/undotbs01.dbf‘ size 220M; Tablespace created. SQL> select tablespace_name,0) total_space from dba_data_files; TABLESPACE_NAME FILE_ID FILE_NAME TOTAL_SPACE -------------------- ---------- ---------------------------------------------------------------------------------------------------- ----------- SYSTEM 1 /u01/app/oracle/oradata/orcl/system01.dbf 790 SYSAUX 3 /u01/app/oracle/oradata/orcl/sysaux01.dbf 730 USERS 6 /u01/app/oracle/oradata/orcl/users01.dbf 5 UNDOTBS2 11 /u01/app/oracle/oradata/orcl/undotbs02.dbf 10 UNDOTBS1 12 /u01/app/oracle/oradata/orcl/undotbs01.dbf 220 (1)设置UNDOTBS1为默认undo表空间 SQL> alter system set undo_tablespace = undotbs1 scope=both; System altered. (2)此时检查UNDOTBS1处于ONLINE状态 SQL> select tablespace_name,status; TABLESPACE_NAME STATUS COUNT(*) -------------------- ---------------- ---------- UNDOTBS1 ONLINE 11 SYSTEM ONLINE 1 UNDOTBS2 OFFLINE 11 (3)删除 SQL> select status,segment_name from dba_rollback_segs where status not in (‘OFFLINE‘) and tablespace_name=‘UNDOTBS2‘; no rows selected SQL> Drop tablespace UNDOTBS2 including contents and datafiles; Tablespace dropped. SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> ? (4)至此,undo表空间由UNDOTBS2又成功切换为UNDOTBS1 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |