How to Purge Recycle Bin in Oracle

  • by

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 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

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

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

  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.

A. 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.

1. 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.

2. 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.

B. Schema Level

It can only purge objects in the current schema.

SQL> purge recyclebin;

C. 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.

D. Database Level

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

SQL> purge dba_recyclebin;

Only users with SYSDBA or PURGE DBA_RECYCLEBIN system privilege can do the operation.

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 *