Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421

    Unanswered: (Old) SQL Statements

    I know there is a (DBA?) table that holds all the SQLs executed on the database (somewhere / somehow)

    I have tried googling, but cannot find it... Does anyone know what I am talking about?

    Greets

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As far as I know, there is no built-in SQL commands history. Some utilities (TOAD, for example, *maybe* iSQL*Plus (I can't check that one at the moment)) have such a functionality, but I don't know how it is implemented (whether using genuine Oracle system tables or some other way).

    There is, however, something that might (or, on the other hand, might not) interest you, and it is called the Oracle Flashback Technology.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Well you cant get a complete history but you can get the most recent sql from v$sqlarea (and for larger queries get it from v$sqltext). You wont however get the values of any bind variables.

    The only way to get a complete history would be to use auditing.

    Alan

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    dml statements are archived and can be found in the archive logs using logminer ...

    HTH
    Gregg

  5. #5
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Thanks all for looking

    logminer? Is that a tool of somesort? or oracle native?

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    It an Oracle tool.

    under sys schema ...
    @/oracle/OraHome1/rdbms/admin/dbmslogmnrd.sql
    @/oracle/OraHome1/rdbms/admin/dbmslogmnr.sql

    build the logminer dictionary, add logfiles, look for data ...

    ----- Build the LogMiner dictionary -----
    EXECUTE sys.DBMS_LOGMNR_D.BUILD( -
    DICTIONARY_FILENAME =>'1LogMiner_Dictionary.ora', -
    DICTIONARY_LOCATION => 'D:\oracle\admin\MI2\udump');

    ----- Specify logs you want to analyze ----

    EXECUTE sys.DBMS_LOGMNR.ADD_LOGFILE( -
    LOGFILENAME => 'D:\Oradata\Cre1\Archives\CRE1T001S01098.ARC', -
    OPTIONS => sys.dbms_logmnr.NEW);

    -- Add other logs --

    EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
    LOGFILENAME => 'D:\Oradata\Cre1\Archives\CRE1T001S01099.ARC', -
    OPTIONS => dbms_logmnr.ADDFILE);

    --------- Start LogMiner ------------

    EXECUTE SYS.DBMS_LOGMNR.START_LOGMNR( -
    DICTFILENAME => 'D:\oracle\admin\CRE1\udump\1LogMiner_Dictionary.o ra', -
    STARTTIME => to_date('25-MAY-2004 10:00:00', 'DD-MON-YYYY HH24:MIS'), -
    ENDTIME => to_date('25-MAY-2004 11:15:00', 'DD-MON-YYYY HH24:MIS'));

    ---------- issue your queries ------------
    examples ....

    -- For a user --
    SELECT sql_redo, sql_undo FROM v$logmnr_contents
    WHERE USERNAME = 'whoever' ;

    --- Occurances for a table ---
    SELECT to_char(timestamp,'HH24:MIS'),USERNAME,OPERATION,STATUS, sql_redo
    FROM v$logmnr_contents
    WHERE SEG_NAME = 'table_name';

    -- Table hits --
    SELECT seg_owner, seg_name, count(*) AS Hits FROM
    v$logmnr_contents WHERE seg_name NOT LIKE '%$' GROUP BY
    seg_owner, seg_name;

    Lookup the documentation on logmining and have fun ... Again, this is just
    the DML statements ... If you are using Oracle9i or above and are using
    the UNDO tablespace, then you also have the "flashback query" options

    HTH
    Gregg

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Brrrr... I see i have a whole lot to learn...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •