Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Drop all the objects inside a tablespace.

Re: Drop all the objects inside a tablespace.

From: Anatoly Moskovsky <avm_at_trais.com.ua>
Date: Wed, 22 Aug 2001 20:09:01 +0300
Message-ID: <TRS10205C52B@trais.com.ua>


Hi!

 P> I want to drop all the tables inside a tablespace without dropping
 P> the tablespace.So that after dropping the old tables,I can create new
 P> tables inside that tablespace. Is there a way?

Using SQLPlus:

set echo off termout off feedback off heading off connect system_at_test
spool drop.sql
select 'drop table "'||owner||'"."'||table_name||'" cascade constraints;'   from dba_tables
 where tablespace_name in ('USER_TS');
spool off
set termout on feedback on heading on
prompt All tables will be dropped!!! Press Ctrl-C to cancel. pause
set echo on
-- once again
connect system_at_test
@drop

Bye




Anatoly Moskovsky, Oracle Developer avm_at_sqlbatch.com [SQL Batch/Oracle, DBA, reverse engineering, scripting extensions]: http://sqlbatch.com/sb/ Received on Wed Aug 22 2001 - 12:09:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US