oracle手記chapter現(xiàn)任云和恩墨CTO

1、Yangtingkun 的DBA 工作手記編者按：老楊在網(wǎng)絡(luò)上的博客數(shù)年來一直維持著頻繁更新的記 錄，積累了近千篇的技術(shù)文章，在本書的組稿過程中，老楊將自己多年來的積累整理、精煉、講解出來，與讀者們他技術(shù)上的結(jié)晶。這一章是老楊的 DBA 手記。DBA 的工作可以苦中作樂， 也可以樂在其中，讓我們首先來看看老楊如何用 SQL 來為我們解釋一個魔術(shù)楊廷琨（網(wǎng)名 Yangtingkun） 現(xiàn)任云和恩墨 CTO，ITPUB 論壇 Oracle 數(shù)據(jù)庫管理版版主。2004 年曾參與編寫Oracle 數(shù)據(jù)庫性能優(yōu)化一書，2007 年被 Oracle 公司授予 Oracle ACE 稱號，喜歡研究 Orac

2、le 相關(guān)的技術(shù)問題，他的技術(shù)博客上積累了 1500 多篇 Oracle 相關(guān)的 技術(shù)文章。個人技術(shù)博客：數(shù)據(jù)驅(qū)動，成就未來Yangtingkun 的 DBA 工作手記利用 SQL 解釋一個魔術(shù)一個偶然的機(jī)會在電視上看到一個有關(guān)牌的魔術(shù)，覺得很有意思。這個魔術(shù)明顯不是靠手快或者做假 來實現(xiàn)的，奧妙在于魔術(shù)中包含了數(shù)學(xué)原理。首先描述一下這個魔術(shù)，有的話，可以按照這個方法試一試。從一副牌中隨意抽取 21 張牌。讓觀眾從這些牌中隨意選擇一張，這張牌就是最后通過魔術(shù)需要找到的目標(biāo)牌。讓觀眾牢記后將其放回到其余 20 張牌中，然后任意洗牌。下面開始進(jìn)行發(fā)牌的工作，發(fā)牌和普通的發(fā)牌規(guī)則一樣。將牌發(fā)成 3

3、疊，每疊 7 張。將每疊牌依次展示給觀眾，要求觀眾確認(rèn)目標(biāo)牌在 3 疊的哪一疊中即可。之后將 3 疊牌合在一起，將包含目標(biāo)牌的一疊放在其他兩疊牌中間。注意此時不要打亂每疊牌的順序。然后再次發(fā)牌，和剛才完全一樣，還是將牌發(fā)成 3 疊。讓對方確認(rèn)目標(biāo)牌所在的一疊，將這疊牌放到另外兩疊牌的中間。最后，再次重復(fù)上面的發(fā)牌、確認(rèn)此過程，仍然將包含目標(biāo)牌的那疊牌，放到另外兩疊牌的中間。下面神奇的時刻到來了：從這疊牌的上面每次拿起一張，每拿起一張牌的同時要說一句話：“你要相信魔術(shù)你的牌是”。說完這句話，下一張牌就是目標(biāo)牌了?？瓷先ミ@個魔術(shù)很神奇，而且最神奇的是，這個魔術(shù)任何人都可以來表演。這就說明無論這張牌

4、最初在哪 個位置，只要按照這個規(guī)則最后都一定會來到這個指定的位置?？戳诉@個魔術(shù)，不禁有點手癢，既然是 DBA 出身，就用 SQL 來演示一下這個魔術(shù)的過程吧，見如下代碼：SQL> WITH A AS23456789(SELECT ROWNUM P FROM DUAL CONNECT BY LEVEL <= 21) SELECT7 + CEIL( (7 + CEIL(7 + CEIL(P/3)/3)/3) FROM A;7+CEIL(7+CEIL(7+CEIL(P/3)/3)/3)111111111111111111111111111111111111·46·數(shù)據(jù)

5、驅(qū)動，成就未來Yangtingkun 的 DBA 工作手記111111已選擇 21 行。簡單地解釋一下這個 SQL：設(shè) WITH中的 P 表示這張牌的初始位置，這個位置的取值范圍是從 1 到21。而后將牌按照發(fā)牌的順序分成 3 份，于是這張牌的位置變?yōu)?CEIL(P/3)。由于在魔術(shù)表演的過程中，目標(biāo)牌所在的那一疊牌會放在其他兩疊牌的中間，也就是說目標(biāo)牌的前面增加了 7 張牌，因此目標(biāo)牌的位置要增加 7。魔術(shù)中上面的步驟重復(fù)了 3 次，因此在 SQL 中這個過程也重復(fù) 3 次，最終 SQL 返回的結(jié)果就是目標(biāo)牌 21種不同初始位置所對應(yīng)的魔術(shù)結(jié)束時刻目標(biāo)牌的最終位置。根據(jù)計算結(jié)果可以看到，無論

