Dropping all the objects for a user

[sh_sonu]

How to drop all the objects for a user? The user object should remain after the dropping of all the objects owned by it.

[pando]

uh? you have to drop all objects and the objects have to remain?

hmm hmm hmm

[Sameer]

Originally posted by sh_sonu
The user object should remain after the dropping of all the objects owned by it.

You mean to say.. you need to cleanup the USER. and DON't want to DROP USER (SCHEMA) (USER=SCHEMA)

1. connect to the USER whos objects you want to drop.
2. spool output of following sql

SELECT 'DROP '||object_type||' '||object_name ||';' FROM user_objects

3. and execute statements generated by spool file.

You may get some errors while dropping objects having dependancies.. that u have to take care..

HTH

Sameer

[Edited by Sameer on 08-26-2002 at 04:30 AM]

[TimHall]

Do you mean the USER must remain after the objects are gone?

One way is to drop the user with the cascade option then recreate it. The other is to write some PL/SQL to identify and drop all the objects doing stuff like:

BEGIN
FOR cur_rec IN (SELECT object_type, object_name FROM user_objects) LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' ' || cur_rec.object_name;
EXCEPTION
WHEN OTHER THEN
NULL;
END;
END LOOP;
END;
/

This may need a bit of work as I've not got a server available to check it at the moment.

Cheers

[shailendra]

execute this script you can also modify it as per your requirment

set heading off
set trimspool on
set feedback off
set echo off
set termout off
set pagesize 1000
declare
cursor cur_table_name is
select distinct table_name
from user_constraints;
V_Tablename varchar2(100);

begin
open cur_table_name;
loop
fetch cur_table_name into V_Tablename;
exit when cur_table_name%notfound;
declare
cursor cur_constraints is
select CONSTRAINT_NAME
from user_constraints where CONSTRAINT_TYPE='R'
and table_name=V_Tablename;
V_Constraint_name varchar2(100);
begin
open cur_constraints;
loop
fetch cur_constraints into V_Constraint_name;
Exit when cur_constraints%notfound;
EXECUTE IMMEDIATE 'alter table ' || V_Tablename ||
' drop constraint '||V_Constraint_name;
end loop;
close cur_constraints;
end;
end loop;
close cur_table_name;
end;
/
declare
cursor cur_alltable_name is
select table_name
from cat where table_type='TABLE';

V_AllTables varchar2(100);

begin
open cur_alltable_name;
loop
fetch cur_alltable_name into V_AllTables;
exit when cur_alltable_name%notfound;
EXECUTE IMMEDIATE 'drop table '|| V_AllTables;
end loop;
close cur_alltable_name;
end;
/

declare
cursor cur_sequence_name is
select table_name
from cat where table_type='SEQUENCE';
V_AllTables varchar2(100);

begin
open cur_sequence_name;
loop
fetch cur_sequence_name into V_AllTables;
exit when cur_sequence_name%notfound;
EXECUTE IMMEDIATE 'drop sequence '|| V_AllTables;
end loop;
close cur_sequence_name;
end;
/
declare
cursor cur_function_name is
select distinct name
from user_source where type='FUNCTION';
V_AllTables varchar2(100);

begin
open cur_function_name;
loop
fetch cur_function_name into V_AllTables;
exit when cur_function_name%notfound;
DBMS_OUTPUT.PUT_LINE(V_AllTables);
EXECUTE IMMEDIATE 'drop function '|| V_AllTables;
end loop;
close cur_function_name;
end;
/
declare
cursor cur_procedure_name is
select distinct name
from user_source where type='PROCEDURE';
V_AllTables varchar2(100);

begin
open cur_procedure_name;
loop
fetch cur_procedure_name into V_AllTables;
exit when cur_procedure_name%notfound;
EXECUTE IMMEDIATE 'drop procedure '|| V_AllTables;
end loop;
close cur_procedure_name;
end;
/
declare
cursor cur_procedure_name is
select distinct name
from user_source where type='PACKAGE BODY';
V_AllTables varchar2(100);

begin
open cur_procedure_name;
loop
fetch cur_procedure_name into V_AllTables;
exit when cur_procedure_name%notfound;
EXECUTE IMMEDIATE 'drop PACKAGE BODY '|| V_AllTables;
end loop;
close cur_procedure_name;
end;
/
declare
cursor cur_procedure_name is
select distinct name
from user_source where type='PACKAGE';
V_AllTables varchar2(100);

begin
open cur_procedure_name;
loop
fetch cur_procedure_name into V_AllTables;
exit when cur_procedure_name%notfound;
EXECUTE IMMEDIATE 'drop PACKAGE '|| V_AllTables;
end loop;
close cur_procedure_name;
end;
/

declare
cursor cur_view is
select view_name
from user_views;
V_AllTables varchar2(100);

begin
open cur_view;
loop
fetch cur_view into V_AllTables;
exit when cur_view%notfound;
EXECUTE IMMEDIATE 'drop view '|| V_AllTables;
end loop;
close cur_view;
end;
/
set heading on
set feedback on
set echo on
set termout on



SHAILENDRA

[grjohnson]

Personally, I'd

DROP USER username CASCADE;

and then re-create the user as it was. Quickest and most simplistic.