Generating DROP TABLES for ORACLE - One Statement

I forget it over and over again, now its here archived :-). The following statement generates DROP TABLE statements which really cleanup the Oracle database:

select 'drop table '||table_name||' CASCADE CONSTRAINTS;' from USER_TABLES

It is very useful for development (JPA will generate the tables for you again), and production (speeds up the performance) :-).

Nice thing: USER_TABLES and Oracle's dictionary is conceptually similar to reflection in Java.


Cloudy Jakarta EE and MicroProfile: Microservices, Clouds and Beyond Jakarta EE / MicroProfile airhacks workshops at MUC airport, Winter Edition

airhacks.fm the podcast:

Stay in touch: airhacks.news.

Comments:

Well performance decreases a little bit while doing this as user SYSTEM - so take care ;-)

Posted by Dirk on December 16, 2008 at 02:51 PM CET #

In MySQL, this is much easier:

DROP DATABASE xyz; CREATE DATABASE xyz

Posted by Michael Kuhlmann on December 18, 2008 at 12:06 PM CET #

Manual trackback: same query for dropping sequences.
Cheers :-).

Posted by Baptiste on December 22, 2008 at 10:38 AM CET #

Very helpfull :)

Posted by 124.124.224.75 on February 23, 2012 at 08:22 AM CET #

Post a Comment:
  • HTML Syntax: NOT allowed
Online Workshops
realworldpatterns.com
...the last 150 posts
...the last 10 comments
License