6、這張牌在哪里，最終都會達(dá)到第 11 張的位置。這也就是這個魔術(shù)的奧秘之所在。不過 SQL 只是演示了結(jié)果，并沒有給出為什么會出現(xiàn)這種結(jié)果的，下面通過數(shù)學(xué)簡單分析一下：由于第一次平均分的時候這張牌的位置是任意的，所以這次平均分的意義不大。這次平均分的目的只是將 目標(biāo)牌的那一份放到中間的位置。所以可以認(rèn)為這張牌在中間位置第 1 到 7 的任何一個位置上，因此這張牌的位置就是 7 + p。下面將牌分成三份，然后將目標(biāo)牌堆放到中間，這時這張牌的位置變?yōu)?7 + (7+p)/3。最后再重復(fù)一次這個動作，最終結(jié)果變?yōu)椋? + (7 + (7+p)/3)/3。對上面的表達(dá)式進(jìn)行通分計算后，結(jié)果變成(7*9

7、+ 7*3 + 7 + p)/9，進(jìn)一步簡化變成(91 + p)/9，最后變成了10 + (1+p)/9，而 p 的位置是 1 到 7，也就是說無論取何值，(1+p)/9 都最后，應(yīng)該修改一下魔術(shù)中 語：“你要相信數(shù)學(xué)你的牌是”。大于 1，所以最終的結(jié)果是 11。ORA-600(17069)錯誤的解決過程在一個報表數(shù)據(jù)庫發(fā)現(xiàn)了這個錯誤，詳細(xì)的錯誤信息為：Fri Feb 20 08:16:44 2009Errors in file /u1/oracle/admin/repdb01/bdump/repdb01_j015_5099.trc:ORA-00600: internal error code

8、, arguments: 17069, 0x6A5DEE1E0, , , , , , Fri Feb 20 08:16:47 2009Errors in file /u1/oracle/admin/repdb01/bdump/repdb01_j015_5099.trc:ORA-00600: internal error code, arguments: 17069, 0x6A5DEE1E0, , , , , , 進(jìn)一步檢查對應(yīng)的 trace 文件：bash-2.03$ more /u1/oracle/admin/repdb01/bdump/repdb01_j015_5099.trc/u1/or

9、acle/admin/repdb01/bdump/repdb01_j015_5099.trcOracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 ProductionORACLE_HOME = /data/oracle/product/920System name: Node name: Release: Version:Machine:SunOS n

10、ewreport 5.8Generic_117350-26sun4uInstance name: repdb01Redo thread mounted by this instance: 1 Oracle process number: 35·47·數(shù)據(jù)驅(qū)動，成就未來Yangtingkun 的 DBA 工作手記Unix process pid: 5099, image: oraclenewreport (J015)* SESSION ID:(12.28191) 2009-02-20 08:16:44.060* 2009-02-20 08:16:44.060ksedmp: i

11、nternal or fatal errorORA-00600: internal error code, arguments: 17069, 0x6A5DEE1E0, , , , , , Current SQL statement for this session:DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN P_GENERATE_REPDATA('FR20T0000020000000000032'); :mydate := next_

12、date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;- Call Stack Trace -callinglocationcalltypeentrypointargument values in hex(? means dubious value)ksedmp()+328CALLksedst()+0FFFFFFFF7FFF6430 ? 000000000 ? 000000000 ?00000003E ? FFFFFFFF7FFF6CC8 ? 1031D56C8 ?000000000 ? 000103400 ?0001035D9 ? 0

13、00102C00 ?1035D9000 ? 1035D9C28 ?kgeriv()+208PTR_CALL 0000000000000000從 trace 文件包含的進(jìn)程名稱 j015 來看，導(dǎo)致問題是一個 JOB。從 trace 文件中包含的錯誤語句則更加證實了這一點。由于這個 JOB 在數(shù)據(jù)庫中已經(jīng)運行了很長時間，一直沒有出現(xiàn)過錯誤?，F(xiàn)在運行報錯，肯定是由于其他的外部導(dǎo)致 JOB 運行的異常。大部分的 ORA-600 錯誤在 Mink 上都有詳細(xì)的描述，于是了 Mink：文檔 Doc ID: 39616.1 中匯總了 ORA-600(17069)錯誤的已知 Bug，不過這些 Bug 的描述

14、都與當(dāng)前問題的現(xiàn)象并不太相符。不過文檔中還是包含了一些有價值的信息。文檔中描述 ORA-600(17069)錯誤的第二個參數(shù)代表 Library Cache Object Handle， 這里的值為 0x6A5DEE1E0?？雌饋韱栴}可能和LATCH 有關(guān)，但是根據(jù)錯誤信息所顯示的地址，在 V$LATCH 和V$LATCH_CHILDREN視圖中都沒有找到有價值的信息。從錯誤信息這個方向入手找不到什么有價值的信息了，現(xiàn)在只能回過頭來檢查發(fā)生錯誤的 JOB。根據(jù) JOB的特性，在運行失敗后這個 JOB 會自動再次執(zhí)行，檢查 JOB 運行時的 V$LOCK 信息：SQL> SELECT AD

15、DR, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK2 FROM V$LOCK WHERE SID = 75;ADDRTYID1ID2LMODEREQUESTBLOCK0000000690342780 CU -1.703E+0966366000000000006903426F8 JQ0在 V$LOCK 中沒有什么特別的信息，接著檢查 V$SESSION_WAIT，看看這個 JOB 在等待什么：很明顯，結(jié)果中 P1RAW 的值就是 ORA-600(17069)錯誤的第二個參數(shù)，配合等待信息基本上可以確定問題就是出現(xiàn)在 LIBRARY CACHE PIN 的過程中。再

16、次查看 Mink 信息，Oracle 指出這個錯誤的·48·SQL> SELECT EVENT, P1TEXT, P1RAW, P2TEXT, P2RAW, STATE2 FROM V$SESSION_WAIT WHERE SID = 75;EVENTP1TEXTP1RAWP2TEXTP2RAWSTATElibrary cache pin handle address 00000006A5DEE1E0 pin address 00000006B1A971A8 WAITING數(shù)據(jù)驅(qū)動，成就未來Yangtingkun 的 DBA 工作手記多半是：運行時間很長的 PROCE

17、DURE 在執(zhí)行過程中，所依賴的對象被編譯或者刪除了。檢查出錯 JOB 所調(diào)用過程的狀態(tài)：SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS234FROM DBA_OBJECTSWHERE OWNER = 'FUJIANREP'AND OBJECT_NAME = 'P_GENERATE_REPDATA'OWNEROBJECT_NAMEOBJECT_TYPESTATUSFUJIANREPP_GENERATE_REPDATAPROCEDUREINVALID果然，出錯過程的狀態(tài)是不正常的。在修正錯誤前，首先將

18、JOB 置于 BROKEN 狀態(tài)以避免 JOB 再次運行：在操作系統(tǒng)級殺掉 JOB 對應(yīng)的 PROCESS：現(xiàn)在 JOB 調(diào)用已經(jīng)被終止，可以手工重新編譯過程了：編譯報錯，錯誤信息指出沒有獲得編譯對象所需的鎖，而導(dǎo)致超時錯誤的發(fā)生。由于從 V$LOCK 和 V$LATCH 視圖中都無法獲得有意義的信息，只能檢查是否有其他人當(dāng)前在P_GENERATE_REPDATA 所依賴的對象：SQL> SELECT * FROM V$ACCESS234WHERE (OWNER, OBJECT) IN(SELECT REFERENCED_OWNER, REFERENCED_NAME FROM DBA_D

19、EPENDENCIES WHERE OWNER = 'FUJIANREP' AND NAME = 'P_GENERATE_REPDATA');SID OWNEROBJECTTYPE545454545454545454545454FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREPFUJIANREPCAT_BUYER CAT_CATEGORY CAT_DOSEAGE_FORM CAT_DRUG CAT

20、_ENTERPRISE CAT_METRIC CAT_ORG CAT_PRODUCTCAT_QUALITY_DEFINE GOV_CAT_BUYER GOV_CAT_ENTERPRISEGOV_S_MO_BUSYNONYM SYNONYM SYNONYM SYNONYM SYNONYM SYNONYM SYNONYM SYNONYM SYNONYM TABLE TABLETABLE·49·SQL> ALTER PROCEDURE P_GENERATE_REPDATA COMPILE; ALTER PROCEDURE P_GENERATE_REPDATA COMPILE

21、*ERROR at line 1:ORA-04021: timeout occurred while waiting to lock object FUJIANREP.P_GENERATE_REPDATASQL> SELECT SPIDSPID FROM V$PROCESS WHERE ADDR IN (SELECT PADDR FROM V$SESSION WHERE SID = 75);14927SQL> HOST kill -9 14927SQL> EXEC DBMS_JOB.BROKEN(63, TRUE)PL/SQL procedure successfully c

