oracle database to find recently added column in table or database(oracle)

Question

I want to find the recently added column to existing table. How to find recently added column in table or database(oracle). Recently the table and the databases of our web application got modified and some of the table got altered.

Answer 1

Ideally you should have restricted access to your Production database, together with a build process which applies scripts out of source control, rather than allowing people to change things using TOAD. It's pretty hard to conduct forensics in a free-fire zone.

You can find out which tables have changed by interrogating the data dictionary:

SQL> select object_name from user_objects t
  2  where t.object_type = 'TABLE'
  3  and t.last_ddl_time > trunc(sysdate)
  4  /

no rows selected

SQL> alter table t23 add col_3 number
  2  /

Table altered.

SQL> select object_name from user_objects t
  2  where t.object_type = 'TABLE'
  3  and t.last_ddl_time > trunc(sysdate)
  4  /
OBJECT_NAME
----------------------------------------------------------
T23

SQL>

This won't tell you what the change was, or who did it. To get better information you need a proper audit trail. At the very least you should enable auditing of DDL statements....

SQL> audit ALTER TABLE;

Audit succeeded.

SQL> 

Find out more.