案例學習Oracle錯誤： ORA-00604 error occurred at recursive SQL level string

ORA-00604 error occurred at recursive SQL level string

　　Cause：An error occurred while processing a recursive SQL statement （a statement applying to internal dictionary tables）。

　　Action：If the situation described in the next error on the stack can be corrected， do so； otherwise contact Oracle Customer Support.

　　ORA-00604： 遞迴某個SQL 層時出現錯誤

　　原因：在執行一條遞迴SQL語句（該語句將應用於對內部表或資料字典的操作）時，發生錯誤。

　　方案：如果上述描述的錯誤所在棧可以被修復，則修復並繼續執行；否則，請聯絡Oracle客服。當然，那是Oracle官方的解決辦法。我曾經記得有個高手總結了關於ORA-00604/ORA-04031問題的解決：

　　修改INIT.ora

　　新增

_db_handles_cached = 0

　　並重新啟動資料庫。

　　分析：ORA-00604這個資訊表明，在資料庫執行內部SQL語句時，發生了錯誤。比如，要往表中插入一行資料，但沒有可擴充套件的空間。ORACLE於是去查尋，哪兒可以建立下一個擴充套件空間，它有多大小，但沒有成功。一般在發生ORA-00604錯誤時，還伴隨著其它的錯誤，例如：ORA-1547等。

　　首先，應當檢查警告檔案alertSID.log，查詢有關ORA-600類的資訊。

　　該錯誤最常見的原因是資料庫檔案initSID.ora中的引數OPEN_CURSORS值太小。可以修改initSID.ora檔案，OPEN_CURSORS的值一般為255.修改完後，宕下ORACLE，再重新啟動。

　　還可以設定並啟動資料庫的事件跟蹤功能。在initSID.ora中加上一行：

event = "00604 trace name errorstack"

　　宕下並重新啟動ORACLE，使這個事件跟蹤引數起作用。這樣，當再發生ORA-604錯誤時，有關資訊就儲存在TRACE檔案中。

　　造成ORA-604錯誤的其它原因可能有：

　　- initSID.ora中，引數DC_FREE_EXTENTS或ROW_CACHE_ENQUEUES太低。可以根據作業系統和資料庫的情況，適當增加這兩個引數的值，宕下並重新啟動ORACLE.

　　- 執行超出空間（伴隨ORA-1547錯誤）。這時，要對錶空間新增新檔案，即增加表空間的大小。

　　- 達到了MAX_EXTENTS（伴隨ORA-1556錯誤）。如果這樣，就要修改表，允許更多的擴充套件。請從技術手冊中查詢MAX_EXTENTS的最大值。如果已經達到了最大值，必須用compress extents選項，把表卸出（export），再匯入（import）資料庫中。

　　案例一：Oracle執行遞迴查詢的時候出錯

　　問題描述：我經常遇到ORA-00604 和ORA-01000（開啟遊標數量達到最大值）錯誤。然而，當我檢查程式碼的時候，所有的結果集和語句物件都在最後的塊中關閉了（我使用的是JDBC）。我執行的查詢是一個Oracle遞迴查詢（以這個開始並透過這個連線）。您能告訴我是哪裡出現了問題，以及在什麼樣的情況下會出現上述的錯誤嗎？

　　解決方案：可能是init.ora 檔案中的open_cursors 引數值的設定太低了。這個引數的預設值是非常低的（50）。它應該設定為200或者更高。即使是你關閉了結果集，但是你並沒有在JAVA程式碼中關閉SQL語句，就會導致這個問題。

　　如果設定為yes的話，那麼確保你的活動連線池啟用了（為了效能的原因），否則設定為no.

　　請你的資料庫管理員監視資料庫，並看看使用V$OPEN_CURSORS 和 V$SYSSTAT資料字典檢視的條目。

案例二：Exp出錯的一個案例

　　問題描述：客戶用的Linux系統，Redhat 企業版（RHEL 3.0）。資料庫，安裝的9iR2， 前一段時間升級過。現在的版本是9204.