22、ompleted. SQL> COMMIT;Commit complete.數(shù)據(jù)驅(qū)動，成就未來Yangtingkun 的 DBA 工作手記545454545454545454545454545454545454545454545454545454545454545454545454FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIANREP FUJIAN

23、REP FUJIANREP FUJIANREP FUJIANREP FUJIANREP NDMAIN NDMAIN NDMAIN NDMAIN NDMAIN NDMAIN NDMAIN NDMAIN NDMAIN NDMAIN NDMAIN NDMAIN NDMAIN NDMAIN PUBLICSYSGOV_S_MO_BU_EN GOV_S_MO_BU_PR GOV_S_MO_EN GOV_S_MO_ME GOV_S_MO_ME_CA GOV_S_MO_ME_PR GOV_S_MO_ORDER GOV_S_YE_ORDER GRP_HOSPITAL GRP_LEVEL ORD_ORDER OR

24、D_ORDER_ITEM ORD_ORDER_ITEM_REP ORD_ORDER_RECEIVEORD_ORDER_RECEIVE_REP ORD_ORDER_REP ORD_ORDER_RETURN ORD_ORDER_RETURN_REP PLT_PLAT USER_TAB_PARTITIONS CAT_BUYER CAT_CATEGORY CAT_DOSEAGE_FORM CAT_DRUG CAT_ENTERPRISE CAT_METRICCAT_ORG CAT_PRODUCT CAT_QUALITY_DEFINE ORD_ORDER ORD_ORDER_ITEM ORD_ORDER_

25、RECEIVE ORD_ORDER_RETURN PLT_PLATUSER_TAB_PARTITIONS STANDARDSTANDARD SYS_STUB_FOR_PURITY_ANALYSISUSER_TAB_PARTITIONSTABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE CURSOR TABLE SYNONYM CURSOR TABLE CURSOR CURSOR CURSOR TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE VI

26、EW VIEW VIEW VIEW TABLE SYNONYM PACKAGE PACKAGE PACKAGEVIEW145 SYS54 SYS54 SYS51 rows selected.過程依賴的對象果然被其他人所，檢查這個會話的信息：沒想到是同事通過 pldevelop 連接的會話，看看這個會話在做什么：·50·SQL> SELECT SQL_TEXT FROM V$SQL2 WHERE ADDRESS IN (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID = 54); SQL_TEXTSQL> SELECT SI

27、D, SERIAL#, USERNAME, PROGRAM, TERMINAL2 FROM V$SESSION WHERE SID = 54;SIDSERIAL# USERNAMEPROGRAMTERMINAL5426216 FUJIANREPPlSqlDev.exe LIBY數(shù)據(jù)驅(qū)動，成就未來Yangtingkun 的 DBA 工作手記居然是 TRUNCATE 分區(qū)的操作，難怪會導(dǎo)致過程處于 INVALID 狀態(tài)，不過 TRUNCATE 操作應(yīng)該續(xù)很長時間，而導(dǎo)致問題產(chǎn)生的語句理論上應(yīng)該已經(jīng)運行很久了：持這個 TRUNCATE 的等待時間已經(jīng)超過 10 天了，很顯然這是一個僵死的會話。應(yīng)從K

28、ill 掉對應(yīng)的進(jìn)程：切換為 FUJIANREP 用戶，再次編譯過程：至此問題解決。將 JOB 重新設(shè)置 BROKEN 即可。問題解決后再次檢查過程，發(fā)現(xiàn) TRUNCATE 語句居然就是這個過程的一部分。這個過程會先執(zhí)行TRUNCATE，然后執(zhí)行等 DML 語句。所有問題都搞清楚了：問題出在手工執(zhí)行出錯的過程中，可能由于網(wǎng)絡(luò)的導(dǎo)致客戶端與數(shù)據(jù)庫端失去，數(shù)據(jù)庫中的會話變成僵死狀態(tài)停在了 TRUNCATE TABLE 語句處。而執(zhí)行者只是中止了客戶端的請求，并沒有進(jìn)程的問題。等到 JOB 的運行時間一到，嘗試再次運行相同的過程時，發(fā)現(xiàn)過程處于運行狀態(tài)，且對一些表持有 LOCK和 LATCH，于是因

