Skip to content
Home » Oracle Database » How Oracle Purge RecycleBin

How Oracle Purge RecycleBin

In this post, we'll talk about the following topics.

  1. Feature of Recyclebin
  2. Prerequisite of Using Recyclebin
  3. Ways to Purge Recyclebin

Feature of Recyclebin

Since 10g, we have recycle bin that can store dropped tables and indexes. Whenever we need them, we can flashback the dropped tables or indexes from the recycle bin.

In an unlikely situation, the database purges dropped objects out of the recycle bin only when the tablespace reaches its limit. Yes, it sounds pretty safe for DBA to prevent some space issues, but the drawback is that those dropped objects still occupy user's space quota on that tablespace.

A more proactive manner is to purge recycle bin by users who really concern about space quota. Furthermore, DBA should teach users about how to purge their recycle bin on a regular basis. In this post, I will show you how.

Prerequisite of Using Recyclebin

You have to make sure that the feature of recycle bin is enable.

SQL> conn / as sysdba
Connected.
SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

The default setting of RECYCLEBIN is ON. If you saw OFF, that could be someone turned it off intentionally.

Next, we drop a table for later demonstration.

SQL> conn hr/hr
Connected.
SQL> drop table job_history;

Table dropped.

By the way, if you don't want the table get into the recycle bin, you can append PURGE to the statement to indicate that you don't want to keep the table. The space will be released back to the tablespace.

To check what objects in the recycle bin, we can query the view.

SQL> column object_name format a30;
SQL> column original_name format a25;
SQL> column type format a8;
SQL> column ts_name format a8;
SQL> select object_name, original_name, type, ts_name from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME             TYPE     TS_NAME
------------------------------ ------------------------- -------- --------
BIN$shkNCfO7CCrgUwsqqMAvrA==$0 JHIST_EMPLOYEE_IX         INDEX    EXAMPLE
BIN$shkNCfO8CCrgUwsqqMAvrA==$0 JHIST_JOB_IX              INDEX    EXAMPLE
BIN$shkNCfO6CCrgUwsqqMAvrA==$0 JHIST_DEPARTMENT_IX       INDEX    EXAMPLE
BIN$shkNCfO9CCrgUwsqqMAvrA==$0 JHIST_EMP_ID_ST_DATE_PK   INDEX    EXAMPLE
BIN$shkNCfO+CCrgUwsqqMAvrA==$0 JOB_HISTORY               TABLE    EXAMPLE

By the way, the view RECYCLEBIN is just a synonym for the view USER_RECYCLEBIN.

Ways to Purge Recyclebin

There're 4 dimensions that can purge dropped objects from recycle bin in Oracle.

  1. Object Level
  2. Schema Level
  3. Tablespace Level
  4. Database Level

Now, let's see how we purge those object from the recycle bin.

Object Level

Objects can be table or index, no other types of object (so far). You can either specify the system-generated name or the original name to purge it.

System-Generated Name

You can find it out from the above query.

SQL> purge table "BIN$shkNCfO+CCrgUwsqqMAvrA==$0";

Don't forget to use quotation marks to enclose the name in case of errors like ORA-00933. As for to quote or not to quote, I explained in How to Resolve ORA-00904 Invalid Identifier pretty much.

Original Object Name

Directly specify the table name that is used to be.

SQL> purge table job_history;

If there're several versions of the table with the same name in the recycle bin, the oldest one is purged.

Schema Level

It can only purge objects in the current schema.

SQL> purge recyclebin;

Tablespace Level

Of course, you must be a privileged user.

SQL> conn / as sysdba
Connected.
SQL> purge tablespace example;

All dropped objects in the specified tablespace are purged.

Database Level

You can purge database-wide objects in the recycle bin, which means, all objects.

SQL> purge dba_recyclebin;

Only users who has SYSDBA or PURGE DBA_RECYCLEBIN system privilege are able to perform the operation.

Pluggable Database (PDB)

For multitenant environments, PURGE DBA_RECYCLEBIN cannot be done across containers, so you need to switch into a specific PDB, then do it. For example:

SQL> alter session set container=ORCLPDB;

Session altered.

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

For more various usages, you may check: Oracle Database : Release 19 : SQL Language Reference : Purge Syntax.

Leave a Reply

Your email address will not be published. Required fields are marked *