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)

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

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

%d bloggers like this: