oracle 怎么清空数据表,oracle清空全部表数据

--1.禁用所有表外键约束

declare

cursor c1 is

select t.table_name

,t.constraint_name

from user_constraints t

where t.owner = 'EMS3'

AND T.constraint_type = 'R';

stmt varchar2(4000);

begin

for cc in c1 loop

BEGIN

stmt := 'alter table ' || cc.table_name || ' disable constraint ' ||

cc.constraint_name;

dbms_output.put_line(stmt);

execute immediate stmt;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('error ' || stmt);

end;

end loop;

end;

--2.启用所有表外键约束

declare

cursor c1 is

select t.table_name

,t.constraint_name

from user_constraints t

where t.owner = 'EMS3'

AND T.constraint_type = 'R';

stmt varchar2(4000);

begin

for cc in c1 loop

BEGIN

stmt := 'alter table ' || cc.table_name || ' enable constraint ' ||

cc.constraint_name;

dbms_output.put_line(stmt);

execute immediate stmt;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('error ' || stmt);

end;

end loop;

end;

--3.清空表数据

declare

cursor c1 is

select table_name

from user_tables t

where t.TABLE_NAME not in ('ROLE_FUNC',

'USER_ROLE',

'ROLE_DICT_TYPE',

'DICT_TYPE_APPLY',

'EXAM_TYPE',

'TEMPLATE_RULE_DICT',

'T_DICT_ITEM_APPLY',

'PAY_GATE_TYPE',

'PAY_GATE',

'DICT_TYPE',

'DICT_ITEM',

'DEPT_INFO',

'ACCOUNT',

'FUNC',

'ROLE');

stmt varchar2(4000);

begin

for cc in c1 loop

BEGIN

stmt := 'TRUNCATE table ' || cc.table_name;

dbms_output.put_line(stmt);

execute immediate stmt;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('error ' || stmt);

end;

end loop;

end;