Oracle tips: drop table and purge, droppurge

Oracle tips: drop table and purge, droppurge

When operating on Oracle, we often delete a table. When a deletion error occurs, we want to restore the table and sometimes release the table space immediately after the table is deleted. The following describes how to delete, restore, and release tables in Oracle.

DROP TABLE
When you drop a table, the database will not immediately release the table space, but will rename the table and put it into the recycle bin. You can view the deleted table in the following way:

select object_name,original_name from user_recyclebin where original_name = 'TEMP';OBJECT_NAMEORIGINAL_NAME-------------------------------------------------------BIN$C1LT5U0DaV7gVAAhKENZ5A==$0TEMP

Object_name: name of the object in the recycle bin;
Original_name: Original Name of the object.
After a table is placed in the recycle bin, Oracle does not use the table space for other objects, and the space occupied by the table is still occupied, unless you perform Purge manually or the database clears the table because the storage space is insufficient. If you find that the TABLE is deleted incorrectly, you can use flashback table to restore the TABLE.
FLASHBACK TABLE
The following uses the flashback table to restore a TABLE named temp:

FLASHBACK TABLE temp TO BEFORE DROP;

To before drop indicates that the table and all its dependent objects are restored. If the TABLE name is already used by another TABLE, an error is returned when you execute the flashback table command:
ORA-38312: original name is used by an existing object
In this case, you can rename the TABLE when executing the flashback table:

FLASHBACK TABLE temp TO BEFORE DROP RENAME TO temp_old;

Rename to indicates that the table is renamed after being restored. If the TABLE has been deleted multiple times, restoring the TABLE using the flashback table will restore the last deleted TABLE by default. If you want to restore the previous version, you need to query the table in the recycle bin:

select object_name,original_name,droptime from user_recyclebin where original_name = 'TEMP';OBJECT_NAMEORIGINAL_NAMEDROPTIME------------------------------------------------------------------------BIN$C1LT5U0FaV7gVAAhKENZ5A==$0TEMP2014-12-29:10:59:41BIN$C1LT5U0HaV7gVAAhKENZ5A==$0TEMP2014-12-29:10:59:54BIN$C1LT5U0GaV7gVAAhKENZ5A==$0TEMP2014-12-29:10:59:47

Then, you can use the restore and rename method to restore each object in sequence.
Note the following before using flashback table:
1) The database will restore all indexes of the table from the recycle bin. Note that bitmap join Index cannot be restored because the Index will not be placed in the recycle bin after the table is deleted, so it cannot be recovered;
2) The database will restore the trigger and constraint of the table, except for the integrity constraints pointing to other tables;
3) When a table is deleted, all materialized view logs defined on the table are also deleted, but are not placed in the recycle bin. Therefore, materialized view logs cannot be restored with the table;
4) When a table is deleted, all indexes related to the table will be placed in the recycle bin. If the database space is insufficient, the database will first clear the index space. Therefore, in this case, the table cannot be restored to all indexes;
5) if the deleted table has been purged, it cannot be restored.
If you do not restore a table after deleting it, you can use purge to clear the table.
PURGE
PURGE can completely clear the table and release the space occupied by the table. The following is a table temp in the recycle bin of PURGE:

purge table temp;

Note that you cannot roll back a purge operation. Once you perform the purge operation on a table, the table cannot be restored.
If the table is deleted multiple times, the purge operation will clear the table that was first deleted. You can also clear the content of the entire recycle bin:

PURGE RECYCLEBIN;

Drop table... PURGE
We can also delete the table and release the space in one step. Here we will delete the temp table test:

drop table temp purge;

This method is equivalent to deleting the temp table and then performing the purge operation on the table. Note: you cannot roll back a drop table operation with PURGE or restore a TABLE deleted with PURGE.