who dropped the table?

[PSoni]

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

[bang_dba]

No way, unless otherwise u have enabled auditing.....

[stmontgo]

log miner 9i?

steve

[PSoni]

log miner 9i?

steve

we are using win2000 server , oracle 8.1.7

[jovery]

In that case as bang_dba said unless you have auditing enabled, you won't be able to find out.

[rohitsn]

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

[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.

[pando]

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

[adrianomp]

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.