怎么查询一个用户执行过的SQL语句

wh62592855 2009-11-20 08:54:45
今天论坛上一个朋友问怎么查询之前执行过的SQL语句，我当时没多想就给回了个
select sql_text from v$sql where parsing_schema_name='USERNAME' order by last_load_time desc;
可是我试了试发现这个语句是有些问题的，好像并不能查询该用户执行的所有SQL语句。比如说我在试验的过程中，SELECT和INSERT可以查出来，CREATE TABLE和DROP TABLE都不行。
而且有时候通过其他一些视图查出来的结果中包含了一些不该有的操作，看样子都是些对系统内部表的查询与操作，而并不是用户真正输入的语句。

V$SQL,V$SQL_TEXT,V$SQLAREA,V$OPEN_CURSOR，这几个视图里面字段又超级多，真的无从下手，有时都不知道该查哪个视图了，可能由于自己对这几个视图的定义和区别理解的还不够深吧，呵呵。这里也希望大家不吝赐教。

仔细想了想，关于这个查询用户执行SQL语句的话题应该可以分出不止一个分支的。比如说：

①查询某个用户在某段时间内执行过的所有SQL语句
②查询当前状态(意味着该SESSION并未断开 正处于活动状态)下某个用户执行过的所有SQL语句
③……
暂时好像就想到了这两个情况而已，哪位朋友想出新的情况了欢迎补充，大家一起讨论。

专门写这么个帖子，希望可以借此帖把这方面的事情彻底的搞清楚，当然，这离不开大家的帮助。
各位踊跃发言哈！

我嘛还才疏学浅，暂时连上面那两种情况的查询语句都还写不出来。
如果哪位以前对这方面做过专门的研究，或有有好的文章推荐，也可以贴出来大家一起学习学习！

当然，我这里说的是用查询来实现哦，LOGMINER和审计咱就不提了哦。
...全文
1988 25 打赏 收藏 转发到动态 举报
写回复
用AI写文章
25 条回复
切换为时间正序
请发表友善的回复…
发表回复
myling_m428 2012-10-18
  • 打赏
  • 举报
 回复
学到好多东西呀
尤其是哪个实验
ruihuahan 2009-11-23
  • 打赏
  • 举报
 回复
①查询某个用户在某段时间内执行过的所有SQL语句
=============================================
这个要从日志中找吧。log miner 功能或许有用
liusong_china 2009-11-23
  • 打赏
  • 举报
 回复
做个标记
wh62592855 2009-11-21
  • 打赏
  • 举报
 回复
OK
我去看一下

呵呵 不过说到你在前面做的那个实验
select sql_id,sql_text into v_sql_id,v_sql
8 from v$sqlarea
9 where sql_id=(select sql_id from v$session
10 where audsid=userenv('sessionid'));

这个时候明明是从v$sqlarea读取出了sql_id
可接下来却从这个视图中查不出相关信息了

OPER@tl> select * from v$sqlarea where sql_id='9gkx5dbw15x43';

未选定行

难道只是在语句的执行过程中v$sqlarea里才存有相关记录?
执行完了就消去了
vc555 2009-11-21
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 wh62592855 的回复:]
呵呵 奇怪哦
在存储过程里都是从V$SQLAREA里读出来的SQL_ID和SQL_TEXT
可后来再次查询却是没有返回记录
[/Quote]
你看v$fixed_view_definition，看看这几个视图的定义。
它们是来源于不同的底层表的。
wh62592855 2009-11-21
  • 打赏
  • 举报
 回复
[Quote=引用 19 楼 vc555 的回复:]
你的1，2问题，我不是给你回了吗。

“全部执行的sql都可以在V$SQL,V$SQL_TEXT,V$SQLAREA三个视图中找到。
不过前提是该sql还在shared pool中存在。
”

用sql的方式就是这个，别无他法。除了logmnr和audit
[/Quote]呵呵 好的 我先研究下每个视图里的字段含义
做做实验试试
wh62592855 2009-11-21
  • 打赏
  • 举报
 回复
[Quote=引用 20 楼 vc555 的回复:]
339这个统计指标代表的是不是硬解析，要看你数据库版本。每个版本编号是不同的。
[/Quote]哦 怪不得我查出来的结果是bytes received via SQL*Net from dblink
vc555 2009-11-21
  • 打赏
  • 举报
 回复
