In previous versions of Oracle Database, the moment you delete a table Drop Table will also be removed from the Data Dictionary, and it requires a database recovery if you want to recover a single table that was deleted by mistake. There is no doubt that it is a very expensive solution in terms of time and in terms of the fact that we will lose some Data.

When deleting a table in previous versions of Oracle, the spaces that it was independent of are freed, but in the Oracle 10g version, the spaces that the table and its objects are independent of are not freed at the moment the table is deleted, but the table is temporarily placed in the Recycle Bin and the table remains owned by the user even though it is moved to the Recycle Bin, but of course The moment it is moved to the Recycle Bin, it is renamed so that there is no conflict in the names if I want to create another table with the same name. This situation gives us the opportunity to retrieve the tables from the Recycle Bin without having to retrieve the database, which reduces the retrieval period for us and also without losing any data. Simply put, the Recycle Bin (BIN RECYCLE) in the Oracle database is very similar to the Recycle Bin in the Windows operating system.

The Recycle Bin (RECYCLE BIN) can be accessed by:

DBA_RECYCLEBIN

USER_RECYCLEBIN

You can also query using the command:

SHOW RECYCLEBIN

Now let's follow this scenario together: The user deletes the USER_MASTER table, and then we query the RECYCLE BIN recycle bin.

SHOW RECYCLE BIN;

I noticed with me that even though we deleted the USER_MASTER table, it is still in the recycle bin and we can also restore it.

FLASHBACK TABLE USER_MASTER TO BEFORE DROP;

We have restored the table from the Recycle Bin but the restoration process may fail if we created a table with the same name before the restoration process so we can rename it to avoid name conflict.

FLASHBACK TABLE USER_MASTER TO BEFORE DROP RENAME TO MASTER;

We have restored the table but with a new name.

SELECT * FROM USER_RECYCLEBIN;

The Recycle Bin can be cleaned and tables can be purged from the Recycle Bin using the PURGE command.

PURGE TABLE MASTER;

The table can also be deleted by the user without putting the table in the Recycle Bin, but rather deleting it directly from the database using the DROP TABLE <TABLE_NAME> PURGE command.

DROP TABLE EMP PURGE;

ﺇIf the cases in which tables are deleted from the database without going through the Recycle Bin:

1- Using the command DROP TABLE <TABLE_NAME> PURGE

2- The moment of deleting the TABLESPACE that contains the tables by the command

DROP TABLESPACE <TABLESPACE_NAME> INCLUDING CONTENTS

3- When deleting the user that contains the tables by the command

.DROP USER <USER_NAME> CASCADE

If you can query the Recycle Bin via:

SHOW RECYCLEBIN

USER_RECYCLEBIN

DBA_RECYCLEBIN

As I mentioned earlier, you can generally delete the table without going through the Recycle Bin via the command:

DROP TABLE TABLE_NAME PURGE

You can delete the table from the Recycle Bin via the command:

PURGE TABLE TABLE_NAME

You can delete the Index Directly and without going through the Recycle Bin via the command:

DROP INDEX INDEX_NAME PURGE

You can delete all deleted TABLESPACE objects via the command:

PURGE TABLESPACE TABLESPACE_NAME

You can also delete all deleted TABLESPACE objects that belong to a specific user via the command:

PURGE TABLESPACE TABLESPACE_NAME USER USER_NAME

You can also delete All objects deleted for a specific user from the Recycle Bin using the command:

PURGE USER_RECYCLEBIN

All objects in the Recycle Bin can be deleted using the command:

PURGE DBA_RECYCLE_BIN

Objects belonging to SYSTEM TABLESPACE cannot be recovered using the FLASHBACK DRPO.