删除online日志测试及ORA-600 [4194]错误的处理 - 数据库编程

:
ORA-01552: cannot use system rollback segment for non-system tablespace 'ZLM'

--重建UNDO表空间（先删除，后创建）
SYS@ora10g> show parameter undo

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;

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.

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重新创建，在线的只要是正常关闭的，就会被写到归档文件中去，

删除online日志测试及ORA-600 [4194]错误的处理(十)