All Purge commands in Oracle -Delete an object permanently

All Purge commands in Oracle -Delete an object permanently

Whenever we drop any objects other than objects reside in system tablespace using DROP command the objects became unavailable and reside in recyclebin. In fact they reside in the same tablespace where it was and thus does not free any space in the tablespace. In order to remove the objects permanently from the recyclebin we have to use PURGE command. With PURGE command we can do,
•Remove a table or index from your recycle bin and release all of the space associated with the object, or
•To remove the entire recycle bin, or
•To remove part of all of a dropped tablespace from the recycle bin.

However during drop you may wish not to put objects in recyclebin. With DROP command you may wish to drop it permanently not residing in recyclebin. Like,

SQL> create table t(a number);
Table created.

SQL> purge table t;
purge table t
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN
As PURGE command is only to drop objects from recyclebin so purge t fails with ORA-38307.

In order to drop t along with recyclebin use,
SQL> drop table t purge;
Table dropped.

To see the all contents of the recyclebin query from DBA_RECYCLEBIN. A user can see his own schma recylebin by querying from USER_RECYCLEBIN. A synonym of USER_RECYCLEBIN is RECYCLEBIN.

The list of Purge Commands are listed below.
1)Purge Table/Index:
•Use
PURGE TABLE table_name
to purge table and to purge index use
PURGE INDEX index_name.
To purge test table which reside in recyclebin now or in in other which was dropped previously by DROP TABLE TEST command and now in recyclebin issue,
PURGE TABLE TEST;

•In the name you can specify either the original user-specified name or the system-generated name in the recylebin.

•While purging objects from recylebin if you specify the user-specified name, and if the recycle bin contains more than one object of that name, then the database purges the object that has been in the recycle bin the longest.

•System generated name in recylebin are unique. So if you purge an object with system generated name specified object is purged.

•When the database purges a table, all table partitions, LOBs and LOB partitions,
indexes, and other dependent objects of that table are also purged.

2)PURGE RECYCLEBIN: To delete/remove objects of current users from the recylebin use PURGE RECYCLEBIN. Other schema users recyclebin objects will be intact. Command is
SQL>PURGE RECYCLEBIN;

3)PURGE DBA_RECYCLEBIN: To clean up all user's recylebin you can use PURGE DBA_RECYCLEBIN statement. Only user having SYSDBA system privilege can do this task. Command is simply,
SQL>CONN / AS SYSDBA
SQL>PURGE DBA_RECYCLEBIN;

4)PURGE TABLESPACE tablespace_name: With PURGE TABLESPACE tablespace clause you can purge all objects residing in the specified tablespace from the recycle bin. To clean up all objects that reside in recylebin of USERS tablespace use,
SQL>PURGE TABLESPACE USERS;

5)PURGE TABLESPACE tablespace USER username: With this clause you can reclaim space in a tablespace for a specified user. The specified user's objects from the specified tablespace recyclebin will be cleaned up. This operation is useful when a particular user is running low on disk quota for the specified tablespace.

To clean up recylebin of user ARJU from TABLESPACE USERS use,
SQL> PURGE TABLESPACE USERS USER ARJU;