Re: deleting all database objects belonging to a user

Depending on your database size, and the number/type of objects, you may not be able to use the 'drop user cascade' command (run out of temp tablespace I belive). When that happens, you can write some SQL from SQL to query the objects and build a a SQL file for you that has all the appropriate drop statements. Then you just execute that file. A short example might be:

spool drop_em.sql

select 'drop ' || object_type || object_name || ';' from user_objects;

spool off

spool drop_em.lis

start drop_em.sql

spool off

Execute the above when connected as the user you wish to clean out. Then drop the user using the drop user command.

Some issues with the above: If you done use some sort of order by clause, you will likely get many messages saying object does not exist. This would be common when the drop statement for the table occured before the indexes, since the indexes will get dropped automatically when the table is dropped. Also, if your objects take advantage of constraints, such as foreign key references, or if you have views referring to views (not recommended) you will have some dependancy problems needing resolution. In the first case, you will need to include code to drop constraints first, you could just add the select statement before the one above, after the spool drop_em.sql line. For the second, you could just run the script a second time to get the remaining objects, or build a really wicked script that alanyzes dependencies first. Best bet is to just start witht the simple version and add the other complexity as needed.

Hope that's enough or more than you needed...

Robert Kuropkat
Avalon Software
rkuropka_at_avalon.COM
rjkurop_at_primnet.com Received on Mon Nov 13 1995 - 00:00:00 CET