Oracle重建UNDO表空间

查看当前UNDO是否存在事务,如果有就kill -9 SPID的值

[oracle@hd58 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 27 13:50:21 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@JOY:~>select count(*) from v$transaction;

  COUNT(*)
----------
         0

SYS@JOY:~>select p.SPID from v$session s,v$process p where s.PADDR=p.ADDR and s.SID in (select s.sid From v$transaction t,v$session s where t.addr=s.taddr);

no rows selected

SYS@JOY:~>

查看当前被使用的回滚段，此语句得出要回滚的数据量，如果不为NULL，则undotbs1表空间不能被删除

SYS@JOY:~>select sum(bytes) from dba_undo_extents where tablespace_name='UNDOTBS1' and status='ACTIVE';

SUM(BYTES)
----------

创建新的UNDO表空间

SYS@JOY:~>select name from v$datafile where name like '%undo%';

NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/JOY/undotbs01.dbf

SYS@JOY:~>show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1
SYS@JOY:~>create undo tablespace UNDOTBS2 datafile '/opt/app/oracle/oradata/JOY/undotbs02.dbf' size 200m autoextend on next 5m maxsize unlimited;

Tablespace created.

SYS@JOY:~>alter system set undo_tablespace='UNDOTBS2';

System altered.

SYS@JOY:~>alter tablespace undotbs1 offline;

Tablespace altered.

SYS@JOY:~>drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SYS@JOY:~>show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2
SYS@JOY:~>