In this post, we'll talk about the following topics.
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.
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.