7. 创建新的UNDO表空间,并设置自动扩展参数;
SQL> create undo tablespace undotbs1 datafile '/oradata/oradata/ddptest/UNDOTBS1.dbf' size 1000m reuse autoextend on next 800m maxsize unlimited;
Tablespace created.
8. 动态更改spfile配置文件;
SQL> alter system set undo_tablespace=undotbs1 scope=both;
System altered.
9. 等待原UNDO表空间所有UNDO SEGMENT OFFLINE;
SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from v$rollstat order by rssize;
10. 再执行看UNDO表空间所有UNDO SEGMENT ONLINE;
SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from v$rollstat order by rssize;
11. 删除原有的UNDO表空间;
SQL> drop tablespace undotbs2 including contents;
Tablespace dropped.
12. 确认删除是否成功;
SQL> select name from v$tablespace;
NAME
------------------------------
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
SYSTEM
TOOLS
USERS
XDB
TEMP
TESTLIB
UNDOTBS1
12 rows selected.
13. 在做此步骤前,请到$ORACLE_HOME/dbs/init$ORACLE_SID.ora如下内容是否发生变更:
#cat $ORACLE_HOME/dbs/initddptest.ora
……
*.undo_management=’AUTO’
*.undo_retention=10800
*.undo_tablespace=’UNDOTBS2’
……
如果没有发生变更请执行如下语句:
SQL> create pfile from spfile;
File created.
参考文献:
1. http://hi.baidu.com/optical/blog/item/a700c695e32c9a47d1135eca.html