Skip to main content
ExLibris

Knowledge Assistant

BETA
  • Subscribe by RSS
  • Back
    Aleph

     

    Ex Libris Knowledge Center
    1. Search site
      Go back to previous article
      1. Sign in
        • Sign in
        • Forgot password
    1. Home
    2. Aleph
    3. Knowledge Articles
    4. Drop table with purge option; RECYCLEBIN

    Drop table with purge option; RECYCLEBIN

    1. Last updated
    2. Save as PDF
    3. Share
      1. Share
      2. Tweet
      3. Share
    1. Additional Information
    • 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.

    Additional Information

    faq


    • Article last edited: 10/8/2013
    View article in the Exlibris Knowledge Center
    1. Back to top
      • drop downs menues display only code and not the text
      • DROWN vulnerability (CVE-2016-0800)
    • Was this article helpful?

    Recommended articles

    1. Article type
      Topic
      Language
      English
      Product
      Aleph
    2. Tags
      1. 18.01
      2. contype:kba
      3. Prod:Aleph
      4. Type:General
    1. © Copyright 2025 Ex Libris Knowledge Center
    2. Powered by CXone Expert ®
    • Term of Use
    • Privacy Policy
    • Contact Us
    2025 Ex Libris. All rights reserved