同上，数据库不open，我没办法新建一个undotbs.
---------------------------------------------------------------
---------------------------------------------------------------
参照这个恢复过程试一下：
SQL> shutdown abort
ORACLE instance shut down.
C:\Documents and Settings\lilixin->sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jul 1 17:30:41 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/new as sysdba
Connected.
SQL> alter database datafile 2 offline drop;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> create pfile='c:\pfilenew.ora' from spfile;
create pfile='c:\pfilenew.ora' from spfile
*
ERROR at line 1:
ORA-03114: not connected to ORACLE
SQL> connect sys/new as sysdba
Connected to an idle instance.
SQL> startup restrict mount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> create pfile='c:\pnewfile.ora' from spfile;
File created.
修改c:\ pnewfile.ora，添加隐含参数：
_corrupted_rollback_segments= (_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict pfile='c:\pnewfile.ora';
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU2$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU3$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU4$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU5$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU6$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU7$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU8$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU9$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU10$ UNDOTBS1 NEEDS RECOVERY
11 rows selected.
SQL> drop tablespace undotbs1 including contents;
drop tablespace undotbs1 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
SQL> drop rollback segment _SYSSMU1$;
drop rollback segment _SYSSMU1$
*
ERROR at line 1:
ORA-00911: invalid character
要加双引号
SQL> drop rollback segment "_SYSSMU1$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU2$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU3$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU4$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU5$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU6$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU7$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU8$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU9$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU10$";
Rollback segment dropped.
SQL> drop tablespace undotbs1 including contents;
drop tablespace undotbs1 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
SQL> CREATE UNDO TABLESPACE UNDOTBS DATAFILE 'C:\oracle\oradata\NEW\undotbs.db
' SIZE 100M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;
Tablespace created.
SQL> drop tablespace undotbs1 including contents;
drop tablespace undotbs1 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 7200
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
SQL> ALTER SYSTEM SET undo_tablespace=UNDOTBS scope=both;
ALTER SYSTEM SET undo_tablespace=UNDOTBS scope=both
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
SQL> ALTER SYSTEM SET undo_tablespace=UNDOTBS scope=memory;
System altered.
SQL> drop tablespace undotbs1 including contents;
Tablespace dropped.