Re: How to DROP ALL TABLEs?

"Slava Krivonos" <kvv_at_cmtk.net> wrote:

An easier way to do the same thing is dropping all of the tables with set pages 0
set head off
set feedback off
spool droptables.out
select 'drop table '||rtrim(lower(table_name))' cascade constraints;' from user_tables;
spool off
@droptables.out

It will drop all of the tables regardless off the constraints. The drop the other objects using the examples below. It is much more efficient than runing the script over repeatedly.

I like to use seperate scripts to drop each object type and run them all from one master script file.

>Some remarks.
>If you have some parent-child constraints you will have to run the script
>several times becouse you cannot drop parent if child exists.
>Drop do delete then drop table. Delete involve log-files. Use truncate
>table before drop. You'll save much time.
>
>Regards,
>
>Slava
>
>Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in article
><01bd7fff$e06056c0$0300a8c0_at_WORKSTATION>...
>>
>> There is a possible disadvantage with a script like this:
>>
>> If there are a lot of objects in each tablespace then
>> the number of free extents generated can become
>> very large. This results in a large cluster of fet$ entries
>> which (due to the code Oracle uses to find and allocate
>> free extents) can lead to a significant time-cost when
>> creating new objects.
>>
>> If necessary a small modification to the script could
>> be made to produce an output which does:
>> drop single object
>> coalesce tablespace for physical object
>> drop single object
>> coalesce ....
>>
>>
>> > Here's a SQL*Plus script I wrote to drop the tables, views, and
>> > procedures for a schema. Modify it to suit your needs.
>> >
>> > -- drop_em.sql
>> > -- Chris Hamilton
>> > -- 16-Mar-1998
>> > -- SQL*Plus script to drop all RMS tables, in
>> > -- preparation for re-importing.
>> >
>> > set pagesize 0;
>> > set linesize 79;
>> > set trimspool on;
>> > set feedback off;
>> > set echo off;
>> >
>> > spool drop_em.tmp;
>> >
>> > select 'drop ' || lower(object_type) || ' ' || object_name || ';'
>>
>> > txt
>> > from dba_objects
>> > where object_type in ('TABLE', 'VIEW', 'PROCEDURE')
>> > and owner = upper('&owner')
>> > order by decode(object_type, 'VIEW', 1, 'PROCEDURE', 2, 'TABLE', 3,
>>
>> > 4),
>> > object_name;
>> >
>> > spool off;
>> >
>> > set feedback on;
>> >
>> > set echo on;
>> >
>> > @drop_em.tmp
>> >
>> > alter tablespace rms_data coalesce;
>> > alter tablespace rms_index coalesce;
>> > alter tablespace rms_library coalesce;
>>
>>

• ========= ========= ========= ========= Noah Monsey noah_at_goodnet.com
• ========= ========= ========= ========= The only dumb question is the one that you don't ask.
• ========= ========= ========= ========= Oracle Master Database Administration, April 1992 Oracle Master Application Development, April 1992
• ========= ========= ========= =========