Re: how to clear a schema without dropping the user?

The best way is to query *_objects and dynamically create a script. For instance, the following script will create a drop script and run that script for you:

spool drop_objects.sql
select 'DROP '||object_type||' '||object_name||';' from user_objects;
spool off
@drop_objects

HTH,
Brian

Kenny Yu wrote:
>
> I need to give a schema owner the effect of 'drop user sch_owning_user
> cascade' without him having dba priviledge. That is, I want to keep the
> user's acocunt/password, tablespace etc, but need to clead all tables,
> packages, sequences, etc. Short of getting a list of objects from obj view,
> is there a simple way?
>
> Kenny
Received on Tue Oct 22 2002 - 12:48:50 CDT