[20210113]給PB開發人員的一個建議.txt
[20210113]給PB開發人員的一個建議.txt
--//PB 表示PowerBuild開發工具,目前至少許多企業依舊在使用它.
--//許多開發在拼接sql語句時使用\r來連線字串,這在dba診斷問題時出現一些奇怪的現象,透過遇到的例子來說明.
--//想檢視那個會話執行某個語句發現.輸出怪異:
1.環境:
SYS> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
2.測試:
SYS> column CURSOR_TYPE format a20
SYS> select * from v$open_cursor where sql_id='fagcu20tqqc7x';
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ------------------------------ ---------------- ---------- ------------- ---------------------- ------------------- ----------- --------------------
OPEN 00000000AB7718C0 862662909 fagcu20tqqc7x SELECT MS_CF01.CFHM,
OPEN 00000000AB7718C0 862662909 fagcu20tqqc7x SELECT MS_CF01.CFHM,
OPEN 00000000AB7718C0 862662909 fagcu20tqqc7x SELECT MS_CF01.CFHM,
--//奇怪看不見SID,而且OPEN跑到user_NAME來了.看見sql_text才明白開發使用\r換行.
--//整個開發團隊的管理存在問題,實際上N久以前就建議開發不要這樣使用.
SYS> select * from v$open_cursor where sql_id='fagcu20tqqc7x'
2 @ prxx
==============================
SADDR : 00000000BC329A58
SID : 448
USER_NAME : XXXYYY_HIS
ADDRESS : 00000000AB7718C0
HASH_VALUE : 862662909
SQL_ID : fagcu20tqqc7x
MS_CF01.CFSB, : SELECT MS_CF01.CFHM,
LAST_SQL_ACTIVE_TIME :
SQL_EXEC_ID :
CURSOR_TYPE : OPEN
==============================
SADDR : 00000000BC80C9F8
SID : 856
USER_NAME : XXXYYY_HIS
ADDRESS : 00000000AB7718C0
HASH_VALUE : 862662909
SQL_ID : fagcu20tqqc7x
MS_CF01.CFSB, : SELECT MS_CF01.CFHM,
LAST_SQL_ACTIVE_TIME :
SQL_EXEC_ID :
CURSOR_TYPE : OPEN
==============================
SADDR : 00000000BDDB7FE0
SID : 19
USER_NAME : XXXYYY_HIS
ADDRESS : 00000000AB7718C0
HASH_VALUE : 862662909
SQL_ID : fagcu20tqqc7x
MS_CF01.CFSB, : SELECT MS_CF01.CFHM,
LAST_SQL_ACTIVE_TIME :
SQL_EXEC_ID :
CURSOR_TYPE : OPEN
PL/SQL procedure successfully completed.
SYS> column SQL_TEXT noprint
SYS> select * from v$open_cursor where sql_id='fagcu20tqqc7x';
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ------------------------------ ---------------- ---------- ------------- ------------------- ----------- --------------------
00000000BC329A58 448 XXXYYY_HIS 00000000AB7718C0 862662909 fagcu20tqqc7x OPEN
00000000BC80C9F8 856 XXXYYY_HIS 00000000AB7718C0 862662909 fagcu20tqqc7x OPEN
00000000BDDB7FE0 19 XXXYYY_HIS 00000000AB7718C0 862662909 fagcu20tqqc7x OPEN
--//這樣顯示就正常了.看看是什麼字元作怪.
SYS> select dump(sql_text,16) c100,to_char(replace(sql_fulltext,chr(13),'')) c100 from v$sqlarea where sql_id='fagcu20tqqc7x';
C100 C100
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
Typ=1 Len=660: 20,20,53,45,4c,45,43,54,20,4d,53,5f,43,46,30,31,2e,43,46,48,4d,2c,20,20,20,d,20,20,20 SELECT MS_CF01.CFHM,
,20,20,20,20,20,20,20,4d,53,5f,43,46,30,31,2e,43,46,53,42,2c,20,20,20,d,20,20,20,20,20,20,20,20,20,2 MS_CF01.CFSB,
0,4d,53,5f,43,46,30,31,2e,42,52,58,4d,2c,d,20,20,20,20,20,20,20,20,20,20,4d,53,5f,43,46,30,31,2e,46, MS_CF01.BRXM,
50,48,4d,20,20,d,20,20,20,20,20,46,52,4f,4d,20,4d,53,5f,43,46,30,31,20,20,d,20,20,20,20,57,48,45,52, MS_CF01.FPHM
45,20,28,20,4d,53,5f,43,46,30,31,2e,46,59,42,5a,20,3d,20,3a,61,69,5f,66,79,62,7a,20,29,20,41,4e,44,2 FROM MS_CF01
0,20,d,20,20,20,20,20,20,20,20,20,20,28,20,4d,53,5f,43,46,30,31,2e,59,46,53,42,20,3d,20,3a,61,69,5f, WHERE ( MS_CF01.FYBZ = :ai_fybz ) AND
79,66,73,62,20,20,6f,72,20,4d,53,5f,43,46,30,31,2e,59,46,53,42,20,3d,20,3a,61,69,5f,67,6c,79,66,73,6 ( MS_CF01.YFSB = :ai_yfsb or MS_CF01.YFSB = :ai_glyfsb) AND
2,29,20,41,4e,44,20,20,d,20,20,20,20,20,20,20,20,20,20,28,20,4d,53,5f,43,46,30,31,2e,5a,46,50,42,20, ( MS_CF01.ZFPB = :"SYS_B_0" ) AND
3d,20,3a,22,53,59,53,5f,42,5f,30,22,20,29,20,41,4e,44,20,20,d,20,20,20,20,20,20,20,20,20,20,28,20,4d ( MS_CF01.KFRQ >= :ai_cfxq ) AND
,53,5f,43,46,30,31,2e,4b,46,52,51,20,3e,3d,20,3a,61,69,5f,63,66,78,71,20,29,20,41,4e,44,d,20,20,20,2 MS_CF01.CFLX in (:ai_xycf,:ai_zycf,:ai_cycf) AND
0,20,20,20,20,20,20,4d,53,5f,43,46,30,31,2e,43,46,4c,58,20,69,6e,20,28,3a,61,69,5f,78,79,63,66,2c,3a (MS_CF01.XTCFBZ < :"SYS_B_1" OR MS_CF01.XTCFBZ IS NULL) AND
,61,69,5f,7a,79,63,66,2c,3a,61,69,5f,63,79,63,66,29,20,20,41,4e,44,d,20,20,20,20,28,4d,53,5f,43,46,3 ((MS_CF01.BRID = :al_brid AND :ai_skbz = :"SYS_B_2") OR
0,31,2e,58,54,43,46,42,5a,20,3c,20,3a,22,53,59,53,5f,42,5f,31,22,20,4f,52,20,4d,53,5f,43,46,30,31,2e ((MS_CF01.FYCK = :"SYS_B_3" OR MS_CF01.FYCK = :ai_ckbh) AND
,58,54,43,46,42,5a,20,49,53,20,4e,55,4c,4c,29,20,20,41,4e,44,d,20,20,20,20,20,20,20,20,20,20,28,28,4 ( MS_CF01.FPHM is not null AND :ai_skbz = :"SYS_B_4" )))
d,53,5f,43,46,30,31,2e,42,52,49,44,20,3d,20,3a,61,6c,5f,62,72,69,64,20,41,4e,44,20,3a,61,69,5f,73,6b
,62,7a,20,3d,20,3a,22,53,59,53,5f,42,5f,32,22,29,20,4f,52,d,20,20,20,20,20,20,20,20,20,20,28,28,4d,5
3,5f,43,46,30,31,2e,46,59,43,4b,20,3d,20,3a,22,53,59,53,5f,42,5f,33,22,20,4f,52,20,4d,53,5f,43,46,30
,31,2e,46,59,43,4b,20,3d,20,3a,61,69,5f,63,6b,62,68,29,20,41,4e,44,20,d,20,20,20,20,20,20,20,20,20,2
0,20,28,20,20,4d,53,5f,43,46,30,31,2e,46,50,48,4d,20,69,73,20,6e,6f,74,20,6e,75,6c,6c,20,41,4e,44,20
,3a,61,69,5f,73,6b,62,7a,20,3d,20,3a,22,53,59,53,5f,42,5f,34,22,20,29,29,29,d,20
20,20,53,45,4c,45,43,54,20,4d,53,5f,43,46,30,31,2e,43,46,48,4d,2c,20,20,20,d,20,20,20
S E L E C T M S _ C F 0 1 . C F H M , ~~
--//注意看下劃線0xd編碼(對應的就chr(13)),沒有0xa.難道開發連\r僅僅表示回車不懂嗎?
--//如果開發使用\n或者\r\n作為回車換行,這個問題根本不存在.\r僅僅表示回車並沒有換行.
--//另外我個人認為許多開發連基本的計算機常識都沒有,可悲可嘆.講了N多次,1次全部替換有這麼難嗎?
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2749747/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 給開發人員的幾點建議：如何理解你的DBATHBAT
- 給技術人員一些技術以外的建議
- 給PHP開發者的建議PHP
- 一個牛人給Java初學者的建議Java
- 對初學ERP人員的建議
- 給2019前端的5個建議前端
- 一個SAP開發人員的雙截棍之路
- 一個SAP開發人員的養蠶流水帳
- 給國內獨立遊戲開發者的一些建議遊戲開發
- 給 Web 開發人員的以太坊入坑指南Web
- 給Web開發人員的以太坊入坑指南Web
- 一個PHP開發者總結的九條建議PHP
- 一個SAP開發人員的2019年終總結
- 一個SAP開發人員的2018年終總結
- 給Java開發初學者的10個學習建議，助你學習事半功倍！Java
- 給Java開發初學者的10個學習建議，助你學習事半功倍Java
- 給好學進取的開發者的一些建議(自學故事)
- LeetCode刷題的一點個人建議和心得LeetCode
- 2010.03.16專題：一個開發人員的專案煩惱
- [需求建議]前臺個人中心外掛開發需求建議。
- 從業遊戲 22 年給獨立開發者的建議遊戲
- 軟體測試人員必備的60個測試工具清單，建議收藏一波！
- 給迷茫的Java員一些中肯建議，你還在虛度光陰嗎？Java
- 什麼是Web開發？如何成為一個Python Web開發人員？WebPython
- 日常開發中，提升技術的13個建議
- 給服務端小白的一些建議服務端
- PHP開發人員使用工具（個人愛好）PHP
- （地區成都）開工降薪 20%，大專測試人員 求建議。。
- Python開發人員常犯的幾個重大錯誤Python
- 給年輕人的有關AIGC的資訊、思考和建議AIGC
- golang開發:go併發的建議Golang
- 以我失敗的職業經歷：給初入職場的技術人員幾個小建議
- 不建議企業自行開發CRM的幾個理由
- 一個SAP開發人員的年終總結：程式設計與游泳程式設計
- 個人實踐的基於 Laravel 的論壇，歡迎大家給予建議和評測Laravel
- Edmund McMillen（E胖）給獨立遊戲開發者的24條建議遊戲開發
- phpMyAdmin給非技術人員一個查閱資料庫的視窗PHP資料庫
- 為什麼 Web 開發人員需要學習一個 JavaScript 框架？WebJavaScript框架