Oracle10g RECYCLEBIN

Tuesday, August 12, 2008

Shortly after using Oracle10g on a more regular basis, I knew about the RECYCLEBIN and how it might be a good idea to purge it on a nightly basis especially for a development environment. However, I recently noticed on one of my development databases the developers appeared to be dropping and re-creating all objects in a schema on a daily basis. Sometimes more than once a day which made for a lengthy purge process in terms of duration.

I do not utilize the Flashback Table feature which made me ponder the thought of how to disable the RECYCLEBIN and just have objects dropped and be forever gone as in versions prior to Oracle10g.

Now I could explain to the developers that if they use the PURGE clause, this will delete the objects and bypass the RECYCLEBIN.

DROP TABLE t1 PURGE;

But somehow I know that knowledge will be lost somewhere down the line and I will be right back where I started and still purging the RECYCLEBIN on a nightly basis.

Instead I disabled the RECYCLEBIN in the currently running instance:

alter system set recyclebin = off;

and set the recyclebin parameter in the init.ora.

recyclebin = off

Now the objects will be dropped and forever gone. It’s a trade-off I’m willing to live with in this specific development environment.

You can find more detailed information about the RECYCLEBIN in Madan Mohan K’s post called “How to Purge the RECYCLEBIN in Oracle 10g“.

References
Oracle® Database Reference 10g Release 2
Oracle® Database Administrator’s Guide 10g Release 2