Re: Deleting User Tables

Mark,

you have several options here. First, if all of the user's tables are isolated to their own tablespace, the quickest way would be to drop the tablespace and recreate it. You can do that with

    drop tablespace <user_TS> including contents;

If that is not the case, you could write dynamic sql to do it for you. Something like this should work:

select 'truncate table '||owner||'.'||table_name||';'   from dba_tables
  where owner = '<user>';
spool drop1.sql
/
spool off

select 'drop table '||owner||'.'||table_name||';'   from dba_tables
  where owner = '<user>';
spool drop2.sql
/
spool off

You will then need to edit the drop1.sql and drop2.sql files and remove headings and feedback and other extraneous text. You can then run them in order, drop1.sql to truncate all of that user's tables, then drop2.sql to drop the empty tables.

I always export the user before doing this, just in case.

There is also a DESTROY=Y flag to imp, I believe this will destroy all the contents of the tables before loading them, but I haven't used it that often.

HTH,
Roy

In article <37738368.F2A7BC44_at_alpinesoftware.com>,   Mark Schenkel <mschenkel_at_alpinesoftware.com> wrote:
> What is the best way to delete all of a user's tables? Right now I
have
> been dropping the user and then recreating the user.
>
> I want to drop all of the user's tables because I am using IMP80 to
> overwrite all of the tables. Is there a way to have IMP80 overwrite
all
> of the existing tables?
>
> Thanks, Mark.
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jun 25 1999 - 12:23:02 CDT