Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Drop all the objects inside a tablespace.
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