SQL> SPOOL clear.SQLSQL>select 'alter table ' || t.table_name || ' disable constraint ' ||t.constraint_name||';' from user_constraints t where t.constraint_type = 'R';SQL> SELECT 'TRUNCATE TABLE '||TABLE_NAME||';' FROM USER_TABLES;SQL>select 'alter table ' || t.table_name || ' enable constraint ' ||t.constraint_name||';' from user_constraints t where t.constraint_type = 'R';SQL> clear.SQL有外键不能truncate
create or replace procedure truncatetables is l_row user_all_tables%rowtype; sqlstr varchar2(1000); cursor tablesa is select * from user_all_tables; cc number; begin open tablesa; loop fetch tablesa into l_row; sqlstr := 'truncate table '||l_row.table_name; -- dbms_output.put_line(sqlstr); begin execute immediate sqlstr; exception when others then dbms_output.put_line(sqlstr||'执行错误'); end; dbms_output.put_line(l_row.table_name||'记录条数:'||cc); select 0 into cc from dual; exit when tablesa%notfound; end loop; close tablesa; end;先建立这个存储过程 然后运行 begin truncatetables; end; 就可以清空表内数据了
从user_table中得到表名...再一个个truncate...
想一起删恐怕做不到.
先取得删除数据语句然后执行就行了
SQL> SPOOL D:\TRUN.SQLSQL> SELECT 'TRUNCATE TABLE '||TABLE_NAME FROM USER_TABLES;SQL> @D:\TRUN.SQL
SQL> SPOOL clear.SQLSQL>select 'alter table ' || t.table_name || ' disable constraint ' ||t.constraint_name||';'
from user_constraints t
where t.constraint_type = 'R';SQL> SELECT 'TRUNCATE TABLE '||TABLE_NAME||';' FROM USER_TABLES;SQL>select 'alter table ' || t.table_name || ' enable constraint ' ||t.constraint_name||';'
from user_constraints t
where t.constraint_type = 'R';SQL> clear.SQL有外键不能truncate
l_row user_all_tables%rowtype;
sqlstr varchar2(1000);
cursor tablesa is select * from user_all_tables;
cc number;
begin
open tablesa;
loop
fetch tablesa into l_row;
sqlstr := 'truncate table '||l_row.table_name;
-- dbms_output.put_line(sqlstr);
begin
execute immediate sqlstr;
exception
when others then
dbms_output.put_line(sqlstr||'执行错误');
end;
dbms_output.put_line(l_row.table_name||'记录条数:'||cc);
select 0 into cc from dual;
exit when tablesa%notfound;
end loop;
close tablesa;
end;先建立这个存储过程 然后运行
begin
truncatetables;
end;
就可以清空表内数据了