Coskan’s Approach to Oracle

August 3, 2007

Flaschback Query on Dropped Table

Filed under: Backup/Recovery, Tips — coskan @ 2:06 pm

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)

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers