Dropping Tables - NEWBIE QUESTION

[alexmp]

HI ALL,

I have a list of obsolete tables to be dropped. Each of these tables has indices, constaints, triggers, foreign key contraints, stored procedures, etc associated with them. Please let me know the easiest way to drop these tables and at the same time cleaning up all their associated DB objects.

Thanks in advance for your response.

ALEX

 

[SantaMufasa]

Here is a simple script that prepares your tables for deletion into a script file, which you can edit prior to actually dropping the tables, to confirm/specify which tables you want dropped:

set newpage 0
set space 0   
set linesize 80
set pagesize 0
set echo off
set feedback off
set heading off
spool drop_tables.sql

select 'drop table ' || table_name || 'cascade constraints;'
  from user_tables 
/
spool off

Notice that this script does not actually drop the tables. You can edit "drop_tables.sql" to tweak what the script drops, then you execute the script from the SQL*Plus prompt as:

@drop_tables.sql

Dropping the tables automatically drops all of the constraints, indexes, and triggers for each dropped table, as well. Although it does not drop dependent views and stored procedures/functions, you can get an idea of which of those objects were affected by the DROPs by running this script:

col object_name format a30
select object_name, object_type from user_objects
where status <> 'VALID';

Let us know how this all works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[alexmp]

This is really helpful, I will give it a try.
Thanks for your very prompt answer, Mufasa!

 

[SantaMufasa]

You are welcome...but before you (or anyone else) goes to use the code, above, please add a black space in front of "cascade" to read:

select 'drop table ' || table_name || ' cascade constraints;'
  from user_tables...

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[alexmp]

Mufasa, it worked like a charm!

To you and the rest of the members, Have a Happy Thanksgiving.

Thanks again!
Alex