Oracle快速清除表数据
时间:2022-03-14 04:27
select table_name, constraint_name from user_constraints where constraint_type = 'R';
2. 禁用所有外键约束:
select 'alter table ' || table_name || ' dsable constraint ' || constraint_name ||';' from user_constraints where constraint_type = 'R';
生成如下sql语句(表名视具体情况而定):
alter table RS_ZGSHBX disable constraint FK_RS_ZGSHB_REFERENCE_RY_JBXX; alter table WS_JKQK disable constraint FK_WS_JKQK_REFERENCE_RY_JBXX; alter table WS_YBJZQK disable constraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;
3. 启用所有外键约束:
select 'alter table ' || table_name || ' enable constraint ' || constraint_name ||';' from user_constraints where constraint_type = 'R';
生成如下sql语句(表名视具体情况而定):
alter table RS_ZGSHBX enable constraint FK_RS_ZGSHB_REFERENCE_RY_JBXX; alter table WS_JKQK enable constraint FK_WS_JKQK_REFERENCE_RY_JBXX; alter table WS_YBJZQK enable constraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;
4. 删除所有外键约束:
select 'alter table ' || table_name || ' drop constraint '|| constraint_name || ';' from user_constraints where constraint_type = 'R';
生成如下sql语句(表名视具体情况而定):
alter table RS_ZGSHBX drop constraint FK_RS_ZGSHB_REFERENCE_RY_JBXX; alter table WS_JKQK drop constraint FK_WS_JKQK_REFERENCE_RY_JBXX; alter table WS_YBJZQK drop constraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;
四:重置sequence初始值
Oracle中、主键常常使用的是指定的sequence来实现自动增长、本身没有什么问题、但是当我们使用truncate清除千万级别的数据之后、sequence每次增长1的话、删除后下次再插入数据时、其id已经是千万以上了、这一般不是我们想要的、Oracle中一般将自增sequence重置为初始1时,都是删除再重建,这种方式有很多弊端,依赖它的函数和存储过程将失效,需要重新编译。不过还有种巧妙的方式,不用删除,利用步长参数,先查出sequence的nextval,记住,把递增改为负的这个值(反过来走),然后再改回来。
假设需要修改的序列名:seq_name:
create or replace procedureseq_reset(v_seqname varchar2) as n number(10); tsqlvarchar2(100); begin execute immediate'select '||v_seqname||'.nextval from dual' into n; n:=-(n-1); tsql:='altersequence '||v_seqname||' increment by '|| n; execute immediatetsql; execute immediate'select '||v_seqname||'.nextval from dual' into n; tsql:='altersequence '||v_seqname||' increment by 1'; execute immediatetsql; end seq_reset;
调用方式:
declare seq_namevarchar2(100); begin seq_name :='seq_ry_jbxx'; seq_reset(seq_name); end;
五:有外键约束的情况下使用truncate
如前面提到过、有FOREIGNKEY(外键)约束的表不能使用TRUNCATE命令、但是可以通过禁用表的外键、然后使用TRUNCATE 清除表数据、最后再恢复表外键的方式来达到目的。
1、生成禁用外键的语句、并导出sql。
2、生成启用外键约束语句、并导出sql。
3、重新整理成新的sql文件。
4、在上面新的sql文件中加入对RY_JBXX表对应的sequence:seq_ry_jbxx进行重置。
5、完整sql文件内容(其中seq_reset是四中生成的存储过程、这里直接调用):
alter table RS_ZGSHBX disable constraintFK_RS_ZGSHB_REFERENCE_RY_JBXX; alter table WS_JKQK disableconstraint FK_WS_JKQK_REFERENCE_RY_JBXX; alter table WS_YBJZQK disableconstraint FK_WS_YBJZQ_REFERENCE_RY_JBXX; truncate tableRY_JBXX; alter table RS_ZGSHBX enable constraintFK_RS_ZGSHB_REFERENCE_RY_JBXX; alter table WS_JKQK enableconstraint FK_WS_JKQK_REFERENCE_RY_JBXX; alter table WS_YBJZQK enableconstraint FK_WS_YBJZQ_REFERENCE_RY_JBXX; select count(*) from ry_jbxx; declare seq_name varchar2(100); begin seq_name :='seq_ry_jbxx'; seq_reset(seq_name); end;