DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: who dropped the table?

  1. #1
    Join Date
    Feb 2001
    Posts
    125

    who dropped the table?

    HI.

    some has dropped a table yesterday. we want to find out
    who is he i.e. osuser,user,terminal etc.

    we tred to findout in v$sqltext, v$sqlarea but there is no such statement.


    we are not using archive backup so can not user log minr


    P.soni
    Last edited by PSoni; 07-02-2003 at 12:56 AM.

  2. #2
    Join Date
    Sep 2002
    Posts
    376
    No way, unless otherwise u have enabled auditing.....

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    log miner 9i?

    steve
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    Feb 2001
    Posts
    125
    log miner 9i?

    steve

    we are using win2000 server , oracle 8.1.7

  5. #5
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    In that case as bang_dba said unless you have auditing enabled, you won't be able to find out.
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  6. #6
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530

    Re: who dropped the table?

    Hi,
    You can be able to find out the user who has done any DDL activity on the database only if you write the database level trigger.This is new feature introduced in Oracle 8i(8.1.5.0)onwards and it works very fine in Oracle 8.1.7.0 without any problem.You have to create a database level trigger which will fire if any user executes a DDL statement and it captures the entire information of that user i.e sid,serial#,osname,username,machine,terminal,object_name,object_owner,ipaddress and stores in a table for viewing.
    If you want to have that trigger do mail me,will send it to you.

    Regards,
    Rohit Nirkhe,Oracle/Apps DBA,3
    OCP 8i,OCP 9i
    oracle-support@indiatimes.com
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  7. #7
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96
    U can user logminor utility on archived online redo log files (if your database in Archive log mode), or else if in noarchivelog mode then u should have log files (not overwritten).

    Or else you can check which user connected from Network, with os-user with IP Address, and connection time in file
    $ORACLE_HOME/network/log/listener.log, U can check in who connected to which Oracle user, and the using which programm (e.g. SQLPLUS, jre if using OEM ). U can get idea who was connected, but can not justify what operations (DDL, DML) made on the database using this option.



    Dilip Patel
    OCP 8i

    Catch me online at Yahoo: ddpatel256

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by Dilippatel
    U can user logminor utility on archived online redo log files (if your database in Archive log mode), or else if in noarchivelog mode then u should have log files (not overwritten).

    Or else you can check which user connected from Network, with os-user with IP Address, and connection time in file
    $ORACLE_HOME/network/log/listener.log, U can check in who connected to which Oracle user, and the using which programm (e.g. SQLPLUS, jre if using OEM ). U can get idea who was connected, but can not justify what operations (DDL, DML) made on the database using this option.



    well as many suggested, logminer shows you DDL if you are using 9i which is not the case so forget about logminer

  9. #9
    Join Date
    Feb 2001
    Posts
    295
    well as many suggested, logminer shows you DDL if you are using 9i which is not the case so forget about logminer
    Before 9i, Logminer can track DDL as DML on the data dictionary. It could be done with an up-to-date dictionary file available and some extra analysis. However, I bet there is not one.
    An ounce of action is worth a ton of theory.
    —Friedrich Engels

Posting Permissions

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


Click Here to Expand Forum to Full Width