Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2001
    Location
    Akron
    Posts
    11

    Question Unanswered: Recovering Drop Views or Tables

    Question. How is one able to determine who and when a given
    view or table is dropped?

    Information: About the system
    Oracle 8.1.7
    Running Archive Log mode.
    AUDITING for DROP ANY VIEW not on.

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    if the database wasn't stopped you can retrieve for a drop view statement in v$sqlarea

    select first_load_time, sql_text from v$sqlarea where ....

    there are also stored some information about who it was and you can joint it with v$session table

  3. #3
    Join Date
    Apr 2003
    Location
    Bahrain
    Posts
    11
    hI,

    You can easily handle this situation by database triggers also

    Heber

  4. #4
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107
    Originally posted by heber
    hI,

    You can easily handle this situation by database triggers also

    Heber
    Hi,
    Hope this code helps

    DROP TRIGGER audit_ddl_changes
    /
    DROP TABLE dll_audit_log
    /

    CREATE TABLE dll_audit_log (
    stamp DATE,
    username VARCHAR2(30),
    osuser VARCHAR2(30),
    machine VARCHAR2(30),
    terminal VARCHAR2(30),
    operation VARCHAR2(30),
    objtype VARCHAR2(30),
    objname VARCHAR2(30))
    /

    CREATE OR REPLACE TRIGGER audit_ddl_changes
    AFTER create OR drop OR alter
    ON scott.SCHEMA -- Change SCOTT to your schema name!!!
    -- ON DATABASE
    BEGIN
    INSERT INTO dll_audit_log VALUES
    (SYSDATE,
    SYS_CONTEXT('USERENV', 'SESSION_USER'),
    SYS_CONTEXT('USERENV', 'OS_USER'),
    SYS_CONTEXT('USERENV', 'HOST'),
    SYS_CONTEXT('USERENV', 'TERMINAL'),
    ORA_SYSEVENT,
    ORA_DICT_OBJ_TYPE,
    ORA_DICT_OBJ_NAME
    );
    END;
    /
    show errors


    -- Now, let's test it
    CREATE TABLE my_test_table (col1 DATE)
    /
    DROP TABLE my_test_table
    /
    set pages 50000
    SELECT * FROM dll_audit_log
    /
    nn

  5. #5
    Join Date
    Apr 2003
    Location
    Bahrain
    Posts
    11
    Hi novice,

    I think it will work perfectly

    Heber

  6. #6
    Join Date
    Sep 2001
    Location
    Akron
    Posts
    11

    Hmm

    Thanks everyone,
    Is there a way to obtain this information in a archive log file without
    doing a recovery? Problem is that the View (table) was removed by
    someone(?? - I suspect tech support). I'm at a lost because of the number of log switches that have occured between the deletion and
    the time I noticed that it. I'll need to pull the information from backup.

Posting Permissions

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