oracle 存储过程记录执行日志_oracle存储过程执行日志_faith.huan的博客

link之家
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
相关文章推荐
爽快的石榴 · 3条非常实用的处世“潜规则”，受益终生-CS ...· 3 月前 ·
慷慨大方的显示器 · 与失业魔王类似的漫画 - 抖音· 1 年前 ·
兴奋的牛肉面 · 记录的地平线 ...· 1 年前 ·
完美的苹果 · 銆愭潕閿﹁�铏鹃叡銆慱鏉庨敠璁拌櫨閰卞搧鐗� ...· 1 年前 ·
气势凌人的花卷 · 市疾控中心解答注射狂犬疫苗知识· 1 年前 ·
本文主要介绍如何记录存储过程执行详细日志,统计存储过程执行时间,实现逻辑如下:
创建一个序列,用于标识[存储过程,函数,触发器]执行先后顺序
create sequence SEQ_PROGRAM_EXECUTE_LOG
minvalue 1
maxvalue 1000000000000
start with 1
increment by 1
cache 100
cycle
order;
创建日志表,用于存储[存储过程,函数,触发器]执行日志 
CREATE TABLE PROGRAM_EXECUTE_LOG
  PROGRAM_NAME      VARCHAR2(1000),
  IS_SUCCEED        CHAR(1),
  LOG_DATE          DATE,
  EXECUTE_MSG       CLOB,
  TIME_CONSUMING    NUMBER,
  EXECUTE_ORDER     NUMBER,
  EXECUTE_BEGINTIME TIMESTAMP(6),
  EXECUTE_ENDTIME   TIMESTAMP(6),
  PROCESS_FLAG      VARCHAR2(10) DEFAULT 'N'
-- Add comments to the table 
comment on table PROGRAM_EXECUTE_LOG
  is '过程、函数、触发器执行性能日志';
-- Add comments to the columns 
comment on column PROGRAM_EXECUTE_LOG.program_name
  is '程序名称';
comment on column PROGRAM_EXECUTE_LOG.is_succeed
  is '执行结果(成功Y,失败N)';
comment on column PROGRAM_EXECUTE_LOG.log_date
  is '日志时间';
comment on column PROGRAM_EXECUTE_LOG.execute_msg
  is '执行详细信息';
comment on column PROGRAM_EXECUTE_LOG.time_consuming
  is '耗时';
comment on column PROGRAM_EXECUTE_LOG.execute_order
  is '流水号-SEQ_PROGRAM_EXECUTE_LOG';
comment on column PROGRAM_EXECUTE_LOG.execute_begintime
  is '执行开始时间';
comment on column PROGRAM_EXECUTE_LOG.execute_endtime
  is '执行结束时间';
comment on column PROGRAM_EXECUTE_LOG.process_flag
  is '处理标识(已处理Y,未处理N)';
CREATE INDEX PROGRAM_EXECUTE_LOG_IDX1 ON PROGRAM_EXECUTE_LOG (EXECUTE_ORDER);
CREATE INDEX PROGRAM_EXECUTE_LOG_IDX2 ON PROGRAM_EXECUTE_LOG (TIME_CONSUMING);
创建计算耗时的函数 
CREATE OR REPLACE FUNCTION F_TIMESTAMP_DIFF(ENDTIME   IN TIMESTAMP,
                                            STARTTIME IN TIMESTAMP)
  RETURN INTEGER AS
  STR      VARCHAR2(50);
  MISECOND INTEGER;
  SECONDS  INTEGER;
  MINUTES  INTEGER;
  HOURS    INTEGER;
  DAYS     INTEGER;
BEGIN
  STR      := TO_CHAR(ENDTIME - STARTTIME);
  MISECOND := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 10, 3));
  SECONDS  := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 7, 2));
  MINUTES  := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 4, 2));
  HOURS    := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 1, 2));
  DAYS     := TO_NUMBER(SUBSTR(STR, 1, INSTR(STR, ' ')));
  RETURN DAYS * 24 * 60 * 60 * 1000 + HOURS * 60 * 60 * 1000 + MINUTES * 60 * 1000 + SECONDS * 1000 + MISECOND;
END;
创建日志记录过程 
CREATE OR REPLACE PROCEDURE SYS_SAVELOG(PROGRAM_NAME VARCHAR2, --过程名称
                                         IS_SUCCEED   VARCHAR2, --是否执行成功 Y|N
                                         EXECUTE_MSG  CLOB, --过程执行信息
                                         BEGIN_TIME   TIMESTAMP --过程开始执行TIMESTAMP
                                         ) AS
  V_PROGRAM_NAME VARCHAR2(1000) := SUBSTRB(PROGRAM_NAME, 1, 1000); --过程名称
  V_IS_SUCCEED   CHAR(1) := SUBSTR(IS_SUCCEED, 1, 1); --过程执行成功表示 Y N
  V_BEGIN_TIME   TIMESTAMP := BEGIN_TIME;
  PRAGMA AUTONOMOUS_TRANSACTION; --日志开启自治事务，不影响业务逻辑事务
  调用方式：
  1.正常日志：PROC_SAVELOG(V_PROC_NAME, 'Y', '执行成功。。。',V_BEGIN_TIME);
  2.异常日志：PROC_SAVELOG(V_PROC_NAME, 'N', SQLERRM,V_BEGIN_TIME);
BEGIN
  INSERT INTO PROGRAM_EXECUTE_LOG
    (PROGRAM_NAME,
     IS_SUCCEED,
     LOG_DATE,
     EXECUTE_MSG, --已改为CLOB
     TIME_CONSUMING,
     --EXECUTE_ORDER,
     EXECUTE_BEGINTIME,
     EXECUTE_ENDTIME)
  VALUES
    (V_PROGRAM_NAME,
     V_IS_SUCCEED,
     SYSDATE,
     EXECUTE_MSG,
     F_TIMESTAMP_DIFF(SYSTIMESTAMP, V_BEGIN_TIME),
     --PROGRAM_EXECUTE_LOG_SEQ.NEXTVAL,
     V_BEGIN_TIME,
     SYSTIMESTAMP);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END;
