欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle自动保存所有执行过的sql语句  

程序员文章站 2022-06-04 16:04:18
...

前言

  项目上出现了一个奇怪错误,导致用户无法正常操作系统。反映到数据上,就是一个表的字段(数字类型)平白无故的变成了null,我们查找了整个系统所有的涉及到的增删改查代码,以及存储等,均未能发现原因,最终决定查看数据库中执行的所有SQL,逐条检查。

实施方案1

  首先是用v$sql视图查询,但是这个结果集里的数据不能保证长时间的存在,而且无法随数据库迁移到新的服务器上。

select t.SQL_TEXT--varchar类型的sql(超过1000字符的sql语句会显示不下)
      ,t.SQL_FULLTEXT--clob类型的sql(完整的sql语句)
      ,to_char(t.LAST_ACTIVE_TIME,'yyyy-mm-dd hh24:mi:ss')--sql的执行时间
from v$sql t
where t.PARSING_SCHEMA_NAME='SCOTT'--大写用户名
  and trunc(t.LAST_ACTIVE_TIME)=trunc(sysdate)--今天执行的sql
--按时间倒叙输出
order by 3 desc;
    p.s.相关知识点(自己查): 

     v$sql、v$sqlarea和v$sqltext的区别

     视图里的数据能保留多久

实施方案2

   定时将数据库执行过的sql保存到单独的一个表里(当然这个表要定期清理)。考虑到这个表的数据量较大,所以单独开一个表空间:

create tablespace sqlarea--表空间名字叫sqlarea
datafile '/opt/oracle/oradata/sqlarea.dbf'--数据库文件路径
size 128m--文件初始大小
autoextend on--设置文件自增长
next 64m--每次增加64MB
maxsize 20480m--最大不超过20GB
extent management local;
 
--不限制用户对表空间的操作
grant create session,create table,unlimited tablespace to SCOTT;
   表空间准备好之后,就可以建表了
create table sqltextlog(
       sql_text varchar2(1000),--文本类型的sql
       sql_fulltext clob,--clob类型的sql
       action varchar2(64),--执行sql的来源
       last_active_time date--执行时间
)
--指定所使用的表空间,不写的话会跑到默认表空间,刚刚我们新建的表空间就白费了
tablespace sqlarea;
  现在就可以往表中插入数据了,为了能够让数据库自动向表中插入数据,我们把插入过程封装成存储过程,存储的名称就叫PRO_SQLTextLog
create or replace procedure PRO_SQLTextLog is--保存sql到指定的表
time_ date;--时间变量,表示表中最后一次插入数据的时间
begin
  --给时间变量赋值,空表的时候,默认用当天的零点
  select nvl(max(last_active_time),trunc(sysdate))
  into time_
  from sqltextlog;
  --开始插入,插入的数据范围从上次插入时间到现在
  insert into sqltextlog(sql_text,sql_fulltext,action,last_active_time)
  select sql_text,sql_fulltext,action,last_active_time
  from v$sql t
  where t.PARSING_SCHEMA_NAME='SCOTT'
    and t.LAST_ACTIVE_TIME>time_
    and t.LAST_ACTIVE_TIME<sysdate;
  --别忘了提交
  commit;
end PRO_SQLTextLog; 
  这里需要注意的一点,就是v$sql的访问权限,需要执行下面的SQL来解决
 grant select any dictionary to SCOTT;
  现在有了存储过程,下一步就是让Oracle每个10分钟执行一遍这个存储。
variable jobid number;--jobid要用作输出
begin
  --第1个参数,用作结果的输出
  --第2个参数,是要执行的存储,注意是字符串类型,而且最后有个英文的分号
  --第3个参数,Job开始的时间,要是想明天0点开始,就改成trunc(sysdate)+1
  --第4个参数,是下次运行的时间,相当于是时间间隔,注意是字符串类型的
  sys.dbms_job.submit(:jobid,'PRO_SQLTextLog;',sysdate,'sysdate+10/1440');
  commit;
end;
/
  上面的sql要在sqlplus下执行,或者是PL/SQL Developer的Command Window里。注意最后不是英文分号结束,而是一个斜杠。
Oracle自动保存所有执行过的sql语句
            
    
    
         
  至此,Oracle自动将SQL保存到指定的数据库表的功能完成,所有执行过的SQL语句都会出现在sqltextlog里
select * from sqltextlog l order by l.last_active_time desc;
  job的执行情况可以通过执行下面的sql查看
select job
      ,last_date
      ,next_date
      ,failures
      ,broken
from user_jobs;
   p.s.相关知识点:

     建表空间

     建表时指定表空间

     建存储过程

     JOB定时作业
  • Oracle自动保存所有执行过的sql语句
            
    
    
         
  • 大小: 22.3 KB

上一篇: 屏蔽回退按键  

下一篇: 备忘录