传统数据仓库日志系统详解
一、目的
当我们编写存储过程/自定义函数等SQL代码比较多的时候,添加日志是非常必要的,有助于我们进行Debug。
二、实现逻辑
日志记录,需要三部分组成:日志表(表分区/表空间/索引空间)、记录日志条数的序列、存储过程实现日志逻辑
1. 日志表的创建
create table t_sys_log(
task_id number not null, --使用序列记录主键值
task_name varchar2(50),
table_name varchar2(50),
start_time timestamp,
end_time timestamp,
task_status number,
task_log varchar2(2000),
task_pos varchar2(1000),
task_tmp varchar2(100) --备用字段
)
nologging --减少日志记录
partition by range(start_time) interval (numtoyminterval(1,'month'))
(
partition part_mm_197001 values less then(to_timestamp('19700101','yyyymmdd'))
)tablespace tbs_sys_log;--创建分区表,tbs_sys_log为表分区名
create index idx_sys_log on t_sys_log(task_id) loacl tablespace tbs_sys_log_idx;--创建索引,tbs_sys_log_idx索引分区
2. 序列的创建
create sequence seq_sys_log
minvalue 1
maxvalue 10000000000000000
start with 1
increment by 1
cache 20;
3. 存储过程逻辑简述
1. 记录日志的SQL,我们大致可以分为两种:执行成功、执行失败。(使用字段:task_status 记录程序执行情况)。
2. 使用if函数来区分这两种状态,同时以全量或增量的形式将程序的当前状态写入到t_sys_log这张表中
3. 报错记录,借助Oracle内置函数:sqlcode和sqlerrm来实现
三、具体实现代码
create or replace procedure prc_sys_log
(
oi_task_id in out integer,
iv_task_name in varchar2, --存储过程/自定义函数名
iv_table_name in varchar2, --同步数据的目标表名
ii_task_status in integer,
iv_task_log in varchar2,
iv_task_pos in varchar2
)
is
begin
if(ii_task_status = 1) then
select seq_sys_log.nextval into oi_task_id from dual; --自增序列,给日志唯一id
insert into t_o_sys_log(
task_id, --日志记录数,创建序列记录
task_name, --存储过程/自定义函数名
table_name, --同步数据涉及目标表名
start_time, --开始时间
end_time, --结束时间
task_status, --标注位,可以定义有值程序正常执行,为null程序抛出异常...
task_log, --程序执行情况
task_pos, --详细报错信息
)
values(
oi_task_id,
iv_task_name,
iv_table_name,
sysdate,
null,
1,
'program processing',
null
);
elsif ii_task_status = 0 then
update t_sys_log
set end_time = sysdate,
task_status = 2,
task_log = 'program completed'
where task_log <> 'performance debug' and task_id = oi_task_id; --用于程序中,可以将程序进行分段定位
else
if oi_task_id is null then
select seq_sys_log.nextval into oi_task_id from dual;
insert into t_sys_log(
task_id,
task_name,
table_name,
start_time,
end_time,
task_status,
task_log,
task_pos
)
values(
oi_task_id,
iv_task_name,
null,
sysdate,
null,
ii_task_status,
substr(iv_task_log,1,5000),
substr(iv_task_pos,1,1000)
);
else
update t_sys_log
set end_time = sysdate,
task_status = ii_task_status,
task_log = substr(iv_task_log,1,2000),
task_pos = substr(iv_task_pos,1,200)
where task_log <> 'performance debug' and task_id = oi_task_id;
end if;
end if;
commit;
exception --异常处理
when others then
rollback;
end;
end;
四、调用方式
create or replace procedure/function Helloworld(
vv_person_name varchar2,
)
is
/*
进行参数定义
*/
vi_task_id integer;
vv_task_name varchar2(50); --记录任务名
vv_err_code number; --记录异常编码
vv_err_log varchar2(500); --记录异常内容
-----------------------
begin
vv_task_name := 'Helloworld';
/*
记录开始日志
*/
prc_sys_log(
vi_task_id,
vv_task_name,
null,
1,
null,
null
);
----------------------------------------
--程序主题部分
-------------------------------
/*
记录结束日志
*/
prc_sys_log(
vi_task_id,
vv_task_name,
null,
0,
null,
null
);
--异常处理部分
/*
当程序出现异常时,我们需要将异常信息写入到日志表中
*/
exception
when others then
vv_task_name := 'Helloworld';
vi_err_code := sqlcode;
vv_err_log := substr(sqlerrm,1,200)
rollback;
prc_sys_log(
vi_task_id,
vv_task_name,
null,
null,
vi_err_code,
vv_err_log
);
end;
五、报错日志记录与查看
--当我们执行程序的时候出现错误,难以定位时日志的存在能够帮我们快速解决问题
select * from t_sys_log order by start_time desc;
--执行上面的语句即可快速查看我们刚刚执行的程序的问题所在了,快速定位快速解决......