[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
- 給技術人員一些技術以外的建議
- 設計師如何與開發人員溝通的6個建議
- 推薦給JavaScript開發人員的10個工具JavaScript
- 給移動應用開發新手的6個建議
- 給iOS開發者的Android開發建議iOSAndroid
- 一個牛人給Java初學者的建議Java
- 推薦給開發人員的6個實用命令列工具命令列
- 一個SAP開發人員的養蠶流水帳
- 一個SAP開發人員的雙截棍之路
- 如何做一個讓開發人員看得起的測試人員
- 給開發人員和設計師的18個手機應用
- 對初學ERP人員的建議
- 給Web開發人員的以太坊入坑指南Web
- 給 Web 開發人員的以太坊入坑指南Web
- 給 Web 開發人員推薦的文件生成工具Web
- 給獨立開發商的5條PR建議
- 給開發維護大型專案開發者的建議
- 每個開發人員都需要了解的一個SQL技巧SQL
- 給Java開發初學者的10個學習建議，助你學習事半功倍！Java
- 給Java開發初學者的10個學習建議，助你學習事半功倍Java
- 推薦給開發人員的實用命令列工具命令列
- 給TouchPad開發人員的忠告：轉移平臺
- 給予Java初學者的建議（JavaWeb/後臺開發）JavaWeb
- 給移動遊戲開發商的6條建議遊戲開發
- 為JavaScript開發人員準備的 21 個小技巧(一)JavaScript
- 給JAVA設計開發新手的一些建議和意見(4)Java
- 給JAVA設計開發新手的一些建議和意見(3)Java
- 給JAVA設計開發新手的一些建議和意見(2)Java
- 給JAVA設計開發新手的一些建議和意見(1)Java
- 10個加速Table Views開發的建議View
- 一個月內從零開始做webOS開發人員Web
- 一個 .NET 上的短期Web開發專案招聘兼職開發人員Web
- Android開發人員必備的10個開發工具Android
- 給初學java的一點建議Java
- 開發人員 vs 測試人員
- 寫給Git初學者的7個建議Git
- Web開發人員常犯的10個錯誤Web