Or, “Oops, I didn’t mean to do that.”
We’ve all had to deal with an accidental deletion or even a dropped table. Sometimes you leave off a where clause, sometimes you drop a table and find out that the change request listed the wrong table. Oracle uses a feature called Flashback to give database professionals the power to view data at different moments in time and take action based on what they find.
Using Flashback on a Dropped Table
No matter how rigorous your practices are, mistakes happen. How do you deal with accidentally dropping a table?
In the SQL Server world, we’d reach for our most recent backup, restore a copy of the database, and hope that the outage didn’t take too long. With Oracle, we can look in the recycle bin.
First we create a table:
|
1 2 3 4 5 6 |
CREATE TABLE test ( id INT, things VARCHAR2(50) ); INSERT INTO test VALUES ( 1, 'ONE' ); INSERT INTO test VALUES ( 2, 'TWO' ); COMMIT; SELECT * FROM test ; |
|
1 2 3 4 |
ID THINGS ---------- -------------------------------------------------- 1 ONE 2 TWO |
And then we drop the table:
|
1 2 3 4 |
DROP TABLE test; COMMIT; SELECT * FROM test; |
|
1 2 3 4 |
SELECT * FROM test * ERROR at line 1: ORA-00942: table or view does not exist |
Oh no, I meant to drop test_two! Now what?
Thankfully, I can take a look in the recycle bin using the command SHOW RECYCLEBIN:
|
1 2 3 4 |
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- BIG_TABLE BIN$AZEU5yqWPZXgUwpdqMBL1Q==$0 TABLE 2014-08-26:15:51:31 TEST BIN$CyLJjKhZPPDgUwpdqMAvJw==$0 TABLE 2014-12-26:09:23:46 |
Okay, now I can see that back in August I dropped a table named BIG_TABLE and our table named TEST is still hanging around. Let’s restore the TEST table:
|
1 |
FLASHBACK TABLE test TO BEFORE DROP RENAME TO test_dropped ; |
Crisis averted! I’ve been able to restore the previously deleted table with a new name. I could leave out the RENAME TO portion and restore the table back with its original name, but it’s always possible that someone else has restored the table. Instead, it’s a probably a better practice to safely restore the table with a different name and then change the name with an ALTER TABLE:
|
1 |
ALTER TABLE test_dropped RENAME TO test; |
Fixing Row Level Mistakes with Flashback
Let’s keep using our test table. Here’s what the data looks like now:
|
1 2 3 4 5 6 7 8 |
ID THINGS ---------- -------------------------------------------------- 1 ONE 2 TWO 3 THREE 4 FOUR 5 FIVE 6 FIVE |
That data isn’t right. I need to know when the right data was present so I can get the right row in place. In order to do that, I can use the VERSIONS clause to tell Oracle to bring back specific versions of rows present for this particular table:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, test.* FROM test VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2014-12-26 09:30:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2014-12-26 09:49:00', 'YYYY-MM-DD HH24:MI:SS') ; |
Now we can see a history of the row with an id of 6!
- The row was inserted at 9:38AM.
- At 9:41, someone corrected the
thingscolumn to readSIX. - At 9:48, someone update the
thingscolumn back toFIVE– which we know is wrong.
How do we get the correct row back?
If we’d configured the database to track supplemental log data, we could use the view flashback_transaction_query and grab the UNDO_SQL column to rollback that one command. Since I have not configured tracking supplemental log data, we’ll have to be a little bit trickier.
Thankfully, we can use some UPDATE shenanigans to get exactly the rows that we want:
|
1 2 3 4 5 6 7 8 |
UPDATE test t1 SET things = (SELECT t2.things FROM test AS OF TIMESTAMP TO_TIMESTAMP('2014-12-26 09:42:00', 'YYYY-MM-DD HH24:MI:SS') t2 WHERE t1.id = t2.id ) WHERE t1.id = 6 ; SELECT * FROM test; |
Results:
|
1 2 3 4 5 6 7 8 |
ID THINGS ---------- -------------------------------------------------- 1 ONE 2 TWO 3 THREE 4 FOUR 5 FIVE 6 SIX |
Holy cow, we got our row back! Make sure you COMMIT the transaction (remember – Oracle won’t auto-commit like SQL Server).
Looking Into the Past
If you hadn’t guessed, it’s even possible to view the contents of a table as of a moment in time using the AS OF TIMESTAMP clause:
|
1 2 |
SELECT * FROM test AS OF TIMESTAMP TO_TIMESTAMP('2014-12-26 09:40:00', 'YYYY-MM-DD HH24:MI:SS'); |
Results:
|
1 2 3 4 5 6 7 8 |
ID THINGS ---------- -------------------------------------------------- 1 ONE 2 TWO 3 THREE 4 FOUR 5 FIVE 6 FIVE |
This functionality makes it possible for analysts to undo changes to the database, review changes for auditing purposes, or even to recalculate historical reports based on newer formulas.
The Tip of the Iceberg
Undeleting tables and reviewing previous rows is only a small portion of what you can accomplish with Oracle Flashback. Entire transactions can be flashed back, databases can be rolled back to a point in time, or the Data Archive feature can be used to house all changed versions of rows for all time. But even without diving into more advanced features, Oracle Flashback gives database professionals the powerful ability to correct for “oops” deletions in the application.
3 Comments. Leave new
Cool post. Sounds like the SQL Server team should take a look over the fence from time to time.
A DBA I used to work with implemented something similar some time ago on SQL 2005/2008. Whether it still works I don’t know, but if you’re interested…
http://www.sqlservercentral.com/articles/DDL+Triggers/66552/
To clarify for readers, this only applies to the “Recycle Bin” features, not the ability to undelete individual rows. I’m always wary of triggers, especially when they could cause DDL to fail.
Oracle’s Flashback makes use of the undo tablespace, so it’s already using something built into the database engine.