NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS@ora10g> set line 130 pages 130
SYS@ora10g> col file_name for a80
SYS@ora10g> col tablespace_name for a15
SYS@ora10g> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ---------------
/u01/app/oracle/oradata/ora10g/zlm01.dbf ZLM
/u01/app/oracle/oradata/ora10g/example01.dbf EXAMPLE
/u01/app/oracle/oradata/ora10g/users01.dbf USERS
/u01/app/oracle/oradata/ora10g/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/ora10g/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/ora10g/system01.dbf SYSTEM
6 rows selected.
SYS@ora10g> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SYS@ora10g> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ---------------
/u01/app/oracle/oradata/ora10g/zlm01.dbf ZLM
/u01/app/oracle/oradata/ora10g/example01.dbf EXAMPLE
/u01/app/oracle/oradata/ora10g/users01.dbf USERS
/u01/app/oracle/oradata/ora10g/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/ora10g/system01.dbf SYSTEM
SYS@ora10g> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/ora10g/undotbs01.dbf' size 50m reuse autoextend on next 10m;
Tablespace created.
--关闭数据库，将pfile中增加的参数去除，并改回undo_management='AUTO'后，用pfile启动数据库
SYS@ora10g> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora10g> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initora10g.ora
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 327155936 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS@ora10g> create spfile from pfile;
File created.
SYS@ora10g> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SYS@ora10g> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora10g> startup
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 327155936 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS@ora10g> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfileora10g.ora
SYS@ora10g> conn zlm/zlm
Connected.
ZLM@ora10g> select count(*) from t1;
COUNT(*)
----------
20
ZLM@ora10g> insert into t1 select * from dba_objects where rownum<11;
10 rows created.
ZLM@ora10g> select count(*) from t1;
COUNT(*)
----------
30
ZLM@ora10g>
最后用修改完的pfile再创建spfile并启动数据库，非系统用户已经能够使用系统回滚段来进行DML事务操作了
总结：
只要非当前的在线日志文件内容未丢失（开启归档，并正常关闭数据库）的情况下，数据库的数据就不会丢失（当然，归档文件也被删除的例外），非在线的可以通过CLEAR重新创建，在线的只要是正常关闭的，就会被写到归档文件中去，通过RECOVER DATABASE UNTIL CANCEL就可以还原数据，最坏的情况就是current的在线日志在数据库意外关闭的情况下丢失，这种情况丢数据在所难免，如果丢数据的范围是可接受的，那么可以通过设置_allow_resetlogs_corruption=true，就可以强行OPEN数据库，但是会存在一定的问题，11g通过open resetlogs应该是可以直接OPEN数据库的，打开后要对数据库做一个全备，而10g通过该隐含参数OPEN数据库后，会遭遇到ORA-600 [4194]的错误，需要设置undo_management=manual,并通过隐含参数_corrupted_rollback_segments='_SYSSMU1$',...,'_SYSSMU10$'将系统回滚段设置为损坏，并重建默认的UNDO表空间后，数据库才能OPEN并正常使用，同样地，打开库以后第一件事就是对数据库做一个完备。