The operating system version and database version are as follows:
Sql>!cat/etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)
Sql> select * from V$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition release 11.2.0.4.0-64bit Production
Pl/sql Release 11.2.0.4.0-production
CORE 11.2.0.4.0 Production
TNS for Linux:version 11.2.0.4.0-production
Nlsrtl Version 11.2.0.4.0-production
View the currently used undo table space Information
Sql> Show Parameter Undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Undo_tablespace string UNDOTBS1
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 3/U01/APP/ORACLE/ORADATA/ORCL/UNDOTBS01.DBF 60
1, the database state is still (no DML operation) to perform undo table space switch (from UNDOTBS1 switch to UNDOTBS2)
(1) Create a new undo table space UNDOTBS2
sql> Create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf ' size 10M;
Tablespace created.
(2) switch UNDOTBS2 to the new undo table Space
Sql> alter system Set Undo_tablespace = UNDOTBS2 Scope=both;
System altered.
(3) At this time the database is in a static state, without any DML operations, view UNDOTBS1 is already in offline state
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 10
(4) Check to confirm that there is no online segment in UNDOTBS1
Sql> Select Status,segment_name from Dba_rollback_segs where status isn't in (' OFFLINE ') and tablespace_name= ' UNDOTBS1 ';
No rows selected
(5) Delete the old UNDOTBS1
Sql> Drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.
(6) At this point, the Undo table space is successfully switched from UNDOTBS1 to UNDOTBS2.
2. During DML operations in the database, toggle the Undo Table Space Step (switch from UNDOTBS2 to UNDOTBS1)
Session 1 is performing the following actions:
Conn Test/test
CREATE TABLE Test (name varchar (2));
INSERT into test values (' Zhangsan ');
Commit
Update test set name= ' Lisi ' where name= ' Zhangsan ';
Not committed at this time
Session 2 starts switching undo table space Operations
(1) confirm the currently used undo table space
Sql> Show Parameter Undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Undo_tablespace string UNDOTBS2
(2) View all selment in the current undo table space are online status
Sql> Select Status,segment_name from Dba_rollback_segs where status isn't in (' OFFLINE ') and tablespace_name= ' UNDOTBS2 ';
STATUS segment_name
---------------- ------------------------------
ONLINE _syssmu11_3423735372$
ONLINE _syssmu12_567660877$
ONLINE _syssmu13_100563780$
ONLINE _syssmu14_1447748955$
ONLINE _syssmu15_478708454$
ONLINE _syssmu16_3309423900$
ONLINE _syssmu17_525951688$
ONLINE _syssmu18_130984470$
ONLINE _syssmu19_3964826557$
ONLINE _syssmu20_994913344$
Ten rows selected.
(3) Create a new undo table space UNDOTBS1
sql> Create undo tablespace UNDOTBS1 datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf ' size 10M;
Tablespace created.
(4) set UNDOTBS1 as default Undo table Space
Sql> alter system Set Undo_tablespace = UNDOTBS1 Scope=both;
System altered.
(5) At this time check UNDOTBS2 there is a segment in the online state
Sql> Select Tablespace_name, Status, COUNT (*) from Dba_rollback_segs GROUP by tablespace_name, status;
Tablespace_name STATUS COUNT (*)
------------------------------ ---------------- ----------
UNDOTBS1 ONLINE 10
SYSTEM ONLINE 1
UNDOTBS2 OFFLINE 9
UNDOTBS2 ONLINE 1
Sql> Select Status,segment_name from Dba_rollback_segs where status isn't in (' OFFLINE ') and tablespace_name= ' UNDOTBS2 ';
STATUS segment_name
---------------- ------------------------------
ONLINE _syssmu15_478708454$
Therefore can not blindly delete UNDOTBS2 table space, otherwise will error or lead to inconsistent data, or even cause database failure (perhaps ~ ~ probably ...) ）
You must wait for the UNDOTBS2 to become offline before you can perform the deletion of the table space.
Waiting for ing ...
(6) go back to session 1 to perform a commit, and then back to session 2 to see that the UNDOTBS2 state changes to offline
Sql> Select Status,segment_name from Dba_rollback_segs where status isn't in (' OFFLINE ') and tablespace_name= ' UNDOTBS2 ';
No rows selected
Sql> Select Tablespace_name, Status, COUNT (*) from Dba_rollback_segs GROUP by tablespace_name, status;
Tablespace_name STATUS COUNT (*)
------------------------------ ---------------- ----------
UNDOTBS1 ONLINE 10
SYSTEM ONLINE 1
UNDOTBS2 OFFLINE 10
(7) Delete UNDOTBS2 at this time
Sql> Drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
Sql> Show Parameter Undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Undo_management string AUTO
Undo_retention Integer 900
Undo_tablespace string UNDOTBS1
(8) At this point, the Undo table space is successfully switched from UNDOTBS2 to UNDOTBS1
Reference article:if:how to Switch to a New Undo tablespace (document ID 1951695.1)