　　客戶準備要做Exp匯出，以前一直系統沒有空間。先給給系統擴了一些空間。Linux下的LVM還算比較好用。雖然檔案系統用的是ext3 ，要暫時停機。

　　進行匯出操作，不成功，發現系統報告錯誤：

EXP-00056: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00000: Export terminated unsuccessfully

　　很多朋友可能對這個錯誤都很熟悉。

　　哦，對了，客戶說是升級過資料庫，首先猜測是不是升級有問題？畢竟在論壇上類似升級不成功的問題看過很多了。

　　執行$ORACLE_HOME/rdbms/admin/catpatch.sql 指令碼。

　　同時要注意調大java_pool_size 和shared_pool_size這兩個引數的大小，要不重新來就耽誤時間了，不要犯低階錯誤

SQL>shutdown immediate;
SQL>startup migrate;
SQL>@?/rdbms/admin/catpatch.sql

　　之後檢視Spool 出來的日誌。 發現有編譯錯誤，重新執行了第二次。 等待……之有這個時候我才想起才抱怨CPU不夠快，記憶體不夠大 ；）

　　這次Log沒錯誤。不料想……使用者連線報告錯誤：

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC_EVENTS
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2

　　發現connect / as sysdba 還是可以登陸進去的。

　　看來是 LBACSYS.LBAC_EVENTS的狀態有點問題。聯接進去，編譯一下如何？ 我的如意算盤是@？/rdbms/admin/utlrp.sql執行一下就沒有問題了，不料根本沒有用，錯誤依然。當時有些頭暈，這系統還沒有備份呢，看來有些麻煩了（心裡暗地埋怨客戶，一直不讓備份，總說"等等再說"，作為一個DBA說話總不被重視也挺悲哀的不是？ ，雖然我自己偷著有個備份，不過還是上次升級時候的呢），趕緊上網Metalink查查，這裡網路速度還不錯 LBACSYS.LBAC_EVENTS 作為關鍵詞，找到如下的資訊：

The reason for this problem seems to be an Upgrade for Label-Security
even if it's not installed. 　//Label security 沒有安裝，居然補丁去預設給升級?

　　解決方案：

shutdown immediate;
startup migrate;
alter view lbacsys.lbac$all_table_policies compile;
alter package lbacsys.lbac_events compile body;
shutdown immediate;
startup;

　　支援人員說這是個Bug.但是普通使用者不可見。 不太放心，再找找，在Suse.com站點的Maillist也發現了一則類似的案例，看來還可以，心裡有底了。

　　按照上面的執行，重新檢查，OK.

　　總結一下

　　其實是一個很沒有技術含量的Case.首先以前升級的時候至少要測試一下Export是否可以（Export已經成為升級成功的一個標誌了！） 其次，準備不夠充分，早成了手忙腳亂。所幸不是關鍵系統，使用者還可以容忍。Oracle 總說微軟是個爛公司，其實他們才真的夠欄。Bug多的不可勝數。

案例三：使用網路應用程式的時候出現遞迴SQL錯誤

　　問題描述：當我使用網路應用程式的時候，遇到了下面的這個錯誤。

　　ORA-00604： 遞迴SQL1級的時候出現錯誤。

　　ORA-04031： 無法分配4200位元組的共享記憶體，"RBKS_BK_INFO"， "sga_heap"， "library cache".

　　這些錯誤資訊是什麼意思？我該如何解決它們？它們是在應用程式裡面還是資料庫裡面？

　　解決方案：您應該使用的是Oracle 8.1.7.4之前版本的Oracle.第一個錯誤資訊告訴你Oracle針對你的行為執行的SQL 語句失敗了。ORA-4031告訴你為什麼它會失敗。ORA-4031錯誤資訊的意思是你沒有獲得足夠的空閒空間。你可以增加你的SHARED_POOL_SIZE，重新啟動資料庫再拭一次。這個bug已經在後續的補丁包中修復了。如果你使用的不是這個版本，你可以應用一下補丁包。

