Thursday, July 24, 2008

Drop All Tables in a Single Query

Recently, I needed to find a single, simple PL/SQL command to drop all the tables from an Oracle database without explicitly naming each table and without dropping the entire schema. Application development has just started, and I need to easily rename tables and other database objects, so the simplest solution at this early stage is often just to wipe and recreate the entire schema.
One possible solution would be to login as SYS and drop the schema itself, but I need to configure a single login for any database access, i.e., the user whose database objects will be deleted.

I did not find my specific solution online, but I owe thanks to alternative approaches in some other posts.

First, make sure that you connect to Oracle as the correct user, i.e., the one whose tables will be dropped.

This command will drop all the user's tables:
BEGIN 
  FOR i IN (SELECT table_name FROM user_tables) 
    LOOP 
      EXECUTE IMMEDIATE('DROP TABLE ' || user || '.' || i.table_name || ' CASCADE CONSTRAINTS'); 
    END LOOP
END;

Similar commands can be used to drop all triggers, sequences, etc.  For example,
BEGIN 
  FOR i IN (SELECT trigger_name FROM user_triggers) 
    LOOP 
      EXECUTE IMMEDIATE('DROP TRIGGER ' || user || '.' || i.trigger_name); 
    END LOOP
END;

               and

BEGIN 
  FOR i IN (SELECT sequence_name FROM user_sequences) 
    LOOP 
      EXECUTE IMMEDIATE('DROP SEQUENCE ' || user || '.' || i.sequence_name); 
    END LOOP
END;

NB: if you drop all tables before triggers, your triggers will be renamed with special characters and the command might not work; make sure to drop triggers before tables.

29 comments:

Ariel Valentin said...

Where was this post when I needed it a year ago? :)

Tim Myer said...

Thanks for the comment, and I totally hear you. It seems like such an obvious thing to know, and it should come up right away in any web search, but it took me more than a minute to find the answer, so I figured it was worth posting. Hopefully this will save someone some time, even if it is too late to help you....
-----Tim-----

Ariel Valentin said...

I should mention that this is what I did...

declare
cursor dropObjectsCusor is
select 'drop '|| object_type || ' ' || object_name as sqlDropStmt
from user_objects
where object_type <> 'TABLE' and object_type <> 'INDEX'
order by object_type;

cursor dropTablesCusor is
select 'truncate table ' || object_name as sqlTruncTbl,
'drop table ' || object_name || ' cascade constraints' as sqlDropTbl
from user_objects
where object_type = 'TABLE'
order by object_type;


begin

for ob in dropObjectsCusor
loop
execute immediate ob.sqlDropStmt;
end loop;

for ob in dropTablesCusor
loop
execute immediate ob.sqlTruncTbl;
execute immediate ob.sqlDropTbl;
end loop;

Tim Myer said...

Great contribution. I may end up using some form of your script in the future. Thanks!
-----Tim------

Emperor361 said...

Awesome script! Just what I needed! Thanks, you saved me a great deal of time...

Tim Myer said...

Hi Emperor361,

Thanks for the feedback! Let me know if you come up with any improvements.

---Tim---

Svetlana said...

Thank you, it works great. I was lucky to find this post
Svetlana

Tim Myer said...

Hi Svetlana,

Thanks for the feedback. If you run into any issues or have ideas for improvements, let me know!

---Tim---

Ranganathan said...

Thank you ! It worked great ! I will visit this blog often.

Tim Myer said...

Hi Ranganathan,

I appreciate the feedback and am happy that it worked for you!

---Tim---

Yogesh said...

Thanks.. It saved a lot of my time.

Teekay Lu said...

You could try this logging in as sysdba (just one log in will do the job)

For all tables
==============

SELECT 'DROP TABLE ' || owner || '.' || table_name || ' CASCADE CONSTRAINTS;'
FROM all_tables
/


For all triggers
================
SELECT 'DROP TRIGGER ' || owner || '.' || trigger_name || ' ;'
FROM all_triggers
/



For all sequences
=================
SELECT 'DROP SEQUENCE ' || sequence_owner || '.' || sequence_name || ' ;'
FROM all_sequences
/

Custom for specific owners/users (but make sure you log in as SYSDBA) Also substitute the sql above if tables, triggers or sequences and include/exclude users/owners as needed
====================================================================


Example for sequence

SELECT 'DROP SEQUENCE ' || sequence_owner || '.' || sequence_name || ';'
FROM all_sequences
where sequence_owner IN (
'SCOTT',
'SH',
'SSS',
'TEST1',
.
.
.
.
.
'WHD'
)

order by sequence_owner

Suzanne said...

Really useful, thanks!

Tim Myer said...

Thanks for the feedback, Suzanne! Glad you found the post useful.

pradeep in love said...

ya solution is quite brilliant that we can delete all the tables from the database at a time...... but how we can delete only 3tables (dept,emp,salgrade) from the database when there are 15 different tables from the same database.

thanks in advance

regards,
thanmathpradeep@gmail.com

Tim Myer said...

Hi Pradeep,

If I understand your question correctly, I think I would modify the inner query for finding all the schema tables to "SELECT table_name FROM user_tables WHERE table_name IN ('dept','emp','salgrade')".

Let me know if that is what you were looking for.

Thanks,
---Tim---

pradeep in love said...

hello Tim Myer::

my question is that ........... in my database i have 5tables i.e.,(emp,dept,salgrade,bonus,sales).

In that 5tables am looking to delete only three table i.e,(emp,dept,bonus) at a time and the remaining tables should be in the database itself.

This is my small requirement.thanks in advance

regards,
thanmathpradeep@gmail.com

Tim Myer said...

Hi Pradeep,

It appears that I did understand your question correctly. Did you attempt what I suggested?

---Tim---

pradeep in love said...

HELLO Tim Myer ,

I HAD TRIED UR COMMAND IT WAS EXCELLENT AND U HAD CREATED THE ABOVE MENTIONED COMMAND TO DROP ALL THE TABLES FROM THE DATABASE AT A TIME.

BUT AM ASKING IN A DIFFERENT WAY,LET ME EXPLAIN WITH ONE EXAMPLE ,,

LET AS ASSUME THE FOLLOWING THINGS...

(1) ASSUME THAT THE NAME OF THE DATABASE IS "PRADEEP"

(2) ASSUME THAT THERE ARE 8 TABLES IN THIS DATABASE.

(3) HERE I WOULD LIKE TO DROP ONLY FOUR TABLES AT A TIME AND I WOULD LIKE TO KEEP THE REMAINING TABLES.

SELECT *FROM TAB;

TABLE_NAME TABLE_TYPE

DEPT TABLE
EMP TABLE
SALGRADE TABLE
BONUS TABLE
INTEREST TABLE
STUDENT_INFO TABLE
BANK_INFO TABLE
ECONOMY TABLE

HERE FROM THE ABOVE TABLES , I WOULD LIKE TO DROP FOUR TABLES I.E.,DEPT,EMP,SALGRADE,BONUS.

WE CAN DROP THOSE TABLES INDIVIDUALLY AS,

SQL>DROP TABLE DEPT;
TABLE DROPPED.

SQL>DROP TABLE EMP;
TABLE DROPPED.

SQL>DROP TABLE SALGRADE;
TABLE DROPPED.

SQL>DROP TABLE BONUS;
TABLE DROPPED.

BUT I WON'T LIKE TO DO THESE OPERATIONS SEPARATELY. I NEEDED A SINGLE,SIMPLE PL/SQL COMMAND TO DROP THESE FOUR TABLES FROM AN ORACLE DATABASE WITH NAMING EACH TABLE AND WITHOUT DROPPING THE ENTIRE SCHEMA.

HOPE YOU UNDERSTAND MY WORDS AND EXPECTATIONS .........
THANKS AGAIN


REGARDS,
THANMATHPRADEEP@GMAIL.COM

Tim Myer said...

Hi Pradeep,

Yes, I understand what you are asking. I will ask you again, did you try what I suggested above?

BEGIN
FOR i IN (SELECT table_name FROM user_tables
WHERE table_name IN ('DEPT','EMP','SALGRADE','BONUS'))
LOOP
EXECUTE IMMEDIATE('DROP TABLE ' || user || '.' || i.table_name || ' CASCADE CONSTRAINTS');
END LOOP;
END;

You may need to uppercase the table name.

---Tim---

pradeep in love said...

thank you sir, my doubt was clarified.

regards,
thanmathpradeep@gmail.com

pradeep in love said...

hello sir,
I have another requirement ...........

let us consider the following table.

select *from pradeep;

ename sal deptno
----- ----- --------
pradeep 5000 10
prasad 2000 20
tulasi 3530 30
venkat 4400 40
sachin 1000 50
dravid 5000 60
sewag 10000 70

( sir,this is the related table)

my requirement is that

i want the get the out put as mentioned below,

ename sal deptno
----- ----- --------

prasad 2000 20
tulasi 3530 30
venkat 4400 40

my condition is that, the above mentioned data should be retrieved from the base table only when deptno is present in the sal

i.e., sal=(20)00 where deptno=20;
sal=35(30) where deptno=30;
sal=4(40)0 where deptno=40;

hope you understood my requirement..

thanks in advance....


regards,
thanmathpradeep@gmail.com

pradeep in love said...

YA I GOT IS SUCCESSFULLY ,

I SOLVED IT BY D FOLLOWING QUERY

SELECT A.ENAME,B.SAL,A.COMM.B.DEPTNO FROM PRADEEP A,PRADEEP B WHERE A.DEPTNO=B.DEPTNO AND INSTR(A.SAL,B.DEPTNO)>0;

REGARDS,
THANMATHPRADEEP@GMAIL.COM

Saeed Adil said...

hi pradeep,

I have a backup 10g database server I have daily full export dmp file of my active database server. can you guide me how I can update my backup database server from full export file on daily basis. I want to import file on daily basis on my backup server to upto date my backup database server.

Thanks in advance

Uzair from Pakistan said...

Muhammad Uzair (ORACLE APEX Developer)

Thanks a lot for sharing this nice information to save the time.

Uzair from Pakistan said...

Thanks for sharing this helpful information to save my time.

Jason D Miles said...

A really helpful bit of code for someone new to Oracle.

Thanks

Shabbir Babjee said...

Works like a charm !!!

DD said...

-- Why is this procedure not giving the output I intended-- delete all views
--It compiles fine and tests fine on pl/sql developer, but never deletes the views
-- I used your code and it worked fine

CREATE OR REPLACE PROCEDURE delete_views IS

v_qtext VARCHAR2(100);
rec_views VARCHAR2(40);
old_views VARCHAR2(40);

CURSOR rec_views IS(
SELECT ut.view_name view_name
FROM user_views ut
ORDER BY view_name);
BEGIN

OPEN rec_views;
FETCH rec_views
INTO old_views;
IF rec_views%NOTFOUND
THEN
v_qtext := 'DROP VIEW ' || old_views;
EXECUTE IMMEDIATE v_qtext;
END IF;
CLOSE old_views;
END delete_views;