ORA-600[4000]/[4097]錯誤的處理

資料庫做不完全恢復後，雖然可以開啟資料庫，但做一些DDL/DML操作，會出現ora-600[4000]錯誤及[4097]錯誤。通常情況下建議匯出資料後重建，但這樣太費時間。透過如下處理，已知可以避開這些錯誤，如果有環境，可以再測試一下。

[@more@]

BANNER
-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> @E:BakORACLEchensq

Creating package CHENSQ
=======================

create or replace package chensq is
*
第 1 行出現錯誤:
ORA-00603: ORACLE 伺服器會話因致命錯誤而終止
程式 ID: 2556
會話 ID: 170 序列號: 5

SQL> shutdown immediate;
ORA-00600: 內部錯誤程式碼, 引數: [4000], [6], [], [], [], [], [], []

SQL> shutdown immediate;
ORA-00600: 內部錯誤程式碼, 引數: [4000], [6], [], [], [], [], [], []

SQL> shutdown immediate;
ORA-00600: 內部錯誤程式碼, 引數: [4000], [6], [], [], [], [], [], []

US# NAME TS# FILE#
--------- ------------------------ ---------- ----------
0 SYSTEM 0 1
2 _SYSSMU2_1245841283$ 2 3
13 _SYSSMU13_1245841283$ 2 3
14 _SYSSMU14_1245841283$ 2 3
15 _SYSSMU15_1245841283$ 2 3
16 _SYSSMU16_1245841283$ 2 3
17 _SYSSMU17_1245841283$ 2 3
18 _SYSSMU18_1245841283$ 2 3
19 _SYSSMU19_1245841283$ 2 3
20 _SYSSMU20_1245841283$ 2 3
1 _SYSSMU1_1245841283$ 2 3

已選擇11行。

SQL> l
1* select us#,name,ts#,file# from undo$
SQL> /

US# NAME TS# FILE#
---------- ------------------------ ---------- ----------
0 SYSTEM 0 1
2 _SYSSMU2_1245841283$ 2 3
13 _SYSSMU13_1245841283$ 2 3
14 _SYSSMU14_1245841283$ 2 3
15 _SYSSMU15_1245841283$ 2 3
16 _SYSSMU16_1245841283$ 2 3
17 _SYSSMU17_1245841283$ 2 3
18 _SYSSMU18_1245841283$ 2 3
19 _SYSSMU19_1245841283$ 2 3
20 _SYSSMU20_1245841283$ 2 3
1 _SYSSMU1_1245841283$ 2 3

已選擇11行。

SQL> select name,ts# from v$tablespace;

NAME TS#
------------------------ ----------
SYSTEM 0
SYSAUX 1
UNDOTBS1 2
USERS 4
TEMP 3

SQL> create undo tablespace undotbs2;

表空間已建立。

SQL> /

US# NAME TS# FILE#
---------- ------------------------ ---------- ----------
0 SYSTEM 0 1
2 _SYSSMU2_1245841283$ 2 3
3 _SYSSMU3_1245921918$ 5 5
4 _SYSSMU4_1245921918$ 5 5
5 _SYSSMU5_1245921918$ 5 5
6 _SYSSMU6_1245921918$ 5 5
7 _SYSSMU7_1245921918$ 5 5
8 _SYSSMU8_1245921918$ 5 5
9 _SYSSMU9_1245921918$ 5 5
10 _SYSSMU10_1245921918$ 5 5
11 _SYSSMU11_1245921918$ 5 5
12 _SYSSMU12_1245921918$ 5 5
13 _SYSSMU13_1245841283$ 2 3
14 _SYSSMU14_1245841283$ 2 3
15 _SYSSMU15_1245841283$ 2 3
16 _SYSSMU16_1245841283$ 2 3
17 _SYSSMU17_1245841283$ 2 3
18 _SYSSMU18_1245841283$ 2 3
19 _SYSSMU19_1245841283$ 2 3
20 _SYSSMU20_1245841283$ 2 3
21 _SYSSMU21_1245921928$ 6 6
1 _SYSSMU1_1245841283$ 2 3
22 _SYSSMU22_1245921928$ 6 6
23 _SYSSMU23_1245921928$ 6 6
24 _SYSSMU24_1245921928$ 6 6
25 _SYSSMU25_1245921928$ 6 6
26 _SYSSMU26_1245921928$ 6 6
27 _SYSSMU27_1245921928$ 6 6
28 _SYSSMU28_1245921929$ 6 6
29 _SYSSMU29_1245921929$ 6 6
30 _SYSSMU30_1245921929$ 6 6

SQL> create undo tablespace undotbs3;

表空間已建立。

SQL> alter system set undo_tablespace=undotbs3;

系統已更改。

SQL> drop tablespace undotbs1;

表空間已刪除。

