如何删除 UNDO 表空间 ?? 急! 急!! 急!!!

tsingsong99

相关信息:
-----init:
*.undo_management=manual
*.undo_retention=10800
*.undo_tablespace=undotbs1
*.rollback_segments='SYSTEM'
----------
[oracle@smsdbserver01 pfile]$ ps -ef | grep ora
oracle   30331     1  0 Nov05 ?        00:00:01 /u01/app/oracle/product/9.2.0.4.0/bin/tnslsnr LISTENER -inherit
oracle    9959  9957  0 11:31 ?        00:00:00 [sshd]
oracle    9960  9959  0 11:31 pts/1    00:00:00 -bash
oracle   10365 10363  0 14:36 ?        00:00:00 [sshd]
oracle   10366 10365  0 14:36 pts/0    00:00:00 -bash
oracle   11141  9960  0 18:45 pts/1    00:00:00 sqlplus            
oracle   11149     1  0 18:47 ?        00:00:00 ora_pmon_kelong70
oracle   11151     1  0 18:47 ?        00:00:00 ora_dbw0_kelong70
oracle   11153     1  0 18:47 ?        00:00:00 ora_lgwr_kelong70
oracle   11155     1  0 18:47 ?        00:00:00 ora_ckpt_kelong70
oracle   11157     1  0 18:47 ?        00:00:00 ora_smon_kelong70
oracle   11159     1  0 18:47 ?        00:00:00 ora_reco_kelong70
oracle   11161     1  0 18:47 ?        00:00:00 ora_cjq0_kelong70
oracle   11163     1  0 18:47 ?        00:00:00 ora_qmn0_kelong70
oracle   11165     1  0 18:47 ?        00:00:00 ora_s000_kelong70
oracle   11167     1  0 18:47 ?        00:00:00 ora_d000_kelong70
oracle   11169     1  0 18:47 ?        00:00:00 ora_arc0_kelong70
oracle   11171     1  0 18:47 ?        00:00:00 ora_arc1_kelong70
oracle   11173 11141  0 18:47 ?        00:00:00 oraclekelong70 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   11174 10366  0 18:54 pts/0    00:00:00 ps -ef
oracle   11175 10366  0 18:54 pts/0    00:00:00 grep ora
[oracle@smsdbserver01 pfile]$
------------------------------------------------------------
-----我的操作::
[oracle@smsdbserver01 pfile]$ sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.4.0 - Production on 星期四 12月 9 19:04:05 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL>;

SQL>; alter database datafile '/usr/oradata/kelong70/undotbs01.dbf' offline drop;

Database altered.

SQL>;  alter database open;
alter database open
*
ERROR at line 1:
ORA-01531: a database already open by the instance


SQL>;  create undo tablespace undotbs1 datafile '/usr/oradata/kelong70/undotbs01.dbf' size 200M autoextend on;
create undo tablespace undotbs1 datafile '/usr/oradata/kelong70/undotbs01.dbf' size 200M autoextend on
*
ERROR at line 1:
ORA-01543: tablespace 'UNDOTBS1' already exists


SQL>; drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping
tablespace


SQL>;

怎么办?? 急!
请各位帮忙!谢谢!

itsbegin

根据你的信息，应该没有删除掉undotbs1.dbf文件，使用：

alter database datafile '...................' online;

就可以了。

如果删除掉了这个文件，那重新创建一个UNDOTBS：

create undo tablespace undotbs2 datafile '.............' size ..m ;

指定undo_tablespace=undotbs2：

alter system set undo_tablespace=undotbs2 scope=both;

最后删除掉undotbs1，要删除掉所有的datafile后drop tablespace undotbs1。

sshd

steps

1) recover datafile '/usr/oradata/kelong70/undotbs01.dbf' ;

2) alter database datafile '/usr/oradata/kelong70/undotbs01.dbf' online;

3) create undo tablespace undotbs2 datafile '.............' size ..m ;  (from above post)

4) alter system set undo_tablespace=undotbs2 scope=both;

5) alter system set undo_management=auto scope = spfile;

6) shutodwn immediate;

7) startup;

drop tablespace undotbs1 including contents and datafiles;