Re: Clean up all objects in a tablespace or clean up all objects owned by a user

Here is the script I use to drop my objects... declare
 cursor constraint_cur is
  select table_name, constraint_name
  from dba_constraints
  where constraint_type = 'R'
  and owner = 'GOLDRUSH';

 cursor sequence_cur is
  select sequence_name
  from seq;

 cursor table_cur is
  select object_name
  from user_objects
  where object_type = 'TABLE';

 sequence_val sequence_cur%ROWTYPE;
 constraint_val constraint_cur%ROWTYPE;  table_val table_cur%ROWTYPE;
 v_sql varchar2(1000);

begin
 // Drop all constraints
 for constraint_val in constraint_cur loop   v_sql := 'alter table goldrush.' || constraint_val.table_name || ' drop constraint ' || constraint_val.constraint_name;   execute immediate v_sql;
 end loop;

 // Drop all sequences
 for sequence_val in sequence_cur loop
  v_sql := 'drop sequence ' || sequence_val.sequence_name;   execute immediate v_sql;
 end loop;

 // Drop all tables
 for table_val in table_cur loop
  v_sql := 'drop table ' || table_val.object_name;   execute immediate v_sql;
 end loop;

end;
/
show errors

Yat-Fan wrote:

> Hi all,
>
> When I try to rebuild a DB thru' full database import, I usually drop all
> the tablespaces including their
> contents, rebuild the "empty" tablespaces and then do the importing.
>
> However, the tablespaces are large, this is very time-consuming. Do anyone
> got one clean script
> which can select out all the objects of a user or a tablespace and then
> clean them up.
>
> Using select statements, it is not difficult to do this job in multiple
> phrases, but I am very lazy
> and in case someone already get such a script :-p
>
> Thanks.
>
> Rgds., fan.
> ps. If possible, please also cc: a copy of your reply to me at
> fan.ho_at_alumni.ust.hk
Received on Wed Jun 23 1999 - 02:37:49 CDT