Flaschback Query on Dropped Table

Necessity of using Flashback Query on dropped tables is disputable, though I found it worthy to tell 🙂 In my opinion, Recycle bin and Flashback Query are most useful features of 10G’s Flashback Concept. Below you will see how to use them together. Please don’t ask me why to use them together, because I couldn’t conjure up an almost real case yet 🙂 Your cases will be welcome as a comment.

/*FLASHBACK QUERY FROM NORMAL TABLE*/ 16:46:25 coskan@DABAK> SELECT * FROM T1 AS OF TIMESTAMP TO_TIMESTAMP(‘2007-08-03 16:45:04’, ‘YYYY-MM-DD HH24:MI:SS’); OBJID CREATION_D LAST_CHANG———- ———- ———-41 03.08.200742 03.08.2007 43 03.08.2007 44 03.08.2007 16:46:32 coskan@DABAK> drop table t1; Table dropped. 16:46:40 coskan@DABAK> SELECT object_name as recycle_name, original_name, type FROM recyclebin; RECYCLE_NAME ORIGINAL_NAME TYPE —————————— ——————————– ————————- BIN$riHbdXwdTB+CF2F6uHK8gg==$0 T1 TABLE BIN$EyQUrilzQLiP9E34k+xlPA==$0 T1_INSERT TRIGGER BIN$5Ysb/qadTD+hJEv3Ro3z5g==$0 T1_UPDATE TRIGGER /*QUERY FROM DROPPED TABLE*/ 16:47:00 coskan@DABAK> SELECT * FROM “BIN$riHbdXwdTB+CF2F6uHK8gg==$0”; OBJID CREATION_D LAST_CHANG ———- ———- ———- 41 03.08.2007 54 03.08.2007 03.08.2007 43 03.08.2007 44 03.08.2007 45 03.08.2007 46 03.08.2007 6 rows selected. /*FLASHBACK QUERY FROM DROPPED TABLE*/ 16:47:30 coskan@DABAK> SELECT * FROM “BIN$riHbdXwdTB+CF2F6uHK8gg==$0” AS OF TIMESTAMP 16:48:16 2 TO_TIMESTAMP(‘2007-08-03 16:45:04’, ‘YYYY-MM-DD HH24:MI:SS’); OBJID CREATION_D LAST_CHANG ———- ———- ———- 41 03.08.2007 42 03.08.2007 43 03.08.2007 44 03.08.2007

For Full Demonstration click code_listing_9

!!! Do not forget that availability of flashback query, depends on undo_retention parameter.

References Used

Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2)