In this post, we will discuss How to drop table in oracle,Drop table if exists in Oracle,How to drop multiple tables in oracle,drop table cascade Constraints, ORA-02449
How to drop table in oracle
- 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.
- Oracle Views, synonyms are not dropped but they become invalid
- Dropped table can be recovered using the FLASHBACK utility, if available in recycle bin. This functionality is available from 10g onwards
- Only the creator of the 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;
The 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;
Or if you are dropping the table in other schema, then
DROP TABLE [SCHEMA NAME].[TABLE NAME] [PURGE];
The below statement will drop the table and place it into the recycle bin.
DROP TABLE SCOTT.TEST;
The below statement can be used to restore it from recycle bin
FLASHBACK TABLE SCOTT.TEST TO BEFORE DROP;
The below statement will drop the table and flush it out from the recycle bin also.
DROP TABLE SCOTT.TEST PURGE;
drop table 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.
Let’s check this with an example
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 a 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/oracle drop table if exists
We sometimes want to check the table’s existence to avoid throwing errors 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 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 "schema name"."table name"';
END IF;
END; /
or if user in dropping table in his own schema
DECLARE count INT; BEGIN SELECT Count(*) INTO count FROM user_tables WHERE table_name = '<table name>'; IF count = 1 THEN EXECUTE IMMEDIATE 'drop table "table name"'; END IF; END; /
Hope you like the content on the drop table statement in Oracle. Please do let me know the feedback
Recommended Reading
Related Articles
How to update table in oracle: An update statement in oracle is used to modify the existing rows in the oracle table. The update can be executed in multiple ways
How to Truncate TABLE in Oracle: Truncate TABLE in Oracle is faster than deleting from the table in oracle. It is a DDL statement and it does not fire the on delete triggers
how to delete a row in oracle: Delete from the table in oracle is used to delete the rows. DELETE rows can be done using EXISTS/NOT EXISTS clause, table based on a subquery, cascade
Alter Table in Oracle
INSERT statement in Oracle
create table syntax in oracle
Primary Key in Oracle