339这个统计指标代表的是不是硬解析，要看你数据库版本。每个版本编号是不同的。
vc555 2009-11-21
  • 打赏
  • 举报
 回复
你的1，2问题，我不是给你回了吗。

“全部执行的sql都可以在V$SQL,V$SQL_TEXT,V$SQLAREA三个视图中找到。
不过前提是该sql还在shared pool中存在。
”

用sql的方式就是这个，别无他法。除了logmnr和audit
wh62592855 2009-11-21
  • 打赏
  • 举报
 回复
①查询某个用户在某段时间内执行过的所有SQL语句
②查询当前状态(意味着该SESSION并未断开 正处于活动状态)下某个用户执行过的所有SQL语句
③……

大家都踊跃发言哈
这几个问题还没人回答呢
补充也可以

是不是周末了都休息去了呀^_^
wh62592855 2009-11-21
  • 打赏
  • 举报
 回复
哦……即时老化
呵呵 这个词用的很生动呀

SQL> select name from v$statname where statistic#=339;



NAME

--------------------

bytes received via S

QL*Net from dblink


339这个统计指标代表的是硬解析?!
vc555 2009-11-21
  • 打赏
  • 举报
 回复
我做了下试验，ddl在shared_pool中是即时老化的。上面说了v$sqltext和v$sqlarea视图的底层表不同。
由结果可知，每次查询v$sqlarea，其底层表是在shared_pool中重新取数据，而v$sqltext则不是。

OPER@tl> select * from v$mystat where statistic#=339;



       SID STATISTIC#      VALUE

---------- ---------- ----------

       138        339          9



OPER@tl> create table test(aaa number);



表已创建。



OPER@tl> select * from v$mystat where statistic#=339;



       SID STATISTIC#      VALUE

---------- ---------- ----------

       138        339         10





--另一会话drop table test;



OPER@tl> create table test(aaa number);



表已创建。



OPER@tl> select * from v$mystat where statistic#=339;



       SID STATISTIC#      VALUE

---------- ---------- ----------

       138        339         11



OPER@tl


可以看到，ddl语句或者说这的create每执行一次，硬解析就增加一次，sql并未被重用。也就是这个create语句执行后即刻就从shared pool中清除掉了。
wh62592855 2009-11-20
  • 打赏
  • 举报
 回复
v$sqltext
存储的是完整的SQL,SQL被分割

SQL> desc v$sqltext
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS RAW(4) ---------
HASH_VALUE NUMBER --------- 和 address 一起唯一标志一条sql
COMMAND_TYPE NUMBER
PIECE NUMBER ---------- 分片之后的顺序编号
SQL_TEXT VARCHAR2(64) -------------- 注意长度



v$sqlarea --------- 存储的SQL 和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息
SQL> desc v$sqlarea
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
VERSION_COUNT NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(38)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(25)
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
KEPT_VERSIONS NUMBER
ADDRESS RAW(4)
HASH_VALUE NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER




v$sql ---------- 存储的是具体的SQL 和执行计划相关信息，实际上，v$sqlarea 可以看做 v$sql 根据 sqltext 等 做了 group by 之后的信息


SQL> desc v$sql
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(38)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
KEPT_VERSIONS NUMBER
ADDRESS RAW(4)
TYPE_CHK_HEAP RAW(4)
HASH_VALUE NUMBER
PLAN_HASH_VALUE NUMBER
CHILD_NUMBER NUMBER ---------- 注意这个
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID NUMBER -------------- 注意这里跟 outline 有关
CHILD_ADDRESS RAW(4)
SQLTYPE NUMBER
REMOTE VARCHAR2(1)
OBJECT_STATUS VARCHAR2(19)
LITERAL_HASH_VALUE NUMBER
LAST_LOAD_TIME VARCHAR2(38)
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER


