Sometime back, I read an interesting post at this URL. http://blog.psftdba.com/
The post titled, "PeopleSoft and the Oracle Recycle Bin".
PeopleSoft -> CRM /HRMS application
Recycle BIN -> A concept introduced in Oracle 10g that "resembles" Windows Recycle BIN feature. i.e. When you drop a table the table does not get physically deleted, but rather is moved to logical area called "Recycle Bin". From there, it will be possible to restore the table back.
The author of the post says that the alters made in PSFT, using "Drop" and create new format, would add space in the database. However, an ideal PeopleSoft database administrator must
1) See, if an alter by table rename is required in first place. ("see if Alter in place can be used rather")
2) If alter by table rename is done, then take the script that is generated in application designer and add a PURGE to the DROP TABLE construct. This would ensure that the table is deleted forever.
These are far better that turning the entire RECYCLE BIN feature off. (as recommended in the blog post).The feature is really an useful one, which helps to recover the lost data when a table is dropped accidentally.
Since you have commented on my blog, I thought I would reply here.
ReplyDeletei) I generally avoid using ALTER TABLE 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.
In non-development environments, I would use it selectively.
I think I don't like the default value for RECYCLEBIN being ON.
Read this post. With my experience in PeopleSoft, I feel that application designer is not a comfortable tool to create / modify objects when it comes to PeopleSoft / Oracle DB. For example, it gives COMMIT statements after every DDL. I always prefer to edit the file my myself and run it in SQLPLUS or any other client for that sake. If that is the case, adding a PURGE should not be a problem. Anyway, the post is useful to know the recent changes!
ReplyDeleteBut DDL statements implicitly commit any transaction before they start!
ReplyDelete