29、。了上面的 ORA-600(17079)錯誤。這也是隨后手工編譯 PROCEDURE 報錯 ORA-4021 的原V$SQL 視圖顯示結(jié)果異常的診斷有一次碰到一個很奇怪的問題，在檢查會話所執(zhí)行的 SQL 時，發(fā)現(xiàn) V$SQL 視圖中 SQL_TEXT 列中的數(shù)據(jù)是不正常的。由于 V$SQL 是動態(tài)性能視圖，里面保存的是當(dāng)前共享池中加載的 SQL 語句，所以如果這個 SQL 不是執(zhí)行很頻繁的話，那么它很可能會被替換出共享池?；蛘邤?shù)據(jù)庫意外重啟，也會導(dǎo)致這個 SQL 徹底丟失。那么首要任務(wù)就是保留現(xiàn)場，一旦錯誤不可再現(xiàn)，那么所有的問題就都無從查起了。將顯示異常的 V$SQL備份到了 BAK_V$S

30、QL 表中，首先看一下異常的 SQL 語句：·51·SQL> EXEC DBMS_JOB.BROKEN(63, FALSE)PL/SQL procedure successfully completed. SQL> COMMIT;Commit complete.SQL> ALTER PROCEDURE P_GENERATE_REPDATA COMPILE;Procedure altered.SQL> SELECT SPIDSPID FROM V$PROCESS WHERE ADDR IN (SELECT PADDR FROM V$SESSION WH

31、ERE SID = 54);12974SQL> HOST kill -9 12974SQL> SELECT EVENT, P1TEXT, P1, P2TEXT, P2, P3TEXT, P3, SECONDS_IN_WAIT2 FROM V$SESSION_WAIT WHERE SID = 54;EVENTP1TEXTP1 P2TEXTP2 P3TEXTP3 SECONDS_IN_WAITdb file sequential readfile#1 block#170158 blocks13995643ALTER TABLE GOV_S_MO_EN TRUNCATE PARTITIO

32、N P200901數(shù)據(jù)驅(qū)動，成就未來Yangtingkun 的 DBA 工作手記SQL> SELECT SQL_TEXT FROM BAK_V$SQL;SQL_TEXTinfo.CONTRACT_ITEM_ID,info.BUYER_ORG_ID ael 未承諾'DISCOUNT_STEP is null or INC.NUM_STEP is null then to_char(INC.DISCOUNT_STEP * 100,'0.0') | '%'劭勐?' |'未 CASH.CASH is null and CASH.CASH_T

33、HIRTY is null then '現(xiàn)款:' | to_char(CASH.CASH * 100,'0.0''30 日結(jié)款:'SH_THIRTY is not null then case whend end as CASH_DISCOUNT,0,'0.0') | '%;'en info.modify_date > inc.modify_date and info.modify_date > cash.modify_date then info.mo顯然這個 SQL 語句是不正常的，語句中甚至連 s

34、elect、insert、update、delete 命令都不包括。但是這個 SQL 全是亂碼，從顯示的部分看，大部分是有一定邏輯在里面的。觀察這個 SQL，第一感覺像是 V$SQL 視圖中顯示了部分 SQL，而沒有從 SQL 語句的開頭部而且即使是部分 SQL，也不是連續(xù)顯示的，因為連續(xù)的兩行并不連貫。始顯示。首先想到的就是 Oracle 的 Bug，因為除了這一點，暫時沒有想到其他的來解釋這個現(xiàn)象。那么如果確實是由于 Bug，導(dǎo)致 V$SQL 顯示整，那么完整的 SQL 又是什么呢，是否完整的 SQL 也會存在問題呢。剛才一直在V$SQL 視圖，下面不妨一下 V$SQLTEXT_WITH_

