Oracle SQL - DROP TABLE Command and recycle bin

  1. HOME
  2. Oracle SQL
  3. Table
  4. DROP TABLE Command and recycle bin

Introduction

You can drop your tables with the DROP TABLE command.

You cannot roll back a DROP TABLE command.

This is true for all DDL statements: CREATE, ALTER, and DROP.

Oracle Database 10g introduced the concept of the database recycle bin.

By default, all dropped tables and their dependent objects initially end up in the recycle bin.

You can query the recycle bin using the [USER_]RECYCLEBIN view.

To make sure we start with an empty recycle bin, we begin the experiment with a PURGE command. 

SQL> purge recyclebin;
Recyclebin purged.

SQL> drop table history;
Table dropped.

SQL> select object_name, original_name, droptime
  2  from   recyclebin;

SQL>

You can recover tables and optionally rename them from the recycle bin by using the FLASHBACK TABLE command: 

SQL> flashback table history to before drop
  2  [rename to <new name>];