DROP TABLE ORACLE statement

Last updated on September 1st, 2019 at 06:29 pm

-The DROP TABLE oracle command is used to remove a table from the database.

-The dropped table and its data remain no longer available for selection. Dropping a table drops the index and triggers associated with it.

  • Views, synonym are not dropped but they become invalid

-Dropped table can be recovered using FLASHBACK utility, if available in recycle bin. This functionality is available from 10g onwards

-Only the creator of  table can drop the table or   the user with drop any table privilege  can drop the table

Syntax for Drop table oracle

DROP TABLE [TABLE NAME] [PURGE]

The below statement will drop the table and place it into the recycle-bin.

DROP TABLE TEST;

Below statement can be used to restore it from recycle-bin

FLASHBACK TABLE TEST TO BEFORE DROP;

The below statement will drop the table and flush it out from the recycle-bin also.

DROP TABLE TEST PURGE;

drop table oracle

Cascade Constraints

We have to Specify CASCADE CONSTRAINTS to drop all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this clause, and such referential integrity constraints exist, then the database returns an error and does not drop the table.

CREATE TABLE “EMP”
( “EMPNO” NUMBER(6,0),
“ENAME” VARCHAR2(10),
“JOB” VARCHAR2(9),
“MGR” NUMBER(4,0),
“HIREDATE” DATE,
“SAL” NUMBER(7,2),
“COMM” NUMBER(7,2),
“DEPTNO” NUMBER(2,0),
CONSTRAINT “PK_EMP” PRIMARY KEY (“EMPNO”),
CONSTRAINT “FK_DEPTNO” FOREIGN KEY (“DEPTNO”)
REFERENCES “DEPT” (“DEPTNO”) ENABLE
);

CREATE TABLE “DEPT”
( “DEPTNO” NUMBER(2,0),
“DNAME” VARCHAR2(14),
“LOC” VARCHAR2(13),
CONSTRAINT “PK_DEPT” PRIMARY KEY (“DEPTNO”)
;

SQL> desc emp
Name Null? Type
—————————————– ——– ———————–

EMPNO NOT NULL NUMBER(6)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)SQL>

SQL> desc dept
Name Null? Type
—————————————– ——– ———————–

DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SQL>

insert into DEPT values(10, ‘ACCOUNTING’, ‘NEW YORK’);
insert into dept values(20, ‘RESEARCH’, ‘DALLAS’);
insert into dept values(30, ‘RESEARCH’, ‘DELHI’);
insert into dept values(40, ‘RESEARCH’, ‘MUMBAI’);

insert into emp values( 7698, ‘BLAKE’, ‘MANAGER’, 7839, to_date(‘1-5-2007′,’dd-mm-yyyy’), 2850, null, 10 );
insert into emp values( 7782, ‘CLARK’, ‘MANAGER’, 7839, to_date(‘9-6-2008′,’dd-mm-yyyy’), 2450, null, 10 );
insert into emp values( 7788, ‘SCOTT’, ‘ANALYST’, 7566, to_date(‘9-6-2012′,’dd-mm-yyyy’), 3000, null, 20 );

insert into emp values( 7789, ‘TPM’, ‘ANALYST’, 7566, to_date(‘9-6-2017′,’dd-mm-yyyy’), 3000, null, null );
insert into emp values( 7560, ‘T1OM’, ‘ANALYST’, 7567, to_date(‘9-7-2017′,’dd-mm-yyyy’), 4000, null, 20 );
insert into emp values( 7790, ‘TOM’, ‘ANALYST’, 7567, to_date(‘9-7-2017′,’dd-mm-yyyy’), 4000, null, null );

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7698 BLAKE MANAGER 7839 01-MAY-07 2850 10

7782 CLARK MANAGER 7839 09-JUN-08 2450 10

7788 SCOTT ANALYST 7566 09-JUN-12 3000 20

7789 TPM ANALYST 7566 09-JUN-17 3000

7790 TOM ANALYST 7567 09-JUL-17 4000

4534 xyz 1000 20

4576 abc 1000

7560 T1OM ANALYST 7567 09-JUL-17 4000 20

SQL> drop table dept;
drop table dept
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL>
SQL> drop table dept cascade constraints;

Table dropped.

How to drop multiple tables in oracle

There might be requirement to drop multiple tables in a single step

begin
execute immediate ‘drop table t_name_1’;
execute immediate ‘drop table t_name_2’;
end;

How to drop all the tables in the schema

spool drop_table_schema.sql

select ‘drop table ‘||table_name||’;’  from user_tables

spool off

@drop_table_schema.sql

Drop table if exists in Oracle

We sometimes want to check the table existence to avoid throwing error in the code. In mysql, sql server, we have exists clause while using drop statement but there is no such clause in oracle.

We can use use PLSQL anonymous block to achieve the same thing

DECLARE
count INT;

BEGIN
SELECT Count(*)
INTO count
FROM dba_tables
WHERE owner = ‘<schema name>’
AND table_name = ‘<table name>’;

IF count = 1 THEN
EXECUTE IMMEDIATE ‘drop table test’;
END IF;
END;

Hope you like the content on drop table statement in Oracle.Please do let me know the feedback

Recommended Reading

Oracle documentation

SQL FAQ

Related Articles

Update statement in oracle

How to Truncate TABLE in Oracle

Delete from table statement in Oracle

How to Alter Table in Oracle

Useful information about INSERT statement in Oracle

Oracle Create table Syntax,Tip and Examples

Leave a Reply