35、NEWLINES 視圖，看看這個視圖中的結(jié)果是否也是不正常的。并且可以對比兩個視圖的結(jié)果，看看從中能否找到一點線索。SQL> SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES2 WHERE HASH_VALUE IN (SELECT HASH_VALUE FROM BAK_V$SQL) ORDER BY PIECE; SQL_TEXTSELECT * FROM (SELECT ROWNUM as numrow, yy.* from ( selectinfo.record_id,info.CONTRACT_ITEM_ID,info.BUYER_ORG

36、_IDWHERE numrow >= 132 rows selected.出乎意料的是，從 V$SQLTEXT_WITH_NEWLINES 中，發(fā)現(xiàn) SQL 的結(jié)果是正常的，而且 V$SQL 中顯示的內(nèi)容在 V$SQLTEXT_WITH_NEWLINES 中都可以找到，只不過不是連續(xù)的。從這一點上看，似乎更可以肯定是 Bug 了。不過還存在幾個疑點：首先在 Mink 上沒有發(fā)現(xiàn)類似的描述，難道存在一個還沒有發(fā)現(xiàn)的 Bug。其次對比兩個視圖的結(jié)果，二者的差異完全沒法解釋。V$SQL 視圖中的 SQL_TEXT 截取了全部 SQL 語句的前 1000 個字節(jié)，而 V_SQLTEXT_WITH

37、_NEWLINES 視圖則包含全部的 SQL 內(nèi)容，但是結(jié)果是分行顯示的。沒有道理顯示結(jié)果是完全正常的，事實上前 1000 個字節(jié)就出現(xiàn)了錯誤。而且錯誤出現(xiàn)得那么離譜，很多信息都是跳著顯示的。關(guān)鍵是找不到這個 Bug 的。如果確實是顯示問題，那么應(yīng)該所有的 SQL 都會有問題。如果僅僅是這個 SQL 有問題，那么多半問題出在這個 SQL 本身。V$SQL 中的 SQL_TEXT 字段長度為 1000，對于長度大于 1000 的 SQL，會顯示前面 1000 個字符。從V$SQLTEXT_WITH_NEWLINES 視圖的結(jié)果看，SQL 的長度肯定超過了 1000，但從 V$SQL 中的看，長度

38、應(yīng)該遠(yuǎn)遠(yuǎn)小于 1000。結(jié)果來·52·數(shù)據(jù)驅(qū)動，成就未來Yangtingkun 的 DBA 工作手記一下 V$SQL 中 SQL_TEXT 的具體長度：長度為 980，這個長度倒是對的，出來的內(nèi)容卻很少，再次，將長度和內(nèi)容一起顯示：SQL> SELECT LENGTH(SQL_TEXT), SQL_TEXT FROM BAK_V$SQL;LENGTH(SQL_TEXT)SQL_TEXT980info.CONTRACT_ITEM_ID,info.BUYER_ORG_ID ael 未承諾'DISCOUNT_STEP is null or INC.NUM_STEP

39、is null then to_char(INC.DISCOUNT_STEP * 100,'0.0') | '%'劭勐?' |'未 CASH.CASH is null and CASH.CASH_THIRTY is null then '現(xiàn)款:' | to_char(CASH.CASH * 100,'0.0''30 日結(jié)款:'SH_THIRTY is not null then case whend end as CASH_DISCOUNT,0,'0.0') | '%;

40、9;en info.modify_date > inc.modify_date and info.modify_date > cash.modify_date then info.mo這個格式不是很美觀，設(shè)置 COL 調(diào)整一下輸出的格式：SQL> COL SQL_TEXT FORMAT A70 WRAPSQL> SELECT LENGTH(SQL_TEXT), SQL_TEXT FROM BAK_V$SQL; LENGTH(SQL_TEXT) SQL_TEXTinfo.T ROWNUM as numrow, yy.* from ( select info.CONTRAC

41、T_ITEM_ID,info.BUYER_ORG_ID as BUYER_ORwhen INC.DISCOUNT_STEP is null or INC.NUM_'起付諾'EP is null thento_char(額:' | to_char(INC.NUM_STEP) | '萬,折扣率:' | end as NUM_DISCOUNT,T_STEP * 100,'0.0') | '%'when CASH.CASH is null and CASH.CASH_THIRcase 信? is null then'現(xiàn)款:

42、' | to_char(CSH.CASH is not null then end''e ASH.CASH * 100,'0.0') | '%;''en CASH.CASH_THIRTY is not null then30 日結(jié)款:' | to_char(CASH.CASH_THIRTY * 100,'0.0') | '%;' case whend end as CASH_DISCOUNT,en info.modify_date > inc.modify_date and info.

43、modify_date > cash.modify_date then info.mo奇怪的事情出現(xiàn)了，不僅 SQL_TEXT 的長度內(nèi)容被覆蓋掉了，而且 SQL_TEXT 的內(nèi)容并沒有從SQL_TEXT的欄位開始，而是從一行的開始位置開始的。更關(guān)鍵的是，的內(nèi)容已經(jīng)發(fā)生了變化。到這里已經(jīng)可以確定問題的了，為了更加精確的問題，將 SQL_TEXT 中的內(nèi)容進(jìn)行 DUMP：SQL> SELECT DUMP(SUBSTR(SQL_TEXT, 1, 100), 16) FROM BAK_V$SQL;DUMP(SUBSTR(SQL_TEXT,1,100),16)Typ=1Len=100:20

44、,53,45,4c,45,43,54,20,2a,20,46,52,4f,4d,20,28,20,d,20,53,45,4c,45,43,54,20,52,4f,57,4e,55,4d,20,61,73,20,6e,75,6d·53·SQL> SELECT LENGTH(SQL_TEXT) FROM BAK_V$SQL; LENGTH(SQL_TEXT)980數(shù)據(jù)驅(qū)動，成就未來Yangtingkun 的 DBA 工作手記,72,6f,77,2c,20,79,79,2e,2a,20,66,72,6f,6d,20,28,20,73,65,6c,65,63,74,20,d,

45、20,69,6e,66,6f,2e, 72,65,63,6f,72,64,5f,69,64,2c,d,20,20,20,20,20,20,20,20,69,6e,66,6f,2e,43,4f,4e,54,52,41 SQL> SELECT SUBSTR(SQL_TEXT, 1, 100) FROM BAK_V$SQL;SUBSTR(SQL_TEXT,1,100)info.CONTRAumrow, yy.* from ( select從 DUMP 文件中已經(jīng)可以清晰地看到問題的了，SQL 語句中僅使用了 ASCII(0XD)回車符，而沒有使用 ASCII(0XA)換行。這會導(dǎo)致在 Unix

46、 和 Linux 環(huán)境下，第二行的數(shù)據(jù)仍然從第一行的第一列位置開始輸出， 這樣就會覆蓋第一行本來的內(nèi)容。一個簡單的例子如下：SQL> SELECT 'AB' | CHR(13) | 'C' FROM DUAL; 'AB'CB1 row selected.SQL> SELECT 'AB' | CHR(10) | CHR(13) | 'C' FROM DUAL; 'AB'|ABC1 row selected.正是這個造成了 V$SQL 中顯示不正常，而 V$SQLTEXT_WITH_NEWL

47、INES 中由于每行只個字符，因此還沒有被覆蓋就切換到下一條了。了解了問題的，剩下的事情就簡單了：SQL> SELECT REPLACE(SQL_TEXT, CHR(13), CHR(10) | CHR(13) FROM BAK_V$SQL;REPLACE(SQL_TEXT,CHR(13),CHR(10)|CHR(13)SELECT * FROM (SELECT ROWNUM as numrow, yy.* from ( select info.record_id,info.CONTRACT_ITEM_ID,info.BUYER_ORG_ID as BUYER_ORGID,case.en

48、d end as CASH_DISCOUNT,case when info.modify_date > inc.modify_date and info.modify_date > cash.modify_date then info.mo手工添加換行信息，就可以解決上面的問題?？此剖?Bug 的問題，其實并不是 Bug 引起的，而是不同操作系統(tǒng)的差異導(dǎo)致了這個問題。在 Windows 環(huán)境中，一個回車符就足夠了，但是對于 Unix 和 Linux 環(huán)境，還需要一個換行符。過程 ORA-4068 之錯誤在運行一個過程了一個 ORA-4068 錯誤。雖然問題很簡單而且解決過程也不復(fù)雜

49、，但是要真正理解錯·54·數(shù)據(jù)驅(qū)動，成就未來Yangtingkun 的 DBA 工作手記誤產(chǎn)生的，還要對概念有比較清晰的理解。下面用一個簡單的例子來重現(xiàn)錯誤：SQL> CREATE TABLE T AS SELECT * FROM TAB;表已創(chuàng)建。SQL> CREATE OR REPLACE PROCEDURE P_RECREATE AS23456BEGINEXECUTE IMMEDIATE 'DROP TABLE T'EXECUTE IMMEDIATE 'CREATE TABLE T AS SELECT * FROM TAB'

50、 END;/過程已創(chuàng)建。SQL> CREATE OR REPLACE PROCEDURE P_INSERT_T AS2345BEGININSERT INTO T SELECT * FROM T; END;/過程已創(chuàng)建。SQL> BEGIN2345P_RECREATE; P_INSERT_T;END;/BEGIN*第 1 行出現(xiàn)錯誤:ORA-04068: 已丟棄程序包的當(dāng)前狀態(tài)ORA-04065: 未執(zhí)行, 已更改或刪除 stored procedure "YANGTK.P_INSERT_T" ORA-06508: PL/SQL: 無法找到正在調(diào)用 : "

51、;YANGTK.P_INSERT_T" 的程序單元ORA-06512: 在 line 3上面建立了兩個過程，在第一個過程 P_RECREATE 中，通過動態(tài)語句 DROP TABLE T，然后通過動態(tài)語句 CREATE TABLE AS SELECT 來重建 T 表。第二個過程 P_INSERT_T 則更簡單，就是根據(jù) T 表的內(nèi)容實現(xiàn)自。將兩個過程放在同一個塊中調(diào)用，就會出現(xiàn)上面的 ORA-4068 錯誤。報錯：如果單獨執(zhí)行兩個過程，則看到這個 ORA-4068 錯誤，首先想到的是 P_RECREATE 過程對表進(jìn)行刪除并重建的操作，這會導(dǎo)致所有和這個 T 表相關(guān)的過程變?yōu)?INV

52、ALID 狀態(tài)。由于 T 表結(jié)構(gòu)在重建前面沒有發(fā)生變化，因此通過簡單的編譯，就可以使得 P_INSERT_T 過程的狀態(tài)變?yōu)閂ALID。因此，嘗試在調(diào)用過程之前重編譯 P_INSERT_T 過程：SQL> BEGIN234P_RECREATE;EXECUTE IMMEDIATE 'ALTER PROCEDURE P_INSERT_T COMPILE' P_INSERT_T;·55·SQL> EXEC P_RECREATEPL/SQL 過程已完成。SQL> EXEC P_INSERT_TPL/SQL 過程已完成。數(shù)據(jù)驅(qū)動，成就未來Yangti

53、ngkun 的 DBA 工作手記5 END;6 /BEGIN*第 1 行出現(xiàn)錯誤:ORA-04068: 已丟棄程序包的當(dāng)前狀態(tài)ORA-04065: 未執(zhí)行, 已更改或刪除 stored procedure "YANGTK.P_INSERT_T" ORA-06508: PL/SQL: 無法找到正在調(diào)用 : "YANGTK.P_INSERT_T" 的程序單元ORA-06512: 在 line 4錯誤依舊。而且有趣的是，由于錯誤發(fā)生在調(diào)用 P_INSERT_T 過程中，所以 alter procedure 命令顯然已經(jīng)執(zhí)行完成，而且已經(jīng)了。而這個命令的執(zhí)行但是

54、錯誤仍然發(fā)生了。SQL> BEGIN說明 P_INSERT_T 過程的狀態(tài)已經(jīng)恢復(fù)為 VALID 狀態(tài)了，2345P_RECREATE;EXECUTE IMMEDIATE 'BEGIN P_INSERT_T; END;' END;/PL/SQL 過程已完成。如果使用動態(tài) SQL 的方式調(diào)用 P_INSERT_T 過程，則問題已經(jīng)清楚了，但是要想說明白，還需要從頭說起。報錯。過程在編譯時，會自動檢查語法錯誤、權(quán)限及所有對象的依賴性。而等到執(zhí)行的時候，Oracle 則再進(jìn)行類似的檢查，而是直接運行過程，這也是過程擁有較高效率的之一。當(dāng)過程所依賴的對象發(fā)生了變化，Oracle

55、會自動將過程的狀態(tài)置為 INVALID，而過程的狀態(tài)如果為 INVALID，則會在下次執(zhí)行時嘗試重新編譯，如果編譯通過則繼續(xù)執(zhí)行；如果編譯失敗則報錯。這就是上面例子中兩個過程單獨執(zhí)行并報錯的。雖然 P_RECREATE 過程會重建 T 表，并導(dǎo)致 P_INSERT_T 過程失效，但是 P_INSERT_T 在調(diào)用時會嘗試重新編譯，而由于 T 的結(jié)構(gòu)保持不變，所以編譯報錯，因此 P_INSERT_T 的調(diào)用就報錯。那么為什么兩個過程放到一起執(zhí)行就會報錯且嘗試重新編譯也不起作用呢。這是由于導(dǎo)致 P_INSERT_T 失效的過程就在調(diào)用 P_INSERT_T 過程的塊中。在將塊提交給 Oracle

56、時，Oracle 對里面每個過程的狀態(tài)進(jìn)行了檢查，由于導(dǎo)致 P_INSERT_T 失效的 P_RECREATE 過程還沒有執(zhí)行，因此這時所有過程的狀態(tài)都是正常的。于是 Oracle了過程的狀態(tài)信息并開始調(diào)用過程。在調(diào)用 P_RECREATE 過程后，由于 T 表被刪除重建，P_INSERT_T 的狀態(tài)發(fā)生變化，但此時 Oracle 對過程 P_INSERT_T 的檢查已經(jīng)完成，因此在嘗試直接運行P_INSERT_T 的代碼時發(fā)現(xiàn) P_INSERT_T 的狀態(tài)已經(jīng)發(fā)生變化，所以這里報錯 ORA-4068。同樣的道理，即使對 P_INSERT_T 進(jìn)行了重新編譯，Oracle 在執(zhí)行時發(fā)現(xiàn)檢查時的代碼已經(jīng)發(fā)生了變化，當(dāng)前的 P_INSERT_T 和調(diào)用時的 P_INSERT_T 已經(jīng)發(fā)生了變化，因此仍然會報錯，即使這時經(jīng)是正常的。過程的狀態(tài)已而采用動態(tài) SQL報錯的就更容易理解了。采用動態(tài) SQL 語句，Oracle 將編譯時進(jìn)行的操作推遲到運行時進(jìn)行。也就