oracle 回收undo,Oracle重建Undo表空间

一台开发机的空间不够了，想挪也没地方挪，检查了一下，UNDOTBS01.DBF文件比较大了，有8个多G，决定重建一下回收。 原来的UNDO表空间名字是UNDOTBS1，DB版本是10G

1.新建一个新的UNDO表空间

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 28 14:59:37 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> create undo tablespace undotbs2 datafile ‘E:\oracle\product\oradata\orcl\undotbs02.dbf’ size 10M 2.切换到新的UNDO

SQL> alter system set undo_tablespace=’UNDOTBS2′ scope=both;

System altered 3.检查新的表空间

SQL> show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS2

SQL> 4.老的表空间OFFLINE

SQL> alter tablespace undotbs2 offline;

Tablespace altered 5.删除老的表空间并检查

SQL> drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped

SQL> select tablespace_name,segment_name,status from dba_rollback_segs;

TABLESPACE_NAME SEGMENT_NAME STATUS

------------------------------ ------------------------------ ---------

SYSTEM SYSTEM ONLINE

UNDOTBS2 _SYSSMU19$ ONLINE

UNDOTBS2 _SYSSMU20$ ONLINE

UNDOTBS2 _SYSSMU21$ ONLINE

UNDOTBS2 _SYSSMU22$ ONLINE

UNDOTBS2 _SYSSMU23$ ONLINE

UNDOTBS2 _SYSSMU24$ ONLINE

UNDOTBS2 _SYSSMU25$ ONLINE

UNDOTBS2 _SYSSMU26$ ONLINE

UNDOTBS2 _SYSSMU27$ ONLINE

UNDOTBS2 _SYSSMU28$ ONLINE

已选择11行。 6.删除物理文件UNDOTBS01.DBF

7.其它说明 删除物理文件时一开始没有OFFLINE表空间，结果提示程序在用，这台机子非生产机，重启服务可删除，至此空间回收,仅以此记。