删除表空间上所有的表

conn system/managerset heading off feedback off pagesize 200 lineisze 200 trimspool on;spool drop_tabsp_tabs.tmp;   select 'drop  table '||table_name ||' ;'   from    dba_tables   where tablespace_name  = upper('specified_tabspace');spool off;@drop_tabsp_tab.tmp;   -- for there may  have constraints (foreign key) ,   --   run the script more times  or drop the constraints at first

drop this tablespace
drop tablespace XXX including contents; 

 

 

declare
vsql varchar2(200);
cursor c1 is
select 'drop table '||table_name||' cascade constraints' v_name
from user_tables where tablespace_name='AFC_NEW';

BEGIN
for i in c1 loop
vsql:=i.v_name;
execute immediate vsql;
end loop;
end;
/

SELECT 'DROP TABLE '||TABLE_NAME||';' FROM user_tables WHERE TABLESPACE_NAME='';

加上CASCADE CONSTRAINTS 是不是好些？删掉用户也方便

用pl/sql developer 更方便