异常日志处理存储过程 
CREATE OR REPLACE PROCEDURE SYS_PROCESS_ERRMSG(PROC_NAME  VARCHAR2, --过程信息
                                                EXE_MSG    CLOB, --执行信息
                                                ERR_MSG    VARCHAR2, --错误信息
                                                BEGIN_TIME TIMESTAMP, --过程开始执行TIMESTAMP
                                                RESULT     OUT VARCHAR2) AS
  /*faith@2016.11.24用于处理异常信息,方便后续扩展
  1.执行信息中添加ERR_MSG
  2.执行记录日志过程
  3.错误信息赋值给返回结果RESULT
  调用方式：
  SYS_PROCESS_ERRMSG(V_PROC_NAME, V_EXE_MSG,V_ERR_MSG,V_BEGIN_TIME,RESULT);
  V_EXE_MSG CLOB := EXE_MSG;
BEGIN
  RESULT := 'OK';
  --如果错误信息长度为0,返回OK
  IF LENGTH(ERR_MSG) = 0 OR ERR_MSG IS NULL THEN
    RETURN;
  END IF;
  DBMS_LOB.APPEND(V_EXE_MSG, ERR_MSG || CHR(10));
  SYS_SAVELOG(PROC_NAME, 'I', V_EXE_MSG, BEGIN_TIME);
  ROLLBACK;
  RESULT := ERR_MSG;
EXCEPTION
  WHEN OTHERS THEN
    RESULT := '处理异常信息发生错误!';
END;
  PROCEDURE SYS_SAVELOG_EXAMPLE(PLAN_DATE IN DATE,
                                CONFIRM   IN VARCHAR2,
                                RESULT    OUT VARCHAR2) AS
    V_PLAN_DATE  DATE := PLAN_DATE;
    V_CONFIRM    VARCHAR2(10) := CONFIRM; --CONFIRM标识, Y:报工确认  N：报工解锁
    V_ERR_MSG    VARCHAR2(1000);
    V_BEGIN_TIME TIMESTAMP := SYSTIMESTAMP;
    V_PROC_NAME  VARCHAR2(1000) := 'SYS_SAVELOG_EXAMPLE/' ||
                                   TO_CHAR(V_PLAN_DATE,
                                           'YYYY-MM-DD HH24:MI:SS') || '/' ||
                                   V_CONFIRM;
    V_EXE_MSG    CLOB;
  BEGIN
    DBMS_LOB.CREATETEMPORARY(V_EXE_MSG, TRUE); --初始化CLOB
    RESULT := 'OK';
    IF V_CONFIRM = 'Y' THEN
      V_ERR_MSG := '不允许确认今天或者今天之后的数据!'; --今天或者
      GOTO PROCESS_ERRMSG;
    END IF;
    COMMIT;
    DBMS_LOB.APPEND(V_EXE_MSG, '执行成功!');
    SYS_SAVELOG(V_PROC_NAME, 'Y', V_EXE_MSG, V_BEGIN_TIME);
    --异常处理
    RETURN; --若为顺序执行到此位置，则直接返回
    <<PROCESS_ERRMSG>>
    SYS_PROCESS_ERRMSG(V_PROC_NAME,
                       V_EXE_MSG,
                       V_ERR_MSG,
                       V_BEGIN_TIME,
                       RESULT);
  EXCEPTION
    WHEN OTHERS THEN
      V_ERR_MSG := SUBSTR(SQLERRM, 1, 160);
      DBMS_LOB.APPEND(V_EXE_MSG, V_ERR_MSG);
      SYS_SAVELOG(V_PROC_NAME, 'N', V_EXE_MSG, V_BEGIN_TIME);
      RESULT := V_ERR_MSG; --错误号对应的信息
  END;
PLSQL 中文乱码解决 
select userenv('language') from dual; 
-- 配置环境变量
-- NLS_LANG=sql查询出来的值
                    创建一个序列,用于标识[存储过程,函数,触发器]执行先后顺序create sequence SEQ_PROGRAM_EXECUTE_LOGminvalue 1maxvalue 1000000000000start with 1increment by 1cache 100cycleorder;创建日志表,用于存储[存储过程,函数,触发器]执行日志CREATE TABL...
				本包用于记录存储过程里面的日志，将日志信息写入日志表(mylog)。可以记录日志时间、过程名称、过程里面记录日志的行数、参数值。如果在异常处理部分记录日志，还可以记录异常代码、异常原因、异常产生的行数。
本人使用开发环境为oracle10g
--------------------------------------------------------------------------------------------------------------建一个日志表
create table T_ERROR_LOG
 error_log_id    VARCHAR2(32) default sys_guid() not null,
 owner        VARCHAR2(30),
				项目中存储过程运行时记录出错日志方法。
步骤一：创建一个序列,用于标识[存储过程,函数,触发器]执行先后顺序
CREATE SEQUENCE SEQ_PROGRAM_EXECUTE_LOG
MINVALUE 1
MAXVALUE 1000000000000
START WITH 1
INCREMENT BY 1
CACHE 100
CYCLE
ORDER;
-- 删除序列
--DROP SEQU...
				Oracle数据库里有一个专门存储操作的视图 v$sqlarea，可以通过它查询历史操作。
select t.SQL_TEXT, t.FIRST_LOAD_TIME
from v$sqlarea t
where t.SQL_TEXT like 'delete%' and t.FIRST_LOAD_TIME like '2013-03-30%'
order by t...
				[Q]怎么样查询特殊字符，如通配符%与_  [Q]如何插入单引号到数据库表中 [Q]怎样设置事务一致性  [Q]怎么样利用光标更新数据  [Q]怎样自定义异常 [Q]十进制与十六进制的转换  [Q]能不能介绍SYS_CONTEXT的详细用法  [Q]怎么获得今天是星期几，还关于其它日期函数用法  [Q]随机抽取前N条记录的问题 [Q]抽取从N行到M行的记录，如从20行到30行的记录  [Q]怎么样抽取重复记录  [Q]怎么样设置自治事务 [Q]怎么样在过程中暂停指定时间 [Q]怎么样快速计算事务的时间与日志量  [Q]怎样创建临时表 [Q]怎么样在PL/SQL中执行DDL语句  [Q]怎么样获
				多年前写的复制目标数据库指定表的数据到本地表。
程序丢了可惜，分享下。
执行存储过程，通过指定目标数据库登录信息，动态生成dblink，打开游标，按批次插入本地指定表中，每批次1万条数据。同步记录和日志信息写在自动生成的sync_log表中。同步完成后自动删除动态生成的存储过程、dblink。
本程序不同步blob和clob字段，玩oracle的人都懂，用sql同步此类数据，速度上不去，此类数据应该要用导出导入的方式提升速度。
具体请参阅程序，可根据自已需要进行修改。
参数说明：
CREATE OR REPLACE PROCEDURE syncTable(
      ip IN VARCHAR2,--目标数据库ip地址
      port IN NUMBER,--目标数据库端口号
      sid varchar2,--目标数据库SID
      ora_user VARCHAR2,--目标数据库登录用户名
      passwd VARCHAR2,--目标数据库登录密码
      src_table_name IN VARCHAR2,--目标数据需要同步的表（数据源）
      dest_table_name IN VARCHAR2,--把目标表同步到本地库的表名，无则创建与目标表一致的表名
      errMsg OUT VARCHAR2)--输出执行信息。
程序同步到执行结束时的目标表所有数据，不按任何字段排序，只判断并记录row_number()到日志表，中断后重新执行可继续同步，如果目标表频繁读写，不保证数据完全正常，故最好是目标表不变动情况下执行同步。
有问题请私信或留言。
				2、登陆数据库，执行如下语句，查看当前进行io操作的进程和模块名称：
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,
se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes
FROM v$session se,v$session_wait st,v$sess_io si,v$process pr
WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR and se.username is not null AND se.sid>6
AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC
				要在Oracle中实现定时执行存储过程，你可以使用Oracle的定时任务功能。
首先，你需要创建一个定时任务。你可以通过查询`dba_jobs`视图来找到之前创建定时任务的`what`的值，即创建定时任务的`what`值。例如，你可以运行以下SQL语句查询：
select job from dba_jobs where what = 'package_name.proce_name();';
然后，你可以手动启动定时任务的执行。通过运行以下代码来实现：
begin
  dbms_job.run(job); -- 这里的job应该是上面查询到的job值
  commit;
这样，定时任务就会立即执行。
关于异常日志记录，你可以使用一个表来记录异常信息。你可以创建一个表，包含以下列：`log_time`（日志记录时间），`job_type`（存储过程类别），`data_type`（数据类型，可选），`data_time`（数据日期），`error_code`（异常码），`error_msg`（异常信息）。
如果你想了解更多关于Oracle数据库相关的知识，例如安装流程、卸载流程、Oracle操作流程步骤和查询技巧，你可以参考相关的学习资料。这些资源可以帮助你深入学习Oracle数据库的使用和操作。
参考链接：
 Oracle定时任务的创建和执行，https://blog.csdn.net/kennyder/article/details/12652221
 Oracle异常日志记录，https://www.cnblogs.com/jerryjiaozhou/p/4500984.html
 Oracle数据库相关知识学习资料，https://download.csdn.net/download/caryxp/88215429<span class="em">1</span><span class="em">2</span><span class="em">3</span>
#### 引用[.reference_title]
-  *1* *2* [Oracle定时任务执行存贮过程](https://blog.csdn.net/weixin_42569679/article/details/125998195)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v92^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"]
-  *3* [某培训机构Java基础培训及Oracle简单运用笔记.doc](https://download.csdn.net/download/caryxp/88215429)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v92^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"]
[ .reference_list ]
                    lvgaga: 
                    我到这一步了 lsnrctl start 执行的时候 出现 TNS-12546: TNS:permission denied
 TNS-12560: TNS:protocol adapter error
  TNS-00516: Permission denied 
这几个错误 说是权限不足
root用户 我执行了 chmod -R 777 /var/tmp/.oracle  提示 chmod: 更改"/var/tmp/.oracle" 的权限: 只读文件系统
怎么破 求助 感谢
                CentOS7.6安装Oracle12cRelease2
                    lvgaga: 
                    我是不是应该手动先编辑 listener.ora 文件 然后在执行登录或者启动这些操作才对啊?