Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Nitesh.

Asked: April 12, 2018 - 5:57 pm UTC

Last updated: April 13, 2018 - 3:42 am UTC

Version: 11 g

Viewed 1000+ times

You Asked

Hi Tom,

Thanks for wonderful support always.

I want to know is there any way to know who has deletes the entire column from the table.

There is no manually created trigger in my database for auditing on column delete.

One entire column has been deleted from my table , now i dont know who has deletes that.
And In what ways I can reterive the column.

and Connor said...

I haven't got good news for you.

Once that column is dropped, its gone. Not even the flashback query/table options will assist here.

SQL> create table t1 as select * from all_objects;

Table created.

SQL>
SQL> select to_char(sysdate,'HH24MISS') from dual;

TO_CHA
------
114041

SQL>
SQL> alter table t1 drop column owner;

Table altered.

SQL> alter table t1 enable row movement;

Table altered.

SQL> flashback table t1 to timestamp timestamp '2018-04-13 11:40:45';
flashback table t1 to timestamp timestamp '2018-04-13 11:40:45'
                *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


You'll need to go back to a backup or datapump copy of the table when it still had the column

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Nitesh Sharma, April 13, 2018 - 9:02 am UTC

Always got what is required.

To Nitesh

J. Laurindo Chiappa, April 13, 2018 - 1:42 pm UTC

Just to add, of course besides backup restoring, to recover from a catastrophic DDL we could use a FLASHBACK DATABASE, if you have the required flashback logs... If not, your only chance will be restore the last backup OR import some dump, yes...

And to find the culprit, if you have LOGMINER available AND supplemental log is active, you could mine the logs... But for sure, if no kind of Audit (be via triggers, native Audit, FGA, whatever) is present AND you do not have the logs and/or supplemental log is not active, imho you will not have any chance to find the culprit...

Regards,

Chiappa
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.