declare
cursor t_sor is
select table_name from user_tables;
str varchar2(50);
begin
for v_sor in t_sor loop
str:='drop table '||v_sor.table_name;
execute immediate str;
end loop;
end;
/
cursor t_sor is
select table_name from user_tables;
str varchar2(50);
begin
for v_sor in t_sor loop
str:='drop table '||v_sor.table_name;
execute immediate str;
end loop;
end;
/
1) drop user user1;
create user user1 ...
2) spool c:\deltbl.sql
select 'drop table ' || table_name || ';' from user_tables;
spool off
@c:\deltbl.sql
cursor t_sor is
select table_name from user_tables;
str varchar2(50);
v_CONSTRAINT_NAME VARCHAR2(30);
begin
for v_sor in t_sor loop
select R_CONSTRAINT_NAME into v_CONSTRAINT_NAME from user_constraints where table_name=upper(v_sor.table_name);
if v_CONSTRAINT_NAME is not null then
str='alter table '||v_sor.table_name||' drop CONSTRAINT '||v_CONSTRAINT_NAME;
execute immediate str;
end if;
str:='drop table '||v_sor.table_name;
execute immediate str;
end loop;
end;
/
drop user username cascade;
删除这个用户下所有的对象,包括表、索引、序列等等。 SQL>spool c:\tt.out
SQL>select 'drop table '||table_name||';' from all_tables where owner ='UserName' ;
SQL>spool offSQL>@c:\tt.out