Recycle Bin in Oracle

Recycle Bin in Oracle
One of the feature of the Oracle 10g is the recyclebin. When enabled, this feature works a little bit like the familiar Windows recycle bin. Dropped tables go “into” the recyclebin, and can be restored from the recyclebin. I’ll cover some of the more subtle aspects of the recyclebin.
Practical
First, a quick review of the basics. There are two recyclebin views: USER_RECYCLEBIN and DBA_RECYCLEBIN. For convenience, the synonym RECYCLEBIN points to your USER_RECYCLEBIN. The recyclebin is enabled by default in 10g, but you can turn it on or off with the RECYCLEBIN initialization parameter, at the system or session level.
When the recyclebin is enabled, any tables that you drop do not actually get deleted. Instead, when you drop a table, Oracle just renames the table and all its associated objects (indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$. In case of DBA enabled account the recycle bin always active but in normal user account its not.
For example, consider this simple table:
SQL> create table tst (snamevarchar2(10));

Table created.

SQL> insert into tst values (‘kalyan’);
1 row created.

SQL> insert into tst values (‘sangeeta’);
1 row created.

SQL> select * from tst ;

SNAME
——
kalian
sangeeta
If the RECYCLEBIN initialization parameter is set to ON (the default in 10g), then dropping this table will place it in the recyclebin:
SQL> drop table tst;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as “UND”, can_purge as “PUR”, droptime
2 fromrecyclebin
SQL> /
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
TST BIN$WR1hc6FRSGqfAgZtouxzhQ==$0 TABLE 2015-03-04:15:26:35
All that happened to the table when we dropped it was that it got renamed. The table data is still there and can be queried just like a normal table:
Since the table data is still there, it’s very easy to “undrop” the table. This operation is known as a “flashback drop”. The command is FLASHBACK TABLE… TO BEFORE DROP, and it simply renames the BIN$… table to its original name:
SQL> flashback table tst to before drop;

Flashback complete.

SQL> select * from tst ;

SNAME
——
kalian
sangeeta

SQL> select * from recyclebin ;

no rows selected
It’s important to know that after you’ve dropped a table, it has only been renamed; the table segments are still sitting there in your tablespace, unchanged, taking up space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin. You can remove an object from the recyclebin by restoring it, or by purging it from the recyclebin.
SQL> select object_name, original_name, type, can_undrop as “UND”, can_purge as “PUR”, droptime
2 fromrecyclebin
SQL> /

OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
—————————— ————- ————————- — — ——————-
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2015-03-04:15:26:35

SQL> purge table “BIN$WR1hc6FRSGqfAgZtouxzhQ==$0” ;

Table purged.

SQL> select * from recyclebin ;

no rows selected
You have several purge options. You can also purge everything from the USER_RECYCLEBIN using PURGE RECYCLEBIN; a user with DBA privileges can purge everything from all recyclebins using DBA_RECYCLEBIN; and finally, you can purge recyclebin objects by schema and user with PURGE TABLESPACE USER .
Unless you purge them, Oracle will leave objects in the recyclebin until the tablespace runs out of space, or until you hit your user quota on the tablespace. At that point, Oracle purges the objects one at a time, starting with the ones dropped the longest time ago, until there is enough space for the current operation. If the tablespace data files are AUTOEXTEND ON, Oracle will purge recyclebin objects before it autoextends a datafile.

3 Comments

  1. KleeGoodlow9510@yandex.com' Get More says:

    I would not even know the way I appeared in this article, nevertheless i imagined this kind of put up used to be wonderful. I would not identify individual preference can be but certainly you will definitely any famed blogger for individuals who may not be currently free computer pdf ebook download. Cheers!

    • ga0qqjdus@hotmail.com' Katy says:

      My wife and i were so delighted John mngeaad to deal with his investigation using the precious recommendations he gained when using the web site. It is now and again perplexing just to possibly be giving for free secrets and techniques which other people have been trying to sell. We recognize we have got you to thank for this. The type of explanations you’ve made, the simple site menu, the friendships you will help to create it is mostly excellent, and it’s aiding our son and us reason why the topic is thrilling, and that is highly fundamental. Many thanks for everything!

  2. dwx27i1ujoc@yahoo.com' Jodie says:

    This was so helpful and easy! Do you have any ariltces on rehab?

Leave a Reply

*