参考文献DBA_HIST_ACTIVE_SESS_HISTORY

dba_users

v$sql事件缘由

昨天接到用户请求，要查询XXXXX数据库上2019年3月17号1点到7点的DML操作数，一脸懵逼，不知道用户要这个数据干嘛，不知道查询哪个视图，不知道DML操作具体有哪些(当年的数据库知识只剩下DML叫做 Data Manipulation Language )，请教大佬，获知查询视图DBA_HIST_ACTIVE_SESS_HISTORY，随后自己搜索DML操作具体包含哪些操作，获知INSERT、UPDATE、DELETE，于是拼凑出以下查询语句：SELECT COUNT(*) AS C_DML

FROM DBA_HIST_ACTIVE_SESS_HISTORY

WHERE sample_time > To_date('2019-03-17 01:00', 'yyyy-mm-dd hh24:mi')

AND sample_time < To_date('2019-03-17 08:00', 'yyyy-mm-dd hh24:mi')

AND sql_opname IN ( 'INSERT', 'UPDATE', 'DELETE' );

查询结果显示共有一万多条，从视图DBA_HIST_ACTIVE_SESS_HISTORY的定义可以获知，对于运行时间小于一秒的SQL，这个视图可能获取不到，所以以上查询的结果也只是大概的DML语句数。

但是，用户真正想要的显然不是这个，于是进一步提出请求，问“可以查的出来哪些是人工执行的，哪些是应用系统执行的sql吗”，这逼我显然装不下去了，完全不懂如何区分人工执行和应用系统执行，只能如实回答不是很懂，问用户究竟想要什么信息，用户说出了自己的真实目的——“我其实是想查出上线当天哪些是我们变更人员自己执行的”，当我继续问用户是需要一个统计出来的数量还是具体的表格时，回复说“拿着应用程序主账号执行的，具体的表格”。拐弯抹角绕圈子向我要信息，我算是明白怎么回事，估摸着变更出问题了，想找出事故责任人。对于如何区分人工执行和应用系统执行还是一脸懵逼，继续向大佬求救，获知应从连接进数据库的应用程序，DBA_HIST_ACTIVE_SESS_HISTORY视图里的program字段入手，得以下查询语句：SELECT DISTINCT PROGRAM

FROM DBA_HIST_ACTIVE_SESS_HISTORY

WHERE sample_time > To_date('2019-03-17 01:00', 'yyyy-mm-dd hh24:mi')

AND sample_time < To_date('2019-03-17 08:00', 'yyyy-mm-dd hh24:mi')

AND sql_opname IN ( 'INSERT', 'UPDATE', 'DELETE' );

结果显示有一百七十多种连进数据库的进程，仔细分析后，排除掉系统连接进来进程(形如：数据库所在主机用户名@数据库所在主机名(进程名))、应用程序连接进来的进程(JDBC Thin Client)，只剩下sqlplus.exe、plsqldev.exe和另外两条形如主机用户名@非数据库所在主机名(TNS V1-V3)的进程，于是构造如下查询语句：SELECT *

FROM DBA_HIST_ACTIVE_SESS_HISTORY

WHERE sample_time > To_date('2019-03-17 01:00', 'yyyy-mm-dd hh24:mi')

AND sample_time < To_date('2019-03-17 08:00', 'yyyy-mm-dd hh24:mi')

AND sql_opname IN ( 'INSERT', 'UPDATE', 'DELETE' )

AND program IN ( 'plsqldev.exe', 'sqlplus.exe', 'xxx@xxxxx(TNS V1-V3)', 'yyy@yyyy(TNS V1-V3)' )

经过这一波筛选，一万多条语句被筛选成一百三十多条，且有意义的连接程序全是plsqldev.exe。

但是，这依旧没有满足用户的需求，因为这个视图里面没有具体的sql语句信息以及执行这条语句的用户名称，而且用户不需要的信息太多，于是关联dba_users和v$sql，构造如下语句：SELECT du.username,

dh.user_id,

dh.sample_time,

dh.sql_opname,

dh.machine,

dh.port,

dh.program,

dh.sql_id,

s.sql_text

FROM DBA_HIST_ACTIVE_SESS_HISTORY dh,

dba_users du,

gv$sql s

WHERE dh.sample_time > To_date('2019-03-17 01:00', 'yyyy-mm-dd hh24:mi')

AND dh.sample_time < To_date('2019-03-17 08:00', 'yyyy-mm-dd hh24:mi')

AND dh.sql_opname IN ( 'INSEERT', 'UPDATE', 'DELETE' )

AND dh.program = 'plsqldev.exe'

AND dh.user_id = du.user_id

AND dh.sql_id = s.sql_id(+);

至此，用户请求基本得到处理。