Last Friday, I received an email concerning a problem a DBA was having migrating objects from one tablespace to another. They thought they had migrated all the objects, but they found some indexes when querying the DBA_SEGMENTS view. These indexes were not visible in the DBA_INDEXES view, which was my first clue to the problem. The next clue was when I found out all the indexes had a prefix of “BIN$”. The DBA did not realize that these were objects that had been dropped previously and were sitting in the recyclebin.
The reyclebin is a little-mentioned feature of the Oracle database that I blogged about back in 2011 when it saved me from an embarrassing and tricky recovery. In honor of this unsung feature, I found the old blog, dusted it off, and updated it where needed. Here we go…
What is recyclebin?
The Oracle recyclebin has been around since version 10g release 1 as part of a feature called Flashback Drop and in my opinion, continues to be a little-known and little-understood feature. It has saved me from potential data loss on at least one occasion, though, so I think it deserves some consideration.
The recyclebin is actually a data dictionary table containing information about dropped objects. Each named user account has their own recyclebin. Unless a user has the SYSDBA privilege, the only objects that the user has access to in the recyclebin are those that the user owns. A user can view his objects in the recyclebin using the following statement:
SELECT * FROM RECYCLEBIN;
When the recyclebin is enabled, dropped tables and any associated objects such as indexes, constraints, and nested tables are removed from the DBA_<objects> views that track database objects. During this process, the table is renamed. The new name has a telltale “BIN$” prefix placed on it. These objects still occupy space in the tablespace where they originated. They continue to count against user space quotas, until purged from the recyclebin either manually or by the database because of tablespace space constraints.
When you drop an entire tablespace including its contents, the objects in the tablespace are NOT placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the dropped tablespace. The database also purges any recycle bin entries for objects in a tablespace you drop. Likewise:
- When you drop a user, any objects belonging to the user are NOT placed in the recyclebin and any objects in the recyclebin owned by that user are purged.
- When you drop a cluster, its member tables are NOT placed in the recyclebin and any former member tables in the recyclebin are purged.
- When you drop a type, any dependent objects such as subtypes are NOT placed in the recyclebin and any former dependent objects in the recyclebin are purged.
In 10g release 1, the recyclebin was enabled by default and there was only an undocumented parameter to disable it. This parameter was _recyclebin which defaulted to a value of TRUE. The recyclebin initialization parameter was introduced in 10g release 2 and had a default setting of ON It was a dynamic parameter and could be enabled or disabled (ON or OFF) with either an ‘alter session’ or an ‘alter system’ command. Oracle 11g release 1 made no changes to the basic feature, except to add the ‘deferred’ option to the ‘alter session’ command to allow subsequent, but not the current, session to be affected. Release 2 remained unchanged from release 1. These features remain unchained into 12c release 2. Also, 12c includes more table-associated objects that is moves to the recyclebin when a table is dropped. The full list of these objects are:
Constraints (excluding foreign key constraints)
To find out if the recyclebin is enabled, except for 10g release 1, you can execute ‘show parameter recyclebin’ from a SQL prompt as a user with sysdba privileges.
SQL> show parameter recyclebin NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on
I would recommend keeping the recyclebin enabled at all times, except during an upgrade, to add an extra layer of recovery options to the existing environment.
Once the recyclebin is enabled, you don’t have to give much thought to its use. Every time a ‘drop table’ command is executed, the table and its associated objects are placed in the recyclebin and renamed with a system-generated naming standard. Again, the objects actually remain in their native tablespaces. Because objects age out of the recyclebin based on demands for space, it is always a good idea to back up a table with an export or execute create table as select (CTAS) to create a backup table before dropping it. This is especially true for dropping very large tables which may not fit in the recyclebin.
I was unable to find any information on exactly how much space is allocated to the recyclebin, but suspect that it can consume all available space in the respective tablespace where the objects reside.
To find out how much space per owner and tablespace recyclebin data is using, you can use this query:
select owner, tablespace_name, round(sum(bytes)/1024/1024/1024,1) "RECYCLE_GB" from dba_segments where segment_name like 'BIN$%' group by owner, tablespace_name order by owner, tablespace_name;
In the event that you do not want a table to be preserved in the recyclebin, the addition of the ‘purge’ option at the end of the ‘drop table’ statement ensures that the recyclebin bin is bypassed and the table as well as the associated objects are dropped from the database. Just ensure you have a backup of the table, just in case.
Recovering tables from the recyclebin
To recover a table from the recyclebin, you need to know if it is available. Use the following query to get this information (the owner was designated to limit the output):
select owner, object_name, original_name, droptime from dba_recyclebin where owner = ‘CMN’; OWNER OBJECT_NAME ORIGINAL_NAME DROPTIME ------------------------------ ------------------------------ -------------------------------- ------------------- CMN BIN$o5lg4yWjH3ngRAAUT0rhuQ==$0 CMN_BASE_CONTACT 2011-05-18:23:12:42 CMN BIN$o5lg4yWiH3ngRAAUT0rhuQ==$0 CMN_BASE_CONTACT_PK 2011-05-18:23:12:42 CMN BIN$o5icV6rtByXgRAAUT0rhuQ==$0 CMN_BASE_CONTACT 2011-05-18:22:51:07 CMN BIN$o5icV6rfByXgRAAUT0rhuQ==$0 CMN_BASE_CONTACT 2011-05-18:22:17:45 CMN BIN$o5lg4yWvH3ngRAAUT0rhuQ==$0 CMN_BASE_CONTACT 2011-05-18:23:15:11 CMN BIN$o5icV6rgByXgRAAUT0rhuQ==$0 EXECUTE_TABLE 2011-05-18:22:49:58 CMN BIN$o5mU5fo6KF/gRAAUT0rhuQ==$0 EXECUTE_TABLE 2011-05-18:23:30:15 CMN BIN$o5icV6ruByXgRAAUT0rhuQ==$0 EXECUTE_TABLE 2011-05-18:22:53:23 CMN BIN$o5mU5foqKF/gRAAUT0rhuQ==$0 CMN_BASE_CONTACT 2011-05-18:23:27:15 CMN BIN$o5mU5fo5KF/gRAAUT0rhuQ==$0 CMN_BASE_CONTACT 2011-05-18:23:30:08 CMN BIN$o5mU5fo4KF/gRAAUT0rhuQ==$0 CMN_BASE_CONTACT_PK 2011-05-18:23:30:08 CMN BIN$o5mU5fpGKF/gRAAUT0rhuQ==$0 CMN_BASE_CONTACT 2011-05-18:23:32:02 CMN BIN$o5mU5fpHKF/gRAAUT0rhuQ==$0 EXECUTE_TABLE 2011-05-18:23:32:02
Here you can see which tables for the CMN user are in the recyclebin and when they were dropped. You may have noticed that there are several versions of the BASE_CONTACT table in this recyclebin differentiated by their droptime value.
To also help determine which table holds the data you want recovered, you can query recyclebin tables like regular tables.
select count(*) from cmn."BIN$o5mU5fpGKF/gRAAUT0rhuQ==$0"; COUNT(*) ---------- 89
It is important to use both the owner identifier (CMN in this case) and double-quotation marks because of the special characters in the recyclebin table name.
Once the table you want to recover has been identified, there are a few different methods by which you can recover the table or the individual data. The ‘flashback table <table_name> to before drop’ command will recover the latest copy of the table in the recyclebin. You can also specify the ‘rename to <alt_table_name>’ at the end of the command to recover the table to a different table name to recover specific data. In the case where there are several copies of the table in the recyclebin and you are not interested in the latest copy, you can do a simple create table as select (CTAS) operation using the recyclebin table name, not the original name. Remember to use double-quotation marks around the recyclebin table name due to the special characters.
When you restore a table from the recyclebin, dependent objects such as indexes DO NOT get their original names back; they retain their system-generated recyclebin names. You must manually rename dependent objects to restore their original names. If you plan to manually restore original names for dependent objects, ensure that you make note of each dependent object’s system-generated recyclebin name before you restore the table.
Purging the recyclebin
You can purge the recyclebin completely or for objects belonging to individual users. As stated earlier, each user account in the database will automatically have a recyclebin assigned to it. A user can simply execute the ‘purge recyclebin’ command to purge their own recyclebin. Users with the sysdba privilege can purse the entire recyclebin with the ‘purge dba_recyclebin’ command. Be careful, though, as this command also purges ALL users recyclebins as well. Purging the entire reyclebin should be reserved for upgrade situations, or unless otherwise directed by Oracle Support.
You can also perform selective purges of the recyclebin as a sysdba-privileged user. For example ‘purge tablespace tmpdata’ only purges those objects in the recyclebin belonging to the tmpdata tablespace regardless of which user owns them. Also, ‘purge tablespace tmpdata user jon’ will purge only those objects in the recyclebin belonging to the tmpdata tablespace owned by the named user jon. You can also purge individual tables from the recyclebin with the command ‘purge table <table_name>’. The table name in this command can be either the system-generated recyclebin name or the original name of the table. As of Oracle version 12c release 2, you need to purge individual user’s objects out of the recyclebin by tablespace as a sysdba-privileged user.
The recyclebin is one of those features that you forget, but are very thankful when it is available and you need it.
Oracle bug 19264323 prevents the recycle bin from purging index segments which enforce a constraint automatically when under space pressure. This bug only affects Oracle version 22.214.171.124 and is fixed in Oracle version 12.2. The workaround is to purge the recycle bin manually if this bug is encountered and there is a patch available to fix this bug. Reach out through your Oracle support channel for more details.