ITPub博客

首页 > 数据库 > Oracle > [20210113]给PB开发人员的一个建议.txt

[20210113]给PB开发人员的一个建议.txt

原创 Oracle 作者:lfree 时间:2021-01-13 16:16:24 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2818
  • 访问量
    6618272