FLASHBACK TABLE vs. DBA_OBJECTS . LAST_DDL_TIME

A little over a week ago, a teammate and I were trying to use Oracle’s FLASHBACK TABLE to undo an “oops” UPDATE statement that a client’s developers had run on one of their test databases, clearing data from two columns in all rows of the table.

The statement was actually part of a script that also contained ALTER TABLE statements to add columns. This is important to note because FLASHBACK TABLE will only let you go back as far as the most recent DDL against that table. To quote the SQL reference:

“Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.”

The Mystery of the Flashback Window

This led me to another question: Is there a way to directly see to precisely what date and time you can flashback a table?

The developer couldn’t give me a precise time, only that the UPDATE statement was executed immediately after the structure-changing DDL, making my target window very small. Naturally, one would think that the LAST_DDL_TIME in the DBA_OBJECTS view would hit that nail on the head. However it turns out that the key bit of that SQL reference quote is “change the structure of the table.”

Why LAST_DDL_TIME Isn't the Smoking Gun

It turns out that there are a few statements that will update the LAST_DDL_TIME without changing the table structure. For example:

• GRANT and REVOKE: These provide a user with certain privileges on an object and will trigger an update to LAST_DDL_TIME. You can then go ahead and flashback the table prior to the privilege change.
• ENABLE ROW MOVEMENT: A prerequisite to FLASHBACK TABLE is to enable row movement via an ALTER TABLE statement. This also bumps LAST_DDL_TIME, but obviously doesn’t block FLASHBACK TABLE from going past it in time.

The bottom of all this is that you can’t use LAST_DDL_TIME to determine just how far back you can go with a FLASHBACK TABLE statement, as you can most likely go past it due to various non-structure-changing DDL statements that affect that timestamp.

A Practical Demonstration

Here’s a little demonstration to illustrate this point:

-- Get our preferred date format SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';  Session altered.  SQL> SQL> -- First we need something to play with SQL> create table emp   2  as select * from hr.employees;  Table created.  SQL> SQL> -- alter row movement to allow for flashback SQL> -- NOTE: this also updates last_ddl_time SQL> alter table emp enable row movement;  Table altered.  SQL> SQL> -- Note the last_ddl_time given in the SQL> -- dba_objects view SQL> select last_ddl_time   2  from dba_objects   3  where owner=user and object_name='EMP';  LAST_DDL_TIME ------------------- 2008/05/28 22:09:11                                                               SQL> SQL> -- Let some time elapse, get a drink of water SQL> exec dbms_lock.sleep(120);  PL/SQL procedure successfully completed.  SQL> SQL> -- Get a marker, note this. SQL> select sysdate from dual;  SYSDATE ------------------- 2008/05/28 22:11:11                                                               SQL> SQL> -- Note the salary before the DML SQL> select first_name, last_name, salary   2  from emp   3  where employee_id=101;  FIRST_NAME           LAST_NAME                     SALARY -------------------- ------------------------- ---------- Neena                Kochhar                        17000                         SQL> SQL> -- Let's give her a 15% raise SQL> update emp   2  set salary = salary * 1.15   3  where employee_id=101;  1 row updated.  SQL> SQL> -- Don't forget the commit! SQL> commit;  Commit complete.  SQL> SQL> -- Observe the new salary SQL> select first_name, last_name, salary   2  from emp   3  where employee_id=101;  FIRST_NAME           LAST_NAME                     SALARY -------------------- ------------------------- ---------- Neena                Kochhar                        19550                         SQL> SQL> -- Get another marker SQL> select sysdate from dual;  SYSDATE ------------------- 2008/05/28 22:11:11                                                               SQL> SQL> -- Now let's run bump last_ddl_time with a grant SQL> grant select on emp to hr;  Grant succeeded.  SQL> SQL> -- Check it out SQL> select last_ddl_time   2  from dba_objects   3  where owner=user and object_name='EMP';  LAST_DDL_TIME ------------------- 2008/05/28 22:11:11                                                               SQL> -- Now flashback to the first marker or SQL> -- anytime after creation but before update SQL> flashback table emp to timestamp   2  to_timestamp('2008/05/28 22:10:00','YYYY/MM/DD HH24:MI:SS');  Flashback complete.  SQL> SQL> -- Verify the flashback worked SQL> select first_name, last_name, salary   2  from emp   3  where employee_id=101;  FIRST_NAME           LAST_NAME                     SALARY -------------------- ------------------------- ---------- Neena                Kochhar                        17000                         SQL> SQL> -- Clean up SQL> drop table emp;  Table dropped. 

Finding the "Point of No Return"

So, I’m still looking for a straight-forward way to identify a point-of-no-return for flashback for future reference. When I asked around the shop, the consensus seemed to be that using LogMiner would be the best way to not only find the SCN of the table structure changes, but to get the SQL to “undo” the table change (if I so desired), and even to undo the effects of the UPDATE statement.

Apologies for the long block of code. And thanks to Marc, Riyaj, Alex F., and Alex G. for their help.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?