dropping a schema in oracle, keeping user

[dave_p]

can anyone tell me, or point me to information on how to delete a schema and all of its objects without doing a drop user cascade? i want to keep the user and the tns entries and database links, i just want to get rid of all the tables, packages, indexes, sequences, triggers and so on. i want a user, remote connection to user but a clean schema. this is for a dev box i am trying to populate with a portion of a production schema, but will have to redo the process a few times before i get all the kinks worked out.

thanks to all.

edit: resolved mostly
database links were created from sys and i generated a script to recreate the user so i just did a drop user cascade. but while i have anybodies attention, is there a good way to copy a portion of a database while keeping referential integrity and copying all tables with no FK constraints? i have tried with TOADs data subset wizard but am not getting all my data for some reason.

 

[sitheris]

you could do something like this from SQLplus:

set pages 10000
set lines 160

spool drop_obj.sql

select 'DROP TABLE ' || table_name || ' purge;'
from user_tables;

spool off

@drop_obj.sql

Use a similar method for dropping packages and any other objects. When you drop a table it should get rid of PKs, FKs, and indexes for you.