Oracle - drop multiple table in a single query

oracle ddl
vim picture vim · Nov 17, 2014 · Viewed 48.5k times · Source

I have fifty tables in a database, in that I need only six tables.

How can I delete remaining tables by one single query?

Answer

DirkNM picture DirkNM · Nov 17, 2014

You can generate a list of DROP TABLE commands with the query below:

SELECT 'DROP TABLE ' || table_name || ';' FROM user_tables;

After that you remove your six tables you want to keep and execute the other commands. Or you add a WHERE table_name NOT IN (...) clause to the query.

Hope it helps.

Related questions

How do I use CREATE OR REPLACE?

Am I correct in understanding that CREATE OR REPLACE basically means "if the object exists, drop it, then create it either way?" If so, what am I doing wrong? This works: CREATE TABLE foo (id NUMBER, title VARCHAR2(4000) DEFAULT 'Default …

Read More
How do I get column datatype in Oracle with PL-SQL with low privileges?

I have "read only" access to a few tables in an Oracle database. I need to get schema information on some of the columns. I'd like to use something analogous to MS SQL's sp_help. I see the table I'm …

Read More
How to generate entire DDL of an Oracle schema (scriptable)?

Can anyone tell me how I can generate the DDL for all tables, views, indexes, packages, procedures, functions, triggers, types, sequences, synonyms, grants, etc. inside an Oracle schema? Ideally, I would like to copy the rows too but that is …

Read More