Drop table with purge option; RECYCLEBIN

• Article Type: General
• Product: Aleph
• Product Version: 18.01

Description:
All SQL table creation processes $alephm_root/sql_tab simply drop a table. In Oracle 10, this is now creating some real problems because the tables are not actually dropped but rather only placed in a recycle bin.

I would suggest that the scripts be updated to do one of two things:

1) If the table already exists, truncate it rather than drop and immediately recreate it. This will allow the table to use the same space as before. By dropping it, there are no guarantees that the table will be created using the same file location and fragmentation can result.

2) If you do have to drop a table for some reason, drop that table with the "PURGE" addition to make sure the table is fully dropped. Without the PURGE option, the table goes into the recycle bin until it is manually deleted later. This can fill up flashback recovery space and adds to overhead. Unless the table might want to be restored (undropped), the script should fully drop the table by adding the "PURGE" option.

Resolution:
It was decided not to change the existing procedures to drop tables with the PURGE parameter or truncate statement. We have changed the setting of Oracle 10 in Aleph18: the 'recycle bin' was disabled by setting the recycle_bin switch to off in init<SID>.ora configuration file.

You can turn the RECYCLEBIN ON and OFF at a session level by using ALTER SESSION SET RECYCLEBIN=OFF;

and at the system level: ALTER SYSTEM SET RECYCLEBIN=OFF;

Note: In Oracle 11, you need to do instead:

ALTER SYSTEM SET recyclebin = OFF DEFERRED;

If the Oracle is set with RECYCLEBIN=ON (use 'show parameter recyclebin' to find out) you can permanently disable it by changing the setting in init<SID>.ora file, just add recycle_bin=off; or change to off if it already exists (if it doesn't exist the default is recycle_bin=on).

If you already have files in the 'recycle bin' (it is a virtual 'recycle bin', actually the dropped tables are renamed by prefixing the table name with BIN$* and adding suffix for the version), you can empty the entire recycle bin with the command PURGE RECYCLEBIN and purge everyone's recycle bin (as sysdba) with the command PURGE DBA_RECYCLEBIN.