die Seilerwerks

NOTE: This post originally appeared on The Pythian Group blog on 6 June 2008, and is reposted here with permission.

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

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

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 statements, which provide a user with certain privileges on an object, will trigger an update to LAST_DDL_TIME. You can then go ahead and flashback the table prior to the privilege change. Another item to note is that a prerequisite to FLASHBACK TABLE is to enable row movement on that table, via (you guessed it) an ALTER TABLE statement. The ALTER TABLE foo ENABLE ROW MOVEMENT statement 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.

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.

SQL>

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.