Drop in DBMS

Drop in DBMS

 

In this article, we will learn about Drop in DBMS.

Sometimes the user wants to permanently delete the existing table or wants to delete the existing data alone or change the table name all these purposes are served using 3 DDL(data definition language) commands drop, truncate and rename respectively.

Drop in DBMS

Dropping a table using DROP in DBMS

  • Drop command delete the table existence completely i.e drop statement destroys the existing database object of that particular table, index or view
  • After dropping a table if you try to use the table then compiler shows an error as ” table or view does not exist

Syntax

DROP TABLE table_name;

Example

drop table emp;

O/P

Table emp dropped successfully

 

Restoring a dropped table using FLASHBACK Command

Before Oracle 10g whenever we use a drop command the table is permanently deleted but from Oracle 10g onwards we can get back the table from recycle bin by using flashback command.

Syntax

flashback table table_ name to before drop

Example

flashback table emp to before drop

O/P

Table emp restored successfully

The above query gets the  emp table back from the recycle bin.

 

Dropping a table permanently: PURGE Command

If you wish to delete the table permanently and not to allow the table to be restored from the recycle bin then you need to use ‘purge ‘command along with drop command

Example

drop table emp purge

O/P

 Table emp dropped permanently

The above query deletes the table object of emp permanently and we cannot use flashback command after purging

Read more about :