oracle替换undo表空间

 ---添加错文件名！

04:28:34 SYS@ntxdb(ntxdb)> alter tablespace UNDOTBS1 add datafile '/u01/app/oracle/oradata/ntxdb/users02.dbf' size 30g autoextend off;

Tablespace altered.

04:30:20 SYS@ntxdb(ntxdb)> alter tablespace UNDOTBS1 add datafile '/u01/app/oracle/oradata/ntxdb/users03.dbf' size 30g autoextend off;

Tablespace altered.

--undo创建错误
create tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/ntxdb/undotbs2_01.dbf' size 5G autoextend off;

create pfile='/home/oracle/pfile_0421' from spfile;

--导致替换报错

05:39:11 > alter system set undo_tablespace=undotbs2 scope=both;
alter system set undo_tablespace=undotbs2 scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-30012: undo tablespace 'UNDOTBS2' does not exist or of wrong type

--删除错误的 undotbs2
05:43:26 > DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

05:43:34 >

--创建正确的 undo
create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/ntxdb/undotbs2_01.dbf' size 10G ;

--替换
alter system set undo_tablespace=undotbs2 scope=both;

--查询 undo 段 是否全是 离线

select tablespace_name,segment_name,status from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS2';

 

--删除
drop tablespace undotbs1 including contents and datafiles;

--重新替换回来！
create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/ntxdb/undotbs01.dbf' size 30G autoextend off;

alter system set undo_tablespace=undotbs1 scope=both;

--查询 undo 段 是否全是 离线

select tablespace_name,segment_name,status from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS1';

 

drop tablespace undotbs2 including contents and datafiles;

--更新 pfile
create pfile from spfile;

alter tablespace UNDOTBS1 add datafile '/u01/app/oracle/oradata/ntxdb/undotbs02.dbf' size 30g autoextend off;

alter tablespace UNDOTBS1 add datafile '/u01/app/oracle/oradata/ntxdb/undotbs03.dbf' size 30g autoextend off;