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 :

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription