oracle varchar数据分析

需求

     存储日志监控无法监控存储运行情况

     原因：监控日志和存储名对应，需要修改存储日志

   实现原理：

     存储定时任务-在存储中插入存储日志表，对该存储进行运行监控。

  OUT_ERROR_ID  := 0;
  OUT_ERROR_MSG := '成功';
INSERT INTO [存储监控表]
  VALUES
    (SEQ_AUTO_TASK_MONITOR.NEXTVAL,
     '[存储过程名]',
     SYSDATE,
     'IN',
     '1',
     '运行成功',
     '{存储结果表名}',
     ''||[结果表数据量]||'');
  COMMIT;
EXCEPTION  --异常捕获
  WHEN OTHERS THEN
    ROLLBACK;
    OUT_ERROR_ID  := -1;
    OUT_ERROR_MSG := OUT_ERROR_MSG || SQLCODE || ':' || SQLERRM;
    LC_ERROR_CODE := SQLCODE;
    LC_ERROR_MSG  := 'err' || '->>' || LC_ERROR_CODE || '->>' ||
                     SUBSTR(SQLERRM, 1, 200);
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(LC_ERROR_CODE) || ':' || LC_ERROR_MSG);
        INSERT [存储监控表]
    VALUES
      (SEQ_AUTO_TASK_MONITOR.NEXTVAL,
       '[存储过程名]',
       SYSDATE,
       'DD',
       '2',
       LC_ERROR_MSG,--异常描述
       '{存储结果表名}',
     ''||[结果表数据量]||'');

解决思路：

 

实现：

1.找到全部需要监控的存储并且放入临时表中

----找到全部需要监控的存储并且放入临时表中
CREATE table tmp_1_315 as 
SELECT/*+parallel(8)*/ DISTINCT(a.name) FROM USER_SOURCE a WHERE a.type='PROCEDURE' and a.name like '%BSS30%'

2.找到正常监控的存储放入临时表2中

  2.1目标格式

  2.2查找正常存储

 SELECT/*+parallel(8)*/ ''''||'test'||''',' 格式化测试 FROM  dual

查询：

无结果集返回

原因：考虑存在空格将空格替换

replace(upper(a.TEXT),' ',null)

依然没有结果返回将格式化后的数据拿出来比对一下

 SELECT /*+parallel(8)*/
   replace(a.TEXT,' ',null)
  FROM USER_SOURCE a
 WHERE a.TYPE = 'PROCEDURE'
 and   a.name = 'P_GFX_RPT_JH_JBHB_DAY_BSS30'
 and a.line=521 
 UNION all
  SELECT /*+parallel(8)*/
   ''''||a.name||''','
  FROM USER_SOURCE a
 WHERE a.TYPE = 'PROCEDURE'
 and   a.name = 'P_GFX_RPT_JH_JBHB_DAY_BSS30'
 and a.line=521

执行结果：

看着相同，使用length函数看看字符串长度

 
 SELECT /*+parallel(8)*/
   length(replace(a.TEXT,' ',null))
  FROM USER_SOURCE a
 WHERE a.TYPE = 'PROCEDURE'
 and   a.name = 'P_GFX_RPT_JH_JBHB_DAY_BSS30'
 and a.line=521 
 UNION all
  SELECT /*+parallel(8)*/
  length( ''''||a.name||''',')
  FROM USER_SOURCE a
 WHERE a.TYPE = 'PROCEDURE'
 and   a.name = 'P_GFX_RPT_JH_JBHB_DAY_BSS30'
 and a.line=521

执行结果

字符串长度不相同 考虑USER_SOURCE中TEXT含有换行符，使用replace替换

ascii换行符：chr(10)

length(replace(replace(a.TEXT,' ',null),chr(10),null))

替换后比对

长度一致

重新查找正常存储

 SELECT/*+parallel(8)*/ 
 DISTINCT(a.name)
 FROM 
 USER_SOURCE a
 WHERE a.TYPE = 'PROCEDURE'
 and  a.name like '%BSS30%'
 and  replace(replace(upper(a.TEXT),' ',null),chr(10),null)=''''||upper(a.name)||''',';

执行结果：

创建临时表二

create table  xxx as   .....

3比较两个临时表找出异常稽核监控

select name from tmp_1_315 WHERE name not in  (select * from tmp_2_315)

执行结果

对应存储名去修改存储内的监控日志。并且删除两张临时表。

 

 

总结

USER_SOURCE

SYS.SOURCE$

SELECT/*+parallel(8)*/ COLUMN_NAME,data_type FROM  all_tab_columns  WHERE table_name ='USER_SOURCE

执行结果：

  a.虽然说oracle不区分大小写，但在分析varchar类型数据时要十分关注大小写的问题，对于该类型数据需要大小写统一，使用函数upper和lower进行大小写统一（如果需求需要区分大小写，则跳过这一步）。

  b.注意格式化后字符串长度的变化，关注是否有特殊字符。 当长度不一致，切确定字符对应就需要考虑看不见的字符，使用ASCII()和 CHR() 函数结合替换字符类型函数处理varchar类数据。

ASCII不可以见字符

二进制	十进制	十六进制	缩写	名称/意义
0000 0000	0	00	NUL	空字符（Null）
0000 0001	1	01	SOH	标题开始
0000 0010	2	02	STX	本文开始
0000 0011	3	03	ETX	本文结束
0000 0100	4	04	EOT	传输结束
0000 0101	5	05	ENQ	请求
0000 0110	6	06	ACK	确认回应
0000 0111	7	07	BEL	响铃
0000 1000	8	08	BS	退格
0000 1001	9	09	HT	水平定位符号
0000 1010	10	0A	LF	换行键
0000 1011	11	0B	VT	垂直定位符号
0000 1100	12	0C	FF	换页键
0000 1101	13	0D	CR	归位键
0000 1110	14	0E	SO	取消变换（Shift out）
0000 1111	15	0F	SI	启用变换（Shift in）
0001 0000	16	10	DLE	跳出数据通讯
0001 0001	17	11	DC1	设备控制一（XON 启用软件速度控制）
0001 0010	18	12	DC2	设备控制二
0001 0011	19	13	DC3	设备控制三（XOFF 停用软件速度控制）
0001 0100	20	14	DC4	设备控制四
0001 0101	21	15	NAK	确认失败回应
0001 0110	22	16	SYN	同步用暂停
0001 0111	23	17	ETB	区块传输结束
0001 1000	24	18	CAN	取消
0001 1001	25	19	EM	连接介质中断
0001 1010	26	1A	SUB	替换
0001 1011	27	1B	ESC	跳出
0001 1100	28	1C	FS	文件分割符
0001 1101	29	1D	GS	组群分隔符
0001 1110	30	1E	RS	记录分隔符
0001 1111	31	1F	US	单元分隔符
0111 1111	127	7F	DEL	删除