Drop All Tables in a schema (Oracle)

person Jason Huangfolder_openCode, Oraclelocal_offer, , access_time October 11, 2009

This script would create a drop table script for all the tables

-- Drop All Tables in a schema:
-- Drop_Tables.sql - Creates script to drop all tables and views from schema
--  Use: script reads table list from schema and spools to a new script
--       containing statements used to drop each table and view.
--       That script is called and drops the schema tables and views on the list.
Spool drop_schema_tables.sql
select 'Drop ' || object_type || ' ' || object_name || ';' from user_objects
where object_type in ('TABLE','VIEW')
ORDER BY object_type DESC, object_name;
spool off
-- @drop_schema_tables.sql


  1. I never thought of it this way. It’s a lot easier than I thought.

    Thank you. Your tip is awesome! 🙂

  2. Hi How to write simple coding in drop table with oracle

  3. drop table [table name];

  4. srikanth gundoju

    this made my job so easy.. good one.. thanks

  5. very good

  6. awesome….works absolutely fine

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>