Oracle RecycleBin, Flashback and Purge Mechanism

Hi all,

In this article, I will be talking about what is the recyclebin and flashback mechanism, How do we activate this mechanism and how it is used.

Recylebin is a table that include tables or triggers or indexes … which is dropped before.

So why the dropped table is stored by the system in a place?

It is not difficult to guess the answer to this question. System is store these records in order to return back the table which was dropped. We accidentally dropped tables, We can also restore using Oracle RecycleBin Mechanism.

If you want to use recyclebin and flashback mechanism, you will set on recyclebin parameter system on the basis. Otherwise you cant use this feature of database. Any possibility of this feature by running the following script on the state. However this parameter may come on state. Therefore you should check this parameter value using v$parameters dynamic performance view.

SELECT value FROM v$parameter where name like ‘%recyclebin%’;

–If parameter value is on, you should run by the folowing –script.alter system set recyclebin=on;

alter system set recyclebin=on;

This parameter has been activated or active in our system After making sure that we can quickly test the functionality of recylebin and flashback table structure.

First of all, create and delete the dummy table and see the status of RecycleBin.

     create table deneme123456 as (select * from v$sql);        –created table

select count(*) from deneme123456;                                  –number of rows 3901

drop table deneme123456;                                                   –droped table

Now, We are looking recyclebin.

select count(*) from deneme123456

As we have seen, the table fell into RecycleBin registration. Let’s get back to the table now, here I mentioned at the beginning of the article will use the term flashback there.

    flashback table deneme123456 to before drop;
select count(*) from deneme123456; — number of rows 3901

After that, we select again recyclebin, we cant see this record. Record was deleted into Recyclebin.

Recyclebin use disk space that including tablespace area. This mean  is limitation. It is about disk space. For this limitations, very large table couldnt get back. It may not be possible. At this point, I think that’s worth checking tablesapce. Also Recyclebin be filled fully, this state prevent our normal working schedule to. To check this memory area, We will drop tables(that we will sure not get back again) using purge keyword, we provide not restore that tables at the recyclebin. You can see this case by the following example.

drop table deneme123456 purge;                                                      -– both drop table and prevent fell into recyclebin

This feature exist in Oracle 10g version. This feature sometimes get off the hook 🙂 If we use this feature carefully.


About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Root, Uncategorized and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply

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

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