　　案例四：Sql_trace進行Oracle診斷案例

　　問題說明：很多時候，在我們進行資料庫操作時，比如drop user，drop table等，經常會遇到這樣的錯誤

　　ORA-00604： error occurred at recursive SQL level 1 .

　　這樣的提示，很多時候是沒有絲毫用處的。本案例就這一類問題提供一個思路及方法供大家參考。

　　1. drop user出現問題

　　報出以下錯誤後退出

　　ORA-00604： error occurred at recursive SQL level 1

　　ORA-00942： table or view does not exist .

　　關於 recursive SQL 錯誤我們有必要做個簡單說明。

　　我們知道，當我們發出一條簡單的命令以後

　　Oracle資料庫要在後臺解析這條命令，並轉換為Oracle資料庫的一系列後臺操作。

　　這些後臺操作統稱為遞迴sql.

　　比如create table這樣一條簡單的DDL命令，Oracle資料庫在後臺，實際上要把這個命令轉換為對於obj$，tab$，col$等底層表的插入操作。Oracle所作的工作可能比我們有時候想的要複雜的多。

　　2.跟蹤問題

　　我們知道Oracle提供sql_trace的功能

　　可以用於跟蹤Oracle資料庫的後臺遞迴操作。

　　透過跟蹤檔案，我們可以找到問題的所在

　　以下是格式化（tkprof）後的輸出：

The following statement encountered a error during parse:
DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = 'WAPCOMM'
Error encountered: ORA-00942

　　Oracle把錯誤資訊首先呈現出來，我們看到ORA-00942錯誤是由於SDO_GEOM_METADATA_TABLE表/檢視不存在所致，問題由此可以定位。

　　對於這一類的錯誤，定位問題以後解決的方法就要依據具體問題原因而定了。

　　3.問題定位

　　對於本案例，透過Metalink獲得以下解釋：

Problem Description
The Oracle Spatial Option has been installed and you are encountering
the following errors while trying to drop a user, who has no spatial tables,
connected as SYSTEM:
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7
A 942 error trace shows the failing SQL statement as:
DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = ''
Solution Description

(1)

Create a synonym for SDO_GEOM_METADATA_TABLE under SYSTEM which points to
MDSYS.SDO_GEOM_METADATA_TABLE.

　　對於本例，為MDSYS.SDO_GEOM_METADATA_TABLE建立一個同義詞即可解決，是相對簡單的情況。

(2)

Now the user can be dropped connected as SYSTEM.
Related Documents
ORA-604 and ORA-942 Reported During DROP USER CASCA

4.實際處理

　　MDSYS.SDO_GEOM_METADATA_TABLE為Spatial物件

　　如果未使用Spatial選項，可以刪除

SQL> connect / as sysdbaConnected.
SQL> select * from dba_sdo_geom_metadata order by owner;
select * from dba_sdo_geom_metadata order by owner
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-04063: view "MDSYS.DBA_SDO_GEOM_METADATA" has errors
SQL> select object_name from dba_objects where object_name like '%SDO%';
OBJECT_NAME
ALL_SDO_GEOM_METADATA
ALL_SDO_INDEX_INFO
ALL_SDO_INDEX_METADATA
DBA_SDO_GEOM_METADATA
DBA_SDO_INDEX_INFO
DBA_SDO_INDEX_METADATA
....
DBA_SDO_GEOM_METADATA
DBA_SDO_INDEX_INFO
...
SDO_WITHIN_DISTANCE
USER_SDO_GEOM_METADATA
USER_SDO_INDEX_INFO
USER_SDO_INDEX_METADATA
88 rows selected.
SQL> drop user MDSYS cascade;
User dropped.
SQL> select owner,type_name from dba_types where type_name like 'SDO%';
no rows selected
SQL>
SQL> alter session set sql_trace=true;
Session altered.
SQL> drop user wapcomm;
User dropped.
SQL> alter session set sql_trace=false;
Session altered.
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

　　這時使用者得以順利drop

　　5.一點總結

　　使用sql_trace可以跟蹤資料庫的很多後臺操作有利於我們發現問題的所在，很多時候，我們想要研究Oracle的內部活動或後臺操作，也可以透過sql_trace跟蹤，sql_trace/10046 是Oracle提供的最為有效的診斷工具之一。

　　案例五：表更新時發生遞迴SQL2級失敗錯誤

　　問題描述：表更新的時候失敗了，並且生成了一條ORA-00604 錯誤資訊。這個錯誤發生在遞迴SQL 2級。

　　解決方案：不幸的是，這個錯誤並不能告訴你Oracle資料庫在錯誤發生的時候正要做什麼。當你執行一條SQL語句的時候，Oracle資料庫輝為你在幕後做很多事情。例如，考慮下面的SQL語句：

UPDATE emp SET sal = sal*1.05 WHERE empno=1001;

　　這條SQL語句給號碼為1001的僱員漲5%的工資。當你執行這條語句的時候，Oracle查詢資料目錄來確定是否有這個表或者你是否使用了同義字。一旦它找到了資料庫物件，Oracle查詢資料字典來判斷你是否擁有訪問這個物件的許可權。那麼，Oracle到底是如何與資料字典進行互動的呢？它執行一條自己的SQL 語句。這些Oracle為你執行的SQL語句被稱為“遞迴”SQL語句。你最初的SQL 語句是0級。Oracle為你執行的遞迴SQL語句是1級。有時候，一條遞迴SQL語句又會引起自己的遞迴SQL語句，就是2級。

　　在你的案例中，有一個2級的遞迴SQL語句正在執行，並且產生了問題。為了解決問題，你需要找出執行的是什麼遞迴SQL語句引起的錯誤。要做到這一點，你必須啟動會話中的追蹤。首先，執行下面的SQL 語句：

ALTER SESSION SET sql_trace=TRUE;

　　然後，執行你的更新語句。你會看到ORA-604 錯誤。接下來，執行下面的語句：

ALTER SESSION SET sql_trace=FALSE;

　　現在到你為資料庫定義的USER_DUMP_DEST 起始引數上的路徑去。那裡應該有一個時間戳為當前時間的檔案。那個就是你生成的追蹤檔案。你可以開啟檔案並檢查遞迴SQL語句，其中包括引起錯誤的一條。

案例六：連線資料庫使用者的時候遇到ORA-00604錯誤

　　問題描述：當我試圖連線到資料庫使用者的時候，得到了如下的錯誤資訊：ORA-00604：遞迴SQL 1級的時候出現錯誤。但是如果我使用資料庫管理員的角色的時候，使用者就能夠連線。系統使用者可以連線，但是scott 就不能連線。

　　解決方案：Oracle為你在幕後做了很多的工作。它在自己的SQL 語句的全過程中進行這項工作。Oracle釋出給你的任何的SQL 語句都是“遞迴的SQL”語句。應該有很多的SQL 語句會引起你遇到的問題。我建議你所做的就是在INIT.ORA檔案中設定SQL_TRACE=TRUE，然後重新啟動資料庫。然後複製ORA-604錯誤。這會在你的USER_DUMP_DEST目錄中生成所有使用者程式的大量追蹤檔案。在錯誤發生之後，立即關閉資料庫，並設定SQL_TRACE=FALSE.然後再一次啟動資料庫。現在透過追蹤檔案，你就可以USER_DUMP_DEST目錄中生成的追蹤檔案中查詢ORA-604錯誤那一條資訊。就在那裡，你就發現ORA-604錯誤是哪一個遞迴SQL語句產生的，以及實際發生的錯誤情況。你的解決方案依賴於語句和實際的錯誤。

　　案例七：有人Move了系統表Dependencie$表， Crash了

　　今天有人問我這樣之後能不能恢復， 我想基本上已經不能了。 在open時報ORA-01092號錯誤， 我查了一下event也沒有這方面的合適的event啊， 我推薦用不完全恢復， 不過好象是沒有備份， 執行在noarchivelog模式。

　　從trc檔案中得到的內容：

KCRA: buffers claimed = 0/0, eliminated = 0
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable state
oerr ora 704
00704, 00000, "bootstrap process failure"
// *Cause: Failure in processing bootstrap data - see accompanying error.
// *Action: Contact your customer support representative.
SQL_TRACE開啟的情況下生成的Trace:
PARSING IN CURSOR #9 len=84 dep=2 uid=0 oct=3 lid=0 tim=18446744073254091198
hv=2287793623 ad='66f6c06c'
select o.name, u.name from obj$ o, user$ u where o.obj# = :1 and o.owner# = u.user#
END OF STMT
PARSE #9:c=0,e=343,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=0,tim=18446744073254091193
EXEC #9:c=0,e=186,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=18446744073254091456
FETCH #9:c=0,e=28019,p=2,cr=5,cu=0,mis=0,r=1,dep=2,og=4,tim=18446744073254119501
STAT #9 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS '
STAT #9 id=2 cnt=1 pid=1 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ#(18) '
STAT #9 id=3 cnt=1 pid=2 pos=1 obj=36 op='INDEX UNIQUE SCAN OBJ#(36) '
STAT #9 id=4 cnt=1 pid=1 pos=2 obj=22 op='TABLE ACCESS CLUSTER OBJ#(22) '
STAT #9 id=5 cnt=1 pid=4 pos=1 obj=11 op='INDEX UNIQUE SCAN OBJ#(11) '
ORA-00704: 載入程式程式失敗
ORA-00604: 遞迴 SQL 層 1 出現錯誤
ORA-01502: 索引'SYS.I_DEPENDENCY1'或這類索引的分割槽處於不可用狀態
EXEC #1:c=109375,e=5578667,p=44,cr=616,cu=1,mis=0,r=0,dep=0,og=4,
tim=18446744073255895570
ERROR #1:err=1092 tim=23012387

　　DBA做事一定要細心， 在執行批處理時一定要審了再審。

　　補充：

　　後來我用AnySQL UnLoader去恢復資料了， 和客戶一起花了24小時， 最後他們說OK了。

　　Eygle和Chensq對這個問題也有研究， 他們想出了更好的辦法解決此事， 不過最後原來的庫肯定是不能再用了， 必須要exp/imp到別的庫了， 我是用AUL幫客戶恢復資料的， 資料量在30G以上。

案例八：ORA-00604：遞迴SQL產生的錯誤

　　問題描述：我有一個Pro*c 的程式，有時候會給出下列的錯誤資訊：

　　ORA-00604：遞迴SQL 1級上產生錯誤

　　你能告訴為什麼會出現這個錯誤，它什麼時候出現，以及可能的解決方案是什麼嗎？

　　解決方案：無論你什麼時候執行查詢，系統都會在後臺執行一些查詢來判斷許多事情，例如“你是否有許可權來執行這個查詢？”，“你要訪問的這個物件是否存在？”。這些系統執行的查詢被稱為“遞迴SQL”。有時候，一個遞迴SQL語句需要呼叫自身的遞迴SQL.那麼這些執行的遞迴SQL語句就是另一個級別的，2級。

　　你不會在SQL*Plus 中看到遞迴SQL語句。要檢視它們的最好的方式就是開啟會話中的追蹤。啟動SQL*Plus ，執行下列命令：

ALTER SESSION SET sql_trace=TRUE;

　　然後執行你的程式，直到崩潰。繼續，並關閉SQL*Plus .現在到USER_DUMP_DEST 目錄中。那裡會生成一個追蹤檔案給你。檢視追蹤檔案中的有關ORA錯誤的資訊。這就是問題產生的根源。糾正ORA錯誤就會防止ORA-600錯誤再次出現。

　　大多數的ORA-600錯誤都可以透過以SYS登入，並從ORACLE_HOME/rdbms/admin 執行CATALOG 和 CATPROC 來予以糾正。