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

Question

本帖最后由 wh62592855 于 2009-11-20 20:55:50 编辑

Answer 1

呵呵 谢谢哈SELECT好像也可以?
--SYS窗口
SQL> select sql_text from v$sql where parsing_schema_name='SCOTT'
  2  order by last_load_time desc;no rows selectedSQL> /SQL_TEXT
-------------------------------------------------------------------------------select * from deptSQL>
--SCOTT窗口
SQL> show user
USER is "SCOTT"
SQL> select * from dept;    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Answer 2

全部执行的sql都可以在V$SQL,V$SQL_TEXT,V$SQLAREA三个视图中找到。
不过前提是该sql还在shared pool中存在。
你做做实验就知道了呀。

Answer 3

不管是DDL还是DML都可以?我今天从V$SQL中好像寄没查出DDL来

Answer 4

你看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> 

Answer 5

呵呵 奇怪哦
在存储过程里都是从V$SQLAREA里读出来的SQL_ID和SQL_TEXT
可后来再次查询却是没有返回记录

Answer 6

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的聚合和累计信息

Answer 7

你看v$fixed_view_definition，看看这几个视图的定义。
它们是来源于不同的底层表的。

Answer 8

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里才存有相关记录?
执行完了就消去了

Answer 9

我做了下试验，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          9OPER@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         11OPER@tl可以看到，ddl语句或者说这的create每执行一次，硬解析就增加一次，sql并未被重用。也就是这个create语句执行后即刻就从shared pool中清除掉了。

Answer 10

哦……即时老化
呵呵 这个词用的很生动呀SQL> select name from v$statname where statistic#=339;NAME
--------------------
bytes received via S
QL*Net from dblink339这个统计指标代表的是硬解析?!

Answer 11

①查询某个用户在某段时间内执行过的所有SQL语句 
②查询当前状态(意味着该SESSION并未断开 正处于活动状态)下某个用户执行过的所有SQL语句 
③…… 大家都踊跃发言哈
这几个问题还没人回答呢 
补充也可以是不是周末了都休息去了呀^_^

Answer 12

你的1，2问题，我不是给你回了吗。“全部执行的sql都可以在V$SQL,V$SQL_TEXT,V$SQLAREA三个视图中找到。 
不过前提是该sql还在shared pool中存在。 
”用sql的方式就是这个，别无他法。除了logmnr和audit

Answer 13

哦 怪不得我查出来的结果是bytes received via SQL*Net from dblink

Answer 14

①查询某个用户在某段时间内执行过的所有SQL语句 
=============================================
这个要从日志中找吧。log miner 功能或许有用