DROP Table in Oracle

DROP Table in Oracle | The DROP command in the Oracle database is used to remove database objects (like the table, index, view e.t.c.) from the database. We use the DROP command to drop a table in the Oracle database.

In all relational databases like Oracle, MySQL, PostgreSQL, and e.t.c, we are allowed to drop only one database object at a time. We can’t drop multiple database objects at a time.

Syntax to use drop command in the Oracle database:-

DROP objectype objectname;

Syntax to drop a table:-

DROP TABLE tablename;

Now let us see the example of creating and dropping a table. First, we will create a table and then drop that table. Create a table test1,

CREATE TABLE test1(num NUMBER(10));
DESC test1;

Output:-

Name        Null?    Type
----------- -------- ----------
NUM                  NUMBER(10)

DROP TABLE test1;
DESC test1;

Output:-

ERROR:
ORA-04043: object test1 does not exist

To drop the table, it must be in your schema or you must have the DROP ANY TABLE system privilege.

The DROP table drops all rows from the table, all table indexes and domain indexes are dropped, as well as any triggers defined on the table, regardless of who created them or whose schema contains them.

Before oracle 10g, when we drop a table then it removes the table permanently. But from oracle 10g Enterprise edition onwards, whenever we drop a table then it goes into the recycle bin. we can found all dropped database objects in the recycle bin. 

Whenever we drop a database object then the recycle bin assigns a different name for that object. The recycle bin of Oracle database stores all dropped object information like original name of database object, recycle bin name of database object, object type (table, index, view, and e.t.c.), and the drop time.  Command to show recycle bin,

SHOW RECYCLEBIN;

Previously we dropped the test1 table in the recycle bin. We can see it inside the recycle bin. Example:- 

SHOW RECYCLEBIN;

Output:-

ORIGINAL NAME  RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
-------------- ------------------------------ ------------ -------------------
TEST1          BIN$2+bfMyIRTEy6d8joZ+EGkg==$0 TABLE        2020-12-24:13:06:49

Get Back Table From Recyclebin

We can get dropped database objects from the recycle bin. Syntax to get back the table from recycle bin:-

FLASHBACK TABLE tablename TO BEFORE DROP;

Example to get back the test1 table from the recycle bin:-

FLASHBACK TABLE test1 TO BEFORE DROP;
DESC test1;

Output:-

 Name     Null?    Type
 -------- -------- ----------
 NUM               NUMBER(10)

Now the table test1 is restored in the database and it is not available in the recycle bin. If we again try to flashback the table test2 then we get an error.

FLASHBACK TABLE test1 TO BEFORE DROP;

Output:-

FLASHBACK TABLE test1 TO BEFORE DROP
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

Drop Table Permanently in Oracle

We can also drop the table permanently in the Oracle database. Syntax to drop the table permanently:-

DROP TABLE tablename PURGE

If we use the PURGE command, then the database does not place the table and its dependent objects into the recycle bin. It also provides enhanced security if we want to prevent sensitive material from appearing in the recycle bin.

Note:- We cannot roll back a DROP TABLE statement with the PURGE clause, nor we can recover the table if we have dropped it with the PURGE clause. 

For example, let us first create a table, and then we will drop it permanently. To check table is dropped permanently or not we will look in the recycle bin.

CREATE TABLE test2(num NUMBER(10));
DROP TABLE test2 PURGE;
SHOW RECYCLEBIN;

Now, this time the table is permanently deleted and it is not stored in the recycle bin. The recycle bin is empty now.

Note:- In the Oracle database, we can’t retrieve drop columns by using the flashback command because the recycle bin doesn’t store the dropped column.

Recycle bin

Oracle 10g Enterprise edition introduced recycle bin which stores dropped table. It works similar to the Windows recycle bin.

If you want to view recycle bin then we are using following syntax:-

show recyclebin;

It is also the same as the window recycle bin that’s why we can also drop tables from the recycle bin by using the “purge” command.

Syntax to drop the single table at a time from recycle bin:- 

PURGE TABLE tablename;

To drop all tables at a time from the recycle bin:-

PURGE RECYCLEBIN;

Practice Example to drop table in Oracle

Create four tables “t1”, “t2”, “t3”, and “t4”. Drop the first three tables “t1”, “t2”, and “t3” which will be stored to recycle bin, and drop the “t4” table permanently. Now, drop table “t1” from recycle bin, and then clean the recyclebin (it will remove all remaining dropped objects from recyclebin).

Solution:-

Create four tables “t1”, “t2”, “t3”, and “t4” with some column.

CREATE TABLE t1(sno NUMBER(10));
CREATE TABLE t2(sno NUMBER(10));
CREATE TABLE t3(sno NUMBER(10));
CREATE TABLE t4(sno NUMBER(10));

Drop “t1”, “t2”, and “t3” which will be stored to the recycle bin,

DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;

Drop the “t4” table permanently,

DROP TABLE t4 PURGE;

Now check the recycle bin,

SHOW RECYCLEBIN;

Output:-

ORIGINAL NAME  RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
-------------- ------------------------------ ------------ -------------------
T1             BIN$4gRO1cCMSRW/DIp8EEoung==$0 TABLE        2020-12-24:13:46:12
T2             BIN$E9EABPGlQDeMGFtmdx7qqg==$0 TABLE        2020-12-24:13:46:16
T3             BIN$B4Q7gTQZQmioPz9ZDU9Ohw==$0 TABLE        2020-12-24:13:46:19

Drop table “t1” from the recycle bin,

PURGE TABLE t1;

Now, again check the recycle bin,

SHOW RECYCLEBIN;

Output:-

ORIGINAL NAME  RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
-------------- ------------------------------ ------------ -------------------
T2             BIN$E9EABPGlQDeMGFtmdx7qqg==$0 TABLE        2020-12-24:13:46:16
T3             BIN$B4Q7gTQZQmioPz9ZDU9Ohw==$0 TABLE        2020-12-24:13:46:19

Remove all dropped database objects from the recycle bin,

PURGE RECYCLEBIN;

Check the recycle bin. Now, recycle bin is empty.

SHOW RECYCLEBIN;

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search