另外注意这个
QL> desc v$sql_plan
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS RAW(4)
HASH_VALUE NUMBER
CHILD_NUMBER NUMBER ------------ 注意这个和 v$sql 里面的相同字段
OPERATION VARCHAR2(60)
OPTIONS VARCHAR2(60)
OBJECT_NODE VARCHAR2(20)
OBJECT# NUMBER
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(64)
OPTIMIZER VARCHAR2(40)
ID NUMBER
PARENT_ID NUMBER
DEPTH NUMBER
POSITION NUMBER
SEARCH_COLUMNS NUMBER
COST NUMBER
CARDINALITY NUMBER
BYTES NUMBER
OTHER_TAG VARCHAR2(70)
PARTITION_START VARCHAR2(10)
PARTITION_STOP VARCHAR2(10)
PARTITION_ID NUMBER
OTHER VARCHAR2(4000)
DISTRIBUTION VARCHAR2(40)
CPU_COST NUMBER
IO_COST NUMBER
TEMP_SPACE NUMBER
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)


实际上，看起来同样的一句SQL ，往往具有不同的执行计划
如果是不同的数据库用户，那么相应的涉及的 对象 可能都不一样，注意v$sql 中
OBJECT# NUMBER
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(64)
OPTIMIZER VARCHAR2(40)

即使是相同的数据库用户，若 session 的优化模式、session 级的参数 等不一样，执行计划也能不同。所以即使相同的sql，也可能具有不同的执行计划！

v$sql join to v$sql_plan 就代表了具体的sql的执行计划,通过下面3个字段做连接

ADDRESS RAW(4)
HASH_VALUE NUMBER
CHILD_NUMBER NUMBER


而v$SQLAREA 忽略了 执行计划 等差异，只是在形式上sql文本看起来一样！相当于做了个聚合，是多个不同执行计划的sql的聚合和累计信息
wh62592855 2009-11-20
  • 打赏
  • 举报
 回复
呵呵 奇怪哦
在存储过程里都是从V$SQLAREA里读出来的SQL_ID和SQL_TEXT
可后来再次查询却是没有返回记录
wh62592855 2009-11-20
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 vc555 的回复:]
你看v$sqltext
SQL codeOPER@tl>createorreplacetrigger tr_ddl2 aftercreateonschema3declare4 v_sql_idvarchar2(100);5 v_sqlvarchar2(100);6begin7select sql_id,sql_textinto v_sql_id,v_sql8from v$sqlarea9where sql_id=(select sql_idfrom v$session10where audsid=userenv('sessionid'));11 dbms_output.put_line('sql_id is:'||v_sql_id);12 dbms_output.put_line('sqltext is:'||v_sql);13end;14/

触发器已创建

OPER@tl>createtable test(aaanumber);
sql_idis:9gkx5dbw15x43
sqltextis:createtable test(aaanumber)

表已创建。

OPER@tl>select*from v$sqlareawhere sql_id='9gkx5dbw15x43';

未选定行

OPER@tl>select*from v$sqltextwhere sql_id='9gkx5dbw15x43';

ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE SQL_TEXT-------- ---------- -------------- ------------ ---------- --------------------------2483CCFC4161991811 9gkx5dbw15x4310createtable test(aaanumber)

OPER@tl>
[/Quote]
3Q3Q~
我刚也试了下 是可以查到
Adebayor 2009-11-20
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 vc555 的回复:]
你看v$sqltext
SQL codeOPER@tl>createorreplacetrigger tr_ddl2 aftercreateonschema3declare4 v_sql_idvarchar2(100);5 v_sqlvarchar2(100);6begin7select sql_id,sql_textinto v_sql_id,v_sql8from v$sqlarea9where sql_id=(select sql_idfrom v$session10where audsid=userenv('sessionid'));11 dbms_output.put_line('sql_id is:'||v_sql_id);12 dbms_output.put_line('sqltext is:'||v_sql);13end;14/

触发器已创建

OPER@tl>createtable test(aaanumber);
sql_idis:9gkx5dbw15x43
sqltextis:createtable test(aaanumber)

表已创建。

OPER@tl>select*from v$sqlareawhere sql_id='9gkx5dbw15x43';

未选定行

OPER@tl>select*from v$sqltextwhere sql_id='9gkx5dbw15x43';

ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE SQL_TEXT-------- ---------- -------------- ------------ ---------- --------------------------2483CCFC4161991811 9gkx5dbw15x4310createtable test(aaanumber)

OPER@tl>
[/Quote]
学习了
vc555 2009-11-20
  • 打赏
  • 举报
 回复
你看v$sqltext
OPER@tl> create or replace trigger tr_ddl

  2  after create on schema

  3  declare

  4  v_sql_id varchar2(100);

  5  v_sql varchar2(100);

  6  begin

  7  select sql_id,sql_text into v_sql_id,v_sql

  8  from v$sqlarea

  9  where sql_id=(select sql_id from v$session

 10  where audsid=userenv('sessionid'));

 11  dbms_output.put_line('sql_id is:'||v_sql_id);

 12  dbms_output.put_line('sqltext is:'||v_sql);

 13  end;

 14  /



触发器已创建



OPER@tl> create table test(aaa number);

sql_id is:9gkx5dbw15x43

sqltext is:create table test(aaa number)



表已创建。



OPER@tl> select * from v$sqlarea where sql_id='9gkx5dbw15x43';



未选定行



OPER@tl> select * from v$sqltext where sql_id='9gkx5dbw15x43';



ADDRESS  HASH_VALUE SQL_ID         COMMAND_TYPE   PIECE       SQL_TEXT

-------- ---------- -------------- ------------ ---------- --------------------------

2483CCFC 4161991811 9gkx5dbw15x43        1          0       create table test(aaa number)



OPER@tl>
wh62592855 2009-11-20
  • 打赏
  • 举报
 回复
不管是DDL还是DML都可以?

我今天从V$SQL中好像寄没查出DDL来
vc555 2009-11-20
  • 打赏
  • 举报
 回复
全部执行的sql都可以在V$SQL,V$SQL_TEXT,V$SQLAREA三个视图中找到。
不过前提是该sql还在shared pool中存在。
你做做实验就知道了呀。
Adebayor 2009-11-20
  • 打赏
  • 举报
 回复
SQL_TEXT显示的内容有问题
加载更多回复（5）
oracle查看执行过的语句,oracle 查询执行过的SQL语句
MySQL开启日志记录查询/执行过的SQL语句作为后端开发者,遇到数据库问题的时候应该通过分析SQL语句来跟进问题所在,该方法可以记录所有的查询/执行的SQL语句到日志文件. 方法有几种,但是个人觉得以下这种最简单,但是重启MySQL服务后需要重 ...oracle查询消耗服务器资源SQL语句1.查找最近一天内,最消耗CPU的SQL语句 SELECT ASH.INST_ID, AS...
oracle查看用户执行的sql,查询Oracle中正在执行和执行过的SQL语句
查询Oracle正在执行的sql语句及执行该语句的用户SELECT b.sid oracleID,b.username 登录Oracle用户名,b.serial#,spid 操作系统ID,paddr,sql_text 正在执行的SQL,b.machine 计算机名FROM v$process a, v$session b, v$sqlarea cWHERE a.addr = b.paddrAND ...
mysql8查看SQL执行语句历史记录
mysql数据库执行语句_mysql中记录SQL执行语句的方法_程序泉的博客-CSDN博客-- 打开sql执行记录功能set global log_output='TABLE'; -- 输出到表set global log=ON; -- 打开所有命令执行记录功能general_log, 所有语句: 成功和未成功的.set global log_slow_queries=ON; -- 打开慢查询sql记录slow_log, 执行成功的: 慢查询语句和未使...https://blog.csdn.net/wei
python执行mysql多个sql语句_mysql -- 一次执行多条sql语句
最近要做一个软件升级，其中涉及到数据库表字段的变动(新增或删除或修改)，所有的关于数据库的变动的sql语句都是存放在Sqlupdate.sql文件中，每次升级的时候都需要执行一次Sqlupdate.sql里的所有sql语句，这就涉及到执行多条语句的问题了。软件在CodeIgniter框架上开发的，CodeIgniter有封装好的执行sql语句的函数：$this->db->query('...
oracle数据库查询正在执行的sql,Oracle查询正在执行的SQL语句
查看 Oracle 正在执行的 sql 语句以及发起的用户SELECT b.sid oracleID,b.username 用户名,b.serial#,paddr,sql_text 正在执行的SQL,b.machine 计算机名称FROM v$process a, v$session b, v$sqlarea cWHERE a.addr = b.paddrAND b.sql_hash_value ...
基础和管理

17,377

社区成员

95,128

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章