Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 149,223
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Flashback Drop (Recycle Bin)

Posted by FatDBA on July 16, 2012

When you’ve deleted a Table and soon after thyat deletion one of the user created a table with the same name then in that case we have to perform some steps to mitigate the conflict otherwise you’ll recieve error ‘Object trying to create already exists’ and will not allow you to restore the Old Table.

Deleted Table emp1 from system —

SQL> drop table emp1;
Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME        OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-
EMP1             BIN$xNis/0KmyEPgQAB/AQBy6Q==$0 TABLE        2012-07-15:12:16:41

Created a table with same name after deletion – emp1 —

SQL> create table emp1 (name varchar2(10));
Table created.
SQL> drop table emp1;
Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME        OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-
EMP1             BIN$xNis/0KnyEPgQAB/AQBy6Q==$0 TABLE        2012-07-15:12:17:31
EMP1             BIN$xNis/0KmyEPgQAB/AQBy6Q==$0 TABLE        2012-07-15:12:16:41

Now if i’ll try to Flashback table emp1 then it will restore the one with latest Time Stamp.
SQL>  show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME        OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-
EMP1             BIN$xNis/0KmyEPgQAB/AQBy6Q==$0 TABLE        2012-07-15:12:16:41
This can also be performed using Time Stamp —
FLASHBACK TABLE emp1 TO TIMESTAMP TO_TIMESTAMP(‘2012-07-15:12:16:41’, ‘YYYY-MM-DD HH:MI:SS’);
During the flashback operation the table can be renamed.
FLASHBACK TABLE flashback_drop_test TO BEFORE DROP RENAME TO flashback_drop_test_old;

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

 
%d bloggers like this: