If you are into software development, you may have wanted to drop all tables of an Oracle database schema in a single shot. How do you do it? Here’s how I do it with a simple PL/SQL procedure.
begin
for i in (select * from tabs) loop
execute immediate ('drop table ' || i.table_name || ' cascade constraints');
end loop;
end;
/
‘tabs’ is actually an in-built view in Oracle. As explained in the follwing forum post, this PL/SQL block affects only the logged in user’s (schema) tables only.
https://community.oracle.com/message/10359255#10359255
- Comment
- Reblog
-
Subscribe
Subscribed
Already have a WordPress.com account? Log in now.
Shaakunthala's Miniblog 
is it working ? what happen to foreign key relationships?
‘cascade constraints’ part handles that. It will drop all referential integrity constraints during this execution.
If you execute the above PL/SQL block without ‘cascade constraints’, most likely it may fail (depending on your database)
Thanks a lot it helps me a lot
My pleasure. 🙂
SELECT ‘DROP TABLE “‘ || table_name || ‘” CASCADE CONSTRAINTS;’ FROM user_tables;
Source: http://www.jochenhebbrecht.be/site/2010-05-10/database/drop-all-tables-in-oracle-db-scheme
That’s correct. But you have to copy-paste your output so it’s two step work. With PL/SQL (what I wrote above) you’ll drop all in one go.
Anyway – both methods will get the job done right. 🙂
Bruh it didnt work , my catalogue is still full of tables
fuck off not worjing
great gob working