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

前言

　　项目上出现了一个奇怪错误，导致用户无法正常操作系统。反映到数据上，就是一个表的字段(数字类型)平白无故的变成了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保存到指定的数据库表的功能完成，所有执行过的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定时作业