Thu Jun 25 17:25:15 2009
create undo tablespace undotbs2
Completed: create undo tablespace undotbs2
Thu Jun 25 17:25:26 2009
create undo tablespace undotbs3
Completed: create undo tablespace undotbs3
Thu Jun 25 17:25:44 2009
Successfully onlined Undo Tablespace 6.
Undo Segment 1 Offlined
Undo Segment 2 Offlined
Undo Tablespace 2 successfully switched out.
ALTER SYSTEM SET undo_tablespace='UNDOTBS3' SCOPE=BOTH;
drop tablespace undotbs1
Completed: drop tablespace undotbs1
Thu Jun 25 17:27:02 2009
Errors in file d:oraclediagrdbmsr11gr11gtracer11g_ora_4840.trc (incident=106018):
ORA-00600: 內部錯誤程式碼, 引數: [4097], [], [], [], [], [], [], []
Incident details in: d:oraclediagrdbmsr11gr11gincidentincdir_106018r11g_ora_4840_i106018.trc
Errors in file d:oraclediagrdbmsr11gr11gtracer11g_ora_4840.trc (incident=106019):
ORA-00603: ORACLE 伺服器會話因致命錯誤而終止
ORA-00600: 內部錯誤程式碼, 引數: [4097], [], [], [], [], [], [], []
Incident details in: d:oraclediagrdbmsr11gr11gincidentincdir_106019r11g_ora_4840_i106019.trc
Thu Jun 25 17:27:04 2009
Trace dumping is performing id=[cdmp_20090625172704]
ORA-603 : opiodr aborting process unknown ospid (3912_4840)

SQL> drop tablespace undotbs2;

表空間已刪除。

SQL> select 'drop rollback segment "'||name||'";' from undo$ where file# in (2,
);

'DROPROLLBACKSEGMENT"'||NAME||'";'
-------------------------------------------------------
drop rollback segment "_SYSSMU3_1245921918$";
drop rollback segment "_SYSSMU4_1245921918$";
drop rollback segment "_SYSSMU5_1245921918$";
drop rollback segment "_SYSSMU6_1245921918$";
drop rollback segment "_SYSSMU7_1245921918$";
drop rollback segment "_SYSSMU8_1245921918$";
drop rollback segment "_SYSSMU9_1245921918$";
drop rollback segment "_SYSSMU10_1245921918$";
drop rollback segment "_SYSSMU11_1245921918$";
drop rollback segment "_SYSSMU12_1245921918$";

已選擇10行。

SQL> drop rollback segment "_SYSSMU3_1245921918$";

回退段已刪除。

SQL> drop rollback segment "_SYSSMU4_1245921918$";

回退段已刪除。

SQL> drop rollback segment "_SYSSMU5_1245921918$";

回退段已刪除。

SQL> drop rollback segment "_SYSSMU6_1245921918$";

回退段已刪除。

SQL> drop rollback segment "_SYSSMU7_1245921918$";

回退段已刪除。

SQL> drop rollback segment "_SYSSMU8_1245921918$";

回退段已刪除。

SQL> drop rollback segment "_SYSSMU9_1245921918$";

回退段已刪除。

SQL> drop rollback segment "_SYSSMU10_1245921918$";

回退段已刪除。

SQL> drop rollback segment "_SYSSMU11_1245921918$";

回退段已刪除。

SQL> drop rollback segment "_SYSSMU12_1245921918$";

回退段已刪除。

SQL> @E:BakORACLEchensq

Creating package CHENSQ
=======================

程式包已建立。

Creating function BLK_NROW
==========================

函式已建立。

Creating function BLK_VALUE
===========================

警告: 建立的函式帶有編譯錯誤。

Creating function CPY
=====================

警告: 建立的函式帶有編譯錯誤。

Creating function CSOUND
========================

函式已建立。

Creating function DATABASE_NAME
===============================

函式已建立。

Creating function DATA_BLK_HDLEN
================================

函式已建立。

Creating function DATA_BLK_NROW
===============================

函式已建立。

Creating function DATA_BLK_OFFS
===============================

警告: 建立的函式帶有編譯錯誤。

Creating function DATA_BLK_VALUE
================================

函式已建立。

Creating function DUMP_HEX_TO_ACT
=================================

函式已建立。

Creating function RMB
=====================

函式已建立。

Creating function TAB_BLOCK_USED_RATIO
======================================

函式已建立。

Creating function TO_ROWID
==========================

函式已建立。

Creating procedure DMP_BLK
==========================

過程已建立。

Creating procedure DUMP_BLK
===========================

過程已建立。

Creating procedure PRO_BLK_REC
==============================

警告: 建立的過程帶有編譯錯誤。

Creating procedure PRO_BLK_VALUE
================================

過程已建立。

Creating package body CHENSQ
============================

程式包體已建立。