• Oracle: check history of executed queries


    Oracle: check history of executed queries

     
    The view v$sql contains almost of queries which are executed in your Oracle DB. Basically you have privileges to query this view, you can check all from it. Below are some useful queries for you to do on this view.

    1. Get latest query
    select sql_text from v$sql where first_load_time=(select max(first_load_time) from v$sql)

    2. Sort executed queries by load time
    select sql_text, first_load_time from v$sql order by first_load_time desc
     
    3. Get executed queries in a schema which have special text and sort by load time
    select * from v$sql
    where parsing_schema_name like 'YOUR_SCHEMA' and sql_text like '%YOUR_TEXT%'
    order by first_load_time desc
     
    4. Get 100 last executed queries
    select sql_fulltext from
    (select * from v$sql where parsing_schema_name like 'VHA' order by first_load_time desc)
    where rownum < 101
     
    5. Get 100 executed UPDATE or DELETE queries in a specific time period and sort by load time
    select sql_text,sql_fulltext, first_load_time, parsing_schema_name from
    (
      select * from v$sql
      where parsing_schema_name like 'YOUR_SCHEMA'
        and (sql_text like '%UPDATE %' or sql_text like '%INSERT %')
        and to_timestamp(first_load_time, 'YYYY-MM-DD/HH24:MI:SS') > to_timestamp('2012-09-27/14:06:00', 'YYYY-MM-DD/HH24:MI:SS')
      order by first_load_time desc
    )
    where rownum < 101

    You can create your own queries to find out what queries you need to check. Remember this view v$sql doesn't store prepared statements.

     
  • 相关阅读:
    复习 层叠样式表
    asp后台练习总结
    序列化
    WindowsForm 增 删 查 改
    WindowsForm 计算器
    UML建模和开发预演
    rabbitmq 使用心得
    设计模式之策略模式
    单一职责原则
    设计模式之工厂模式
  • 原文地址:https://www.cnblogs.com/yaoyangding/p/15183861.html
Copyright © 2020-2023  润新知