首先写一个存储过程 reset_sequence
CREATE OR REPLACE PROCEDURE reset_sequence (seq_name IN VARCHAR2, tSql IN Varchar2) AS cval INTEGER;startvalue Integer;inc_by VARCHAR2(25); BEGIN EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0'; EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval; EXECUTE IMMEDIATE tSql INTO startvalue; cval := cval - startvalue + 1; IF cval < 0 THEN inc_by := ' INCREMENT BY '; cval:= ABS(cval); ELSE inc_by := ' INCREMENT BY -'; END IF; EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || inc_by || cval; EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval; EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' INCREMENT BY 1'; END reset_sequence;/
--开始批量重置oracle的sequenceexec reset_sequence('yp_chain_detail_seq','select max(id)+1 from yp_chain_detail');exec reset_sequence('yp_chain_seq',' select max(chain_id)+1 from yp_chain');exec reset_sequence('yp_column_field_map_seq','select max(id)+1 from yp_column_field_map');exec reset_sequence('yp_column_property_map_seq','select max(id)+1 from yp_column_property_map');exec reset_sequence('yp_data_extra_seq','select max(id)+1 from yp_data_extra');exec reset_sequence('yp_data_seq','select max(id)+1 from yp_data');exec reset_sequence('yp_data_temp_seq',' select max(id)+1 from yp_data_temp');exec reset_sequence('yp_fields_meta_data_seq','select max(fieldid)+1 from yp_fields_meta_data');exec reset_sequence('yp_field_query_seq',' select max(id)+1 from yp_field_query');exec reset_sequence('yp_impexp_seq','select max(id)+1 from yp_impexp');exec reset_sequence('yp_impexp_template_seq',' select max(templateid)+1 from yp_impexp_template');exec reset_sequence('yp_list_header_seq',' select max(id)+1 from yp_list_header');exec reset_sequence('yp_modules_meta_data_seq',' select max(moduleid)+1 from yp_modules_meta_data');exec reset_sequence('yp_property_items_seq','select max(itemid)+1 from yp_property_items');exec reset_sequence('yp_property_seq','select max(propertyid)+1 from yp_property');exec reset_sequence('yp_recommend_history_seq',' select max(id)+1 from yp_recommend_history');exec reset_sequence('yp_recommend_seq',' select max(id)+1 from yp_recommend');exec reset_sequence('yp_recommend_stat_seq','select max(id)+1 from yp_recommend_stat');exec reset_sequence('yp_structured_data_seq',' select max(id)+1 from yp_structured_data');
OK！
