ORALCE数据库undo管理-切换UNDO表空间

create undo tablespace UNDOTBS1 DATAFILE '/oracle/oradata9/UNDOTBS00A.DBF' SIZE 30g AUTOEXTEND OFF;
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/oracle/oradata8/UNDOTBS00B.DBF' SIZE 30g AUTOEXTEND OFF;
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/oracle/oradata7/UNDOTBS00C.DBF' SIZE 30g AUTOEXTEND OFF;
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/oracle/oradata6/UNDOTBS00d.DBF' SIZE 30g AUTOEXTEND OFF;

--undo表空间不能使用ASSM,也不能指定UNIFORM SIZE;缺省为LMT

SQL> SHOW PARAMETER UNDO
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     1000
undo_tablespace                      string      UNDOTBS02

SQL> alter system set undo_tablespace='UNDOTBS1';
System altered

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     1000
undo_tablespace                      string      UNDOTBS1

SQL> SELECT SEGMENT_NAME,status,tablespace_name FROM DBA_ROLLBACK_SEGS WHERE TABLESPACE_NAME='UNDOTBS02' AND STATUS='ONLINE';
SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU18$                     ONLINE           UNDOTBS02
_SYSSMU22$                     ONLINE           UNDOTBS02
_SYSSMU33$                     ONLINE           UNDOTBS02
_SYSSMU34$                     ONLINE           UNDOTBS02
_SYSSMU35$                     ONLINE           UNDOTBS02

SQL> 

SQL> select distinct status From dba_undo_extents where tablespace_name='UNDOTBS02';
STATUS
---------
EXPIRED
UNEXPIRED
ACTIVE

SQL> 

等待原来的UNDO表空间对应的segment变成offline以及对应的extent不存在active。
sqlplus / as sysdba
drop tablespace undotbs02 including contents and datafiles;

ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS2' is currently in use

--果然还有, 过会儿alert.log有如下提示, 因为原undo表空间还有活动事务, 还在迁移中:

Wed Jun 24 18:06:02 2009
Undo Tablespace 1 moved to Pending Switch-Out state.

--等到alert.log出现如下提示后, 表示undo表空间迁移完毕:

Wed Jun 24 18:16:02 2009
Undo Tablespace 1 successfully switched out.

--此时再看是否还有在UNDOTBS1表空间上online状态的回滚段:

SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs 
where tablespace_name='UNDOTBS2' and status = 'ONLINE';

no rows selected

SQL> select tablespace_name,file_name from  DBA_DATA_FILES where tablespace_Name='UNDOTBS02';

STATUS
---------
EXPIRED
UNEXPIRED
 

--此时虽然还有offline状态的回滚段, 但可以删除原来的UNDO表空间了:

2 --删除undotbs2表空间

alter tablespace undotbs1 offline;
drop tablespace undotbs02 including contents and datafiles;

drop tablespace undotbs02 including contents keep datafiles;

Wed Oct 30 10:30:01 CST 2019
Deleted file /niosdb2/oracle/oradata8/undotbs02_2.dbf
Deleted file /niosdb2/oracle/oradata8/undotbs02.dbf
Deleted file /niosdb2/oracle/oradata8/undotbs02_3.dbf
Deleted file /niosdb2/oracle/oradata11/undotbs02_4.dbf
Deleted file /niosdb2/oracle/oradata12/undotbs02_5.dbf
Deleted file /niosdb2/oracle/oradata10/undotbs02_6.dbf
Deleted file /niosdb2/oracle/oradata12/undotbs0001.dbf
Deleted file /niosdb2/oracle/oradata12/undotbs0000111.dbf
Deleted file /niosdb2/oracle/oradata12/undotbs0000222.dbf
Completed: drop tablespace UNDOTBS02 including contents and datafiles 

1 开始文件系统未释放，应该存在进程占用数据文件句柄。

2 一会后，文件系统开始释放。

3 数据库重启可解决。

用lsof -n | grep deleted找到刚才删除的dbf文件（后边会有一个deleted的标识），查看对应的pid，直接kill掉就可以了。
这是因为这些被删除的表空间数据文件还被数据库进程所持有，在进程关闭之前磁盘空间是不会释放的，如果想要验证该方法的可用性把数据库重启一次就可以了，数据库重启的效果等同于使用kill命令。