Re: How to DROP ALL TABLEs?

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 Fri May 15 1998 - 07:51:21 CDT