It works just like the Windows recycle bin. You can drop a table and then flash it back (they didn't call it UNDROP because Oracle marketing now calls everything Flashback). So when you drop a table, Oracle marks it as dropped, and renames it with a system generated name beginning with BIN$. You can look at the contents of the Recycle Bin through a catalogue view.
>create table t (a number);
>drop table t;
>select * from user_recyclebin
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME DROPSCN
------------------------------ ------------------- ------------------- ----------
PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT SPACE
-------------------------------- --- --- ---------- ----------- ------------ ----------
BIN$ZCLja8iAA9LgRAAOf+3h5A==$0 T DROP TABLE
PSDEFAULT 2009-03-02:13:35:12 2009-03-02:13:35:14 9.7561E+12
YES YES 776642 776642 776642 4
If you don't want a table to go into the recycle bin when you drop it, you can code DROP TABLE ... PURGE - but PeopleSoft doesn't do this.
PeopleSoft alter scripts usually drop and recreate tables. In a production system this is often the best option, otherwise you run the risk of causing rows to migrate to other blocks when you add new columns. In one system, I found 17000 objects in the recycle bin, occupying 1.3Gb.
My Opinion
Personally, I would disable the recycle bin by setting the initialisation parameter RECYCLEBIN = OFF in all PeopleSoft environments, with the possible exception of the development environment.
The RECYCLEBIN parameter can also be set dynamically at session or system level. You could perhaps turn it on prior to upgrade/data migration procedures, and then when satisfied turn it off again and manually purge the recycle bin.
I think Oracle features should be used knowingly. It doesn't matter whether you decide to use a feature or not. It is important that in making that decision you have thought about how to deal with the implications, rather than be surprised later.
@go-faster.co.uk on BlueSky
Posts
4 comments :
Hey, nice post!
What is your response to
http://thinktibits.blogspot.com/2009/08/peoplesoft-and-recycle-bin.html
Cheers,
i) I generally avoid using alter in place scripts (which generally use ALTER TABLE commands) in a production system. I prefer to recreate tables because adding and populating new columns can lead to row migration.
ii) You cannot get Application Designer to automatically add the PURGE option to DROP TABLE commands. You have to go in there and add them manually.
I didn't say you shouldn't use the Recycle Bin. However, it is enabled by default, and most developers don't realise that. The result is lots of debris builds up.
I would use it selectively in non-development environments.
because there always is the possibility of the recycle bin being useful, I've kept it on in my PeopleSoft databases. however, when performing the once-a-week cold backup, I automatically purge the dba_recyclebin; otherwise, it would grow to unmanageable proportions.
Hi,
I read the post at http://thinktibits.blogspot.com/2009/08/peoplesoft-and-recycle-bin.html and still agree with what David has said!
Post a Comment