Date: Mon, 1 Nov 1999 11:12:14 -0600 (CST)
To: rob fordone 
Subject: When was the table dropped ??

> Ari,
> 
> Hope you are well.
> 
> A few quick questions for you.
> 
> How can I determine when a table was dropped ???
> 
> Also is it possible to determine the log sequence number that I would need 
> to recover to for the dropped table ?
> 
> Thanks for your time
> Rob Ford
> 

Rob,

Normally, it is impossible to tell when a table was dropped, and
impossible to determine the log sequence number.

What you can do is setup auditing on the table. Oracle can generate either
a record into an audit table OR generate a flat file with information on
the time, username, etc of who dropped the table.

The syntax is:

AUDIT DROP TABLE ON owner.table_name
BY {SESSION/ACCESS}
WHENEVER {NOT} SUCCESSFUL;

So an example is:

AUDIT DROP TABLE ON scott.emp
BY SESSION
WHENEVER SUCCESSFUL;

Also, "AUDIT ANY" is a required privilege to issue audit commands. You can
do various "SELECT * FROM SYS.AUD$" to view the audit information, which
will tell you who and when the "scott.emp" table was dropped.

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 335+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://www.arikaplan.com                          <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page ikaplan.com"> Back to Ari Kaplan's Home Page