Re: How to DROP ALL TABLEs?

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;
>
>
Received on Wed May 20 1998 - 10:40:08 CDT