参考
http://community.csdn.net/Expert/topic/4176/4176039.xml?temp=.4484217
http://community.csdn.net/Expert/topic/4176/4176039.xml?temp=.4484217
解决方案 »
- oracle11有啥linux下的图形管理工具么?
- 怎么让换行字符串不换行???
- Oracle函数的一个问题,求解决
- linux as5下用./test.sh执行SH时报错,syntax error near unexpected token '(',请大哥大姐帮帮忙!
- 多表、多表分区的联合查询性能如何??
- 有难度的问题:.net2005+c#+oracle10g应用过程中,用c#代码调用oracle数据库,使其动态生成树形结构,出现空白,甚至整个系统登陆不上
- 如何将Oracle中的表导入到ACCESS
- 有关Oracle更新语句中空值处理的问题
- oracle 8和9在联想开天4600上无法安装,清高手指点!(在线等)
- 系统调用proc,急用.
- 通过代理服务器访问数据库?高手帮忙了!
- 菜鸟请教oracle存储过程的问题,急盼高手解答
可以采用如下方法
先mount,然后把UNDOTBS01.DBF所在的tablespace , offline,
然后再alter database open....
ERROR 位于第 1 行:
ORA-01178: 文件 2 在最后一个 CREATE CONTROLFILE 之前创建,无法重新创建
ORA-01110: 数据文件 2: 'D:\ORACLE\ORADATA\XYZ\UNDOTBS01.DBF'
如果另建一个新的表空间并把undo_tablespace指过去的话,会提示类型不对。
SQL> shutdown abort
ORACLE instance shut down.C:\Documents and Settings\lilixin->sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on Fri Jul 1 17:30:41 2005Copyright (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_segment = (_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 RECOVERY11 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 useSQL> show parameter undoNAME 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.
尽管过程不大一样,可帮了我的大忙了。