Wednesday, March 10, 2010

Oracle Flashback Drop and Recycle bin

The Oracle 10g provides the ability to reinstating an accidentally dropped table, which is called Flashback Drop.

When a table is dropped, the database does not immediately remove the space associated with that table. Instead, the table is renamed to a system-defined name and placed in the Recycle bin. The flashback drop operations recovers from recycle bin.

SQL> DROP TABLE employee_tbl;

You can see the system-defined name of the table.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$gXxxELu7aQ/gQAoKd5l2Hg==$0 TABLE

You can see the dropped table in the recycle bin using
SQL> show Recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-------------- ------------------------------ ------------ ----------------
EMPLOYEE_TBL BIN$gXxxELu7aQ/gQAoKd5l2Hg==$0 TABLE 2010-03-01:09:10:00

Recover the dropped table from Recycle bin using
SQL> FLASHBACK TABLE employee_tbl TO BEFORE DROP;

Recover the dropped table from Recycle bin with new name using
SQL> FLASHBACK TABLE employee_tbl TO BEFORE DROP RENAME TO employee_tmp;

You can also drop a table completely with out needing a flashback using
SQL> DROP TABLE employee_tbl PURGE;

The tablespace will not free up space until the table will remain until recycle bin is purged. You can purge table explicitly from recycle bin using
SQL> PURGE TABLE employee_tbl;

Purge all tables in recycle bin using
SQL> PURGE RECYCLEBIN;

As a DBA can purge all the objects in any tablespace using
SQL> PURGE DBA_RECYCLEBIN;

Purge all the objects in recycle bin in a tablespace using
PURGE TABLESPACE employee_tbs;

Purge all the objects in recycle bin in a tablespace specific to a user only using
PURGE TABLESPACE employee_tbs USER emp_user;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

11 comments:

  1. Very helpful information, really appreciate it.

    ReplyDelete
  2. can i flashback table * to before drop ?

    ReplyDelete
  3. Using EM you can flashback more than one table to before drop. But it is not possible using command line.

    Regards
    Satish
    http://www.oracleracexpert.com

    ReplyDelete

  4. شركة نقل عفش بالدمام الشرق الاوسط متحصصه فى نقل عفش واثاث بالدمام ونقل العفش بالخبر كما انها توفر شركة نقل عفش بالجبيل والخبر وشركة نقل عفش بالقطيف والاحساء وجميع خدمات نقل العفش والاثاث بالمنطقة الشرقية بارخص اسعار نقل عفش بالدمام وتقدم ايضا شركة تخزين عفش بالدمام والخبر
    نقل عفش بالدمام
    شركة نقل اثاث بالدمام
    شركة نقل اثاث بالخبر

    ReplyDelete
  5. من اكبر وافضل واقوى شركات تنظيف خزانات المياه بجدة تلك التي تقوم بأعمال تنظيف وتعقيم خزانات المياه في جدة بمنتهى الدقة والاحترافية وتقدم شركة نظافة خزانات بجدة التي تستخدم الخدمات الجيدة في تنظيف وتعقيم الخزانات لكي تحافظ على الماء نظيفا ومعقما اطول فترة زمنية ممكنة لكي تكون مياهك نظيفة

    ReplyDelete
  6. Explore the profiles of guwahati escorts, guwahati call girls, call girls in guwahati
    and escorts in guwahati for female companionship in top rated hotels.
    Get affordable escort services in guwahati hotels now.
    College escorts in Guwahati |
    Housewife escorts in Guwahati |
    Assamese call girls in Guwahati |
    Nepali call girls in Guwahati |
    Female escorts in Guwahati |
    High profile escorts in Guwahati |
    Russian escorts in Guwahati |
    College call girls in Guwahati |

    ReplyDelete
  7. Yes, such mind-blowing escorts can be found in my agency of such, across the entire Guwahati.
    Our Call Girls In Guwahati Agency's Escorts available now.Nitu Roy is a popular and trusted
    name in the field of call girl services.
    Guwahati Call Girl
    Call Girl in Guwahati

    ReplyDelete
  8. We serve our Guwahati escort service in the whole Guwahati city Our services are at
    the top when it comes to Escort Service in guwahati. We have the
    best call girl in guwahati to satisfy.
    Escorts in Guwahati
    Guwahati Escorts

    ReplyDelete
  9. Diya Roy is a premier escort agency in Guwahati that provides call girl services or
    escort services in Guwahati hotels at cheap and affordable fixed rates
    Call Girl service in Guwahati
    Escort service in Guwahati

    ReplyDelete