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保存到指定的数据库表的功能完成,所有执行过的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语句的执行时间和SP2-0027错误
-
oracle导出sql语句的结果集和保存执行的sql语句(深入分析)
-
探讨:Oracle数据库查看一个进程是如何执行相关的实际SQL语句
-
查询Oracle中正在执行和执行过的SQL语句
-
oracle查看执行最慢与查询次数最多的sql语句
-
如何让docker中的mysql启动时自动执行sql语句
-
Oracle基础:通过sqlplus执行sql语句后的结果进行判断
-
Oracle基础多条sql执行在中间的语句出现错误时的控制方式
-
oracle中得到一条SQL语句的执行时间的两种方式
-
Oracle批量执行sql语句之禁用所有表的外键