查看当前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:~>