Dropping a Table- Tables and Constraints

If you want to remove an object, such as a table, from a user, use the DROP TABLE statement. This example drops a table named INV:

SQL> drop table inv;

You should see the following confirmation:

Table dropped.

If you attempt to drop a parent table that has either a primary key or unique key referenced as a foreign key in a child table, you see an error such as this:

ORA-02449: unique/primary keys in table referenced by foreign keys

You need to either drop the referenced foreign key constraint(s) or use the CASCADE CONSTRAINTS option when dropping the parent table:

SQL> drop table inv cascade constraints;

You must be the owner of the table or have the DROP ANY TABLE system privilege to drop a table. If you have the DROP ANY TABLE privilege, you can drop a table in a different schema by prepending the schema name to the table name:

SQL> drop table inv_mgmt.inv;

If you do not prepend the table name to a username, Oracle assumes you are dropping a table in your current schema.

Tip If flashback query or flashback database is enabled, keep in mind that you can flash back a table to before the drop for an accidentally dropped table.

Undropping a Table

Suppose you accidentally drop a table, and you want to restore it. First, verify that the table you want to restore is in the recycle bin:

SQL> show recyclebin;

Here is some sample output:

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
————- ————— ———– —————-INV BIN$0F27WtJGbXngQ4TQTwq5Hw==$0 TABLE 2022-12-08:12:56:45

Next, use the FLASHBACK TABLE…TO BEFORE DROP statement to recover the dropped table:

SQL> flashback table inv to before drop;

Note You cannot use the FLASHBACK TABLE…TO BEFORE DROP statement for a table created in the SYSTEM tablespace.

When you issue a DROP TABLE statement (without PURGE), the table is actually renamed (to a name that starts with BIN$) and placed in the recycle bin. The recycle bin is a mechanism that allows you to view some of the metadata associated with a dropped object. You can view complete metadata regarding renamed objects by querying DBA_ SEGMENTS:

SQL> select owner ,segment_name

,segment_type ,tablespace_name from dba_segments
where segment_name like ‘BIN$%’;

The FLASHBACK TABLE statement simply renames the table to its original name. By default, the RECYCLEBIN feature is enabled. You can change the default by setting the RECYCLEBIN initialization parameter to OFF.
I recommend that you not disable the RECYCLEBIN feature. It is safer to leave this feature enabled and purge the RECYCLEBIN to remove objects that you want permanently deleted. This means that the space associated with a dropped table is not released until you purge your RECYCLEBIN. If you want to purge the entire contents of the currently connected user’s recycle bin, use the PURGE RECYCLEBIN statement:

SQL> purge recyclebin;

If you want to purge the recycle bin for all users in the database, then do the following, as a SYSDBA-privileged user or user with the PURGE DBA_RECYCLEBIN role:

SQL> purge dba_recyclebin;

If you want to bypass the RECYCLEBIN feature and permanently drop a table, use the PURGE option of the DROP TABLE statement:

SQL> drop table inv purge;

You cannot use the FLASHBACK TABLE statement to retrieve a table dropped with the PURGE option. All the space used by the table is released, and any associated indexes and triggers are also dropped.