SYBASE删除disk sybase删除表

转载

mob64ca140d61c6 2024-04-30 01:10:00

文章标签 SYBASE删除disk sybase delete table action 文章分类 Sybase 数据库

之前在试验了bcp,传统的正向delete方法皆不可取的情况下，考虑使用select into 反向做法，把要保留的数据通过select into 法重新创建对象，

达到了数据快速进入新表，并对新表进行索引默认值重建，即达到了数据清理，也达到了数据清理后索引不会失效的目的，还腾出了碎片空间。

前期采用了静态的处理方式，在由于不同地方部署版本不同导致数据库结构也不完全相同情况下，需要ddlgen方式拿回外网的ddl脚本然后对静态清理程序进行对比修改，非常繁琐，头晕眼花对的，后来考虑到sybase的对象定义信息都保留的系统数据字典中，通过分析数据字典的方式进行对象的动态重构，从而省去了程序对照修改的累事。

以下看我实际处理的步骤思路以及具体脚本：

1.清理程序基本保证在深夜时间可以清理完毕，但是为了保证第二天海量数据运营级的数据库能够正常运行，所以做到了清理程序支持断点续清，清理未完也不影响第二天应用正常使用的目的。所以额外增加了清理日志表：

IF EXISTS (SELECT 1 FROM sysobjects o WHERE  o.name = 'dba_delete_history_data'  AND o.type = 'U') 
drop table dba_delete_history_data 
go 
create table dba_delete_history_data ( 
id                              char(32)                         not null  ,--流水id 
table_name                      varchar(100)                     not null  ,--表名 
start_time                      datetime                             null  ,--清理开始时间 
end_time                        datetime                             null  ,--清理结束时间 
status                          int                              not null,   --清理结果状态 
default_fail_sql         varchar(7000)         null        --默认值加了注释，导致语句过长产生问题 
) 
lock datarows 
go

-----------------------------------------------------------------------------------------------

2.单个对象表的清理封装过程up_delete_history_table

if exists (select 1 
            from  sysobjects 
            where id = object_id('up_delete_history_table') 
            and   type = 'P') 
   drop procedure up_delete_history_table 
go 
create procedure up_delete_history_table  
@objname varchar(100),--对象表名 
@condition_name varchar(1000),--清理条件 
@is_bak varchar(1),--清理后是否备份原表 
@server_lock_scheme varchar(50) --服务的锁级别配置 
as 
declare @start_time datetime  
declare @end_time datetime 
declare @indid int  
declare @keys varchar(1024)  
declare @msg varchar(1024) 
declare @create_tmp_str varchar(1024) 
declare @drop_temp varchar(1024) 
declare @lock_datarows_str varchar(100) 
declare @pk_str varchar(1024) 
declare @start_str varchar(100) 
declare @default_str varchar(8000) 
declare @index_str varchar(1024) 
declare @grant_str varchar(1024) 
declare @ddl_str varchar(1024) 
declare @rename_str varchar(1024) 
declare @end_str varchar(100) 
declare @column_name varchar(1024)  
declare @pre_column_name varchar(1024) 
declare @default_name varchar(255) 
declare @new_id char(32) 
declare @data_str varchar(10) 
declare @sysstat2 int 
declare @tab_lock_scheme varchar(50) 
begin 
set nocount on 
set @start_time=getdate() 
set @data_str=right(convert(varchar(10),getdate(),112),4) 
select @new_id =newid() 
insert into dba_delete_history_data 
select @new_id,@objname,@start_time,null,0,'' 
set @start_str =@objname+' data cleaning start......' 
    print @start_str 
    set @drop_temp="if exists(select 1 from sysobjects where type='U' and name='"+@objname+"_tmp') begin truncate table "+@objname+"_tmp drop table "+@objname+"_tmp  end" 
    exec (@drop_temp) 
  
set @create_tmp_str='select * into '+@objname+'_tmp from '+@objname+'  '+@condition_name 
exec (@create_tmp_str) 
if  @@error  != 0 
       return -1 
create table #temp 
( 
 id int identity, 
 column_name varchar(200), 
 default_name varchar(7500) 
) 
declare @n        int 
declare @rows     int 
select @n=1 
insert #temp(column_name,default_name) 
 select c.name,d.text  
 from syscomments d,sysprocedures p ,syscolumns c,sysobjects o  
 where   d.id   =   c.cdefault   and   p.id=d.id    and c.id=o.id and o.type='U' and o.name=@objname 
 and p.sequence=0   and p.status & 4096 =4096  
order by d.id,d.colid 
select @rows = @@rowcount 
select @pre_column_name='' 
while @n <= @rows 
begin 
select  @column_name=column_name,@default_name=default_name from #temp where id=@n 
if @n=1 
select @default_str ='alter table '+@objname+'_tmp replace '+@column_name+' '+@default_name 
if @n>1 and  @column_name!= @pre_column_name 
 begin 
   exec (@default_str) 
   if @@error  != 0 
     begin  
       update dba_delete_history_data  
       set default_fail_sql=default_fail_sql+left(@default_str,400)+'     ' 
       where table_name=@objname 
     end  
   select @default_str ='alter table '+@objname+'_tmp replace '+@column_name+' '+@default_name 
 end 
if  @n>1 and  @column_name = @pre_column_name 
    select  @default_str=@default_str+@default_name 
     
select @pre_column_name=@column_name 
select @n = @n + 1 
end 
if @rows >0  
 exec (@default_str) 
 if @@error  != 0 
    begin  
     update dba_delete_history_data  
     set default_fail_sql=default_fail_sql+left(@default_str,400)+'     ' 
     where table_name=@objname 
    end  
drop table #temp 
if @@trancount = 0 
begin set chained off end 
set transaction isolation level 1 
if @objname like "%.%.%" and  substring(@objname, 1, charindex(".", @objname) - 1) != db_name() 
begin  raiserror 17460 end 
if not exists (select id   from sysobjects  where id = object_id(@objname)) 
begin raiserror 17461 end 
select @indid = min(indid) 
   from sysindexes  where id = object_id(@objname) and indid > 0 and indid < 255 
if @indid is null 
begin 
       exec sp_getmessage 17640, @msg output 
       print @msg 
end 
while @indid is not null 
begin 
   declare @i int 
   declare @thiskey varchar(30) 
   declare @sorder char(4)    
   declare @lastindid int 
   select @keys = "", @i = 1 
   while @i <= 31 
   begin 
       select @thiskey = index_col(@objname, @indid, @i) 
       if (@thiskey is null) 
       begin 
           goto keysdone 
       end 
       if @i > 1 
       begin 
           select @keys = @keys + ", " 
       end 
       select @keys = @keys + @thiskey 
       select @sorder = index_colorder(@objname, @indid, @i) 
       if (@sorder = "desc") 
           select @keys = @keys + " " + @sorder 
       select @i = @i + 1 
   end 
   keysdone: 
   if exists(select 1 from sysindexes where id = object_id(@objname) and indid = @indid and  status  & 2048 = 2048) 
begin 
select @pk_str='alter table '+@objname+'_tmp add constraint '+name+' primary key nonclustered ('+@keys+')' 
from sysindexes where id = object_id(@objname) and indid = @indid and  status  & 2048 = 2048 
exec (@pk_str) 
end 
if exists(select 1 from sysindexes where id = object_id(@objname) and indid = @indid and  status  & 2048 <> 2048) 
begin 
select @index_str='create index '+name+' on '+@objname+'_tmp('+@keys+')' 
from sysindexes where id = object_id(@objname) and indid = @indid and  status  & 2048 <> 2048 
exec (@index_str) 
   end 
   select @lastindid = @indid 
   select @indid = null 
   select @indid = min(indid) from sysindexes  where id = object_id(@objname) and indid > @lastindid and indid < 255 
end 
create table #temp_protect 
  ( 
    id int identity, 
    table_name varchar(200), 
    user_name varchar(200), 
    action  int, 
    protecttype int 
  ) 
declare @protect_n        int 
declare @protect_rows     int 
    declare @protect_str varchar(200) 
select @protect_n=1 
insert #temp_protect(table_name,user_name,action,protecttype) 
 select o.name,u.name,p.action,p.protecttype  
from sysprotects p,sysobjects o,sysusers u where p.id=o.id and u.uid=p.uid and o.name=@objname 
select @protect_rows = @@rowcount 
while @protect_n <= @protect_rows 
begin 
select  @protect_str=(case when protecttype=0 then 'grant with grant ' 
when protecttype=1 then 'grant ' 
when protecttype=2 then  'revoke ' end) + 
(case when action=151 then 'references' 
when action=167 then 'set proxy' 
when action=187 then 'set statistics on' 
when action=188 then 'set statistics off' 
when action=193 then 'select' 
when action=195 then 'insert' 
when action=196 then 'delete' 
when action=197 then 'update' 
when action=198 then 'create table' 
when action=203 then 'create database' 
when action=205 then 'grant' 
when action=206 then 'revoke' 
when action=207 then 'create view' 
when action=221 then 'create trigger' 
when action=222 then 'create procedure' 
when action=224 then 'execute' 
when action=228 then 'dump database' 
when action=233 then 'create default' 
when action=235 then 'dump transaction' 
when action=236 then 'create rule' 
when action=253 then 'connect' 
when action=282 then 'delete statistics' 
when action=317 then 'dbcc' 
when action=320 then 'truncate table' 
when action=326 then 'update statistics' 
when action=347 then 'set tracing' end) + 
' on '+ 
table_name+ 
'_tmp to '+ 
user_name   
from #temp_protect where id = @protect_n 
exec (@protect_str) 
select @protect_n = @protect_n + 1 
end 
drop table #temp_protect  
if(lower(@is_bak)='y') 
begin 
 set @drop_temp="if exists(select 1 from sysobjects where type='U' and name='"+@objname+'_'+@data_str+"') begin truncate table "+@objname+'_'+@data_str+" drop table "+@objname+'_'+@data_str+"  end" 
      exec (@drop_temp) 
 set @rename_str='exec sp_rename '+@objname+','+@objname+'_'+@data_str 
 exec (@rename_str) 
 set @rename_str='' 
 ----更改备份表的lock schema以达到重新编译表对象的目的 
 select @sysstat2 = sysstat2 from sysobjects where name = @objname+'_'+@data_str and type = 'U' 
 if @sysstat2 & 8192 = 8192 --原为allpages 
   begin     
     set @lock_datarows_str='alter table '+@objname+'_'+@data_str+' lock  datarows' 
     exec (@lock_datarows_str) 
   end    
 else if @sysstat2 & 16384 =  16384 --原为datapages    
   begin     
     set @lock_datarows_str='alter table '+@objname+'_'+@data_str+' lock  datarows' 
     exec (@lock_datarows_str) 
   end    
 else if @sysstat2 & 32768 = 32768 --原为datarows    
   begin 
     set @lock_datarows_str='alter table '+@objname+'_'+@data_str+' lock  datapages' 
     exec (@lock_datarows_str) 
     set @lock_datarows_str='alter table '+@objname+'_'+@data_str+' lock  datarows' 
     exec (@lock_datarows_str) 
   end 
end 
if(lower(@is_bak)='n') 
begin 
set @ddl_str='truncate table '+@objname 
exec (@ddl_str) 
set @ddl_str='drop table '+@objname 
exec (@ddl_str) 
end 
set @rename_str='exec sp_rename '+@objname+'_tmp,'+@objname 
exec (@rename_str) 
--更改新的正式表的lock schema以达到重新编译表对象的目的 
select @sysstat2 = sysstat2 from sysobjects where name = @objname and type = 'U' 
if @sysstat2 & 8192 = 8192 --原为allpages 
   begin     
     set @lock_datarows_str='alter table '+@objname+' lock  datarows' 
     exec (@lock_datarows_str) 
   end    
else if @sysstat2 & 16384 =  16384 --原为datapages    
   begin     
     set @lock_datarows_str='alter table '+@objname+' lock  datarows' 
     exec (@lock_datarows_str) 
   end    
else if @sysstat2 & 32768 = 32768 --原为datarows    
   begin 
     set @lock_datarows_str='alter table '+@objname+' lock  datapages' 
     exec (@lock_datarows_str) 
     set @lock_datarows_str='alter table '+@objname+' lock  datarows' 
     exec (@lock_datarows_str) 
   end 
    
set @end_str=@objname+' data cleaning complete!' 
    print @end_str 
set @end_time=getdate() 
update dba_delete_history_data 
set status=1,end_time=@end_time 
where id=@new_id and default_fail_sql ='' 
set nocount off 
end 
go 
sp_procxmode up_delete_history_table, anymode 
go 
grant execute on up_delete_history_table to sms 
go 
grant execute on up_delete_history_table to web 
go

----------------------------------------------------------------------------------------------------------

3.多表传入解放人机交互时间作为整体任务，

进行同一分割时间点的批任务清理过程up_delete_history_data

if exists (select 1 
            from  sysobjects 
            where id = object_id('up_delete_history_data') 
            and   type = 'P') 
   drop procedure up_delete_history_data 
go 
create procedure up_delete_history_data  
@objnames varchar(8000), --多表字符串，表名间用 逗号隔开 
@history_time varchar(20), --清理时间点 
@is_bak varchar(1) --是否备份原表标记 
as 
declare @condition_name varchar(1000) 
declare @objname varchar(50) 
declare @lock_scheme varchar(50) 
select @objnames=','+@objnames+',' 
begin 
    if(lower(@is_bak) not in ('n','y')) 
   begin 
     print "the third parameter means is bakup all source tables, must be 'n' or 'y'" 
return -1 
   end 
   if @objnames is null or char_length(@objnames)=0  
        begin 
             print "please input source tables" 
            return -1 
        end 
   if @history_time is null or @history_time>getdate()  
      begin 
         print "please input history_time and histroy_time must less than current date" 
         return -1 
      end 
select @lock_scheme=value2 from master..sysconfigures where config=327 and name='lock scheme' 
   if charindex(',ew_sending_queue,',@objnames) > 0 
    begin 
 set  @objname='ew_sending_queue' 
 set  @condition_name='where send_time > convert(char(10),dateadd (dd ,-5,getdate()),111)' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
if charindex(',sms_sending,',@objnames) > 0 
    begin 
 set  @objname='sms_sending' 
 set  @condition_name='where send_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',sms_receiving,',@objnames) > 0 
    begin 
 set  @objname='sms_receiving' 
 set  @condition_name='where receive_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',sms_timing,',@objnames) > 0 
    begin 
 set  @objname='sms_timing' 
 set  @condition_name='where creation_time > convert(char(10),dateadd (dd ,-5,getdate()),111)' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',si_record,',@objnames) > 0 
    begin 
 set  @objname='si_record' 
 set  @condition_name='where creation_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',msg_comment,',@objnames) > 0 
    begin 
 set  @objname='msg_comment' 
 set  @condition_name='where creation_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',msg_homework,',@objnames) > 0 
    begin 
 set  @objname='msg_homework' 
 set  @condition_name='where creation_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',msg_notice,',@objnames) > 0 
    begin 
 set  @objname='msg_notice' 
 set  @condition_name='where creation_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',msg_sending,',@objnames) > 0 
    begin 
 set  @objname='msg_sending' 
 set  @condition_name='where creation_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',msg_receiving,',@objnames) > 0 
    begin 
 set  @objname='msg_receiving' 
 set  @condition_name='where creation_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',ex_fjabt_student_record,',@objnames) > 0 
    begin 
 set  @objname='ex_fjabt_student_record' 
 set  @condition_name='where creation_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',ex_fjabt_teacher_record,',@objnames) > 0 
    begin 
 set  @objname='ex_fjabt_teacher_record' 
 set  @condition_name='where creation_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',adc_interface_log,',@objnames) > 0 
    begin 
 set  @objname='adc_interface_log' 
 set  @condition_name='where creation_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',adc_userbind_log,',@objnames) > 0 
    begin 
 set  @objname='adc_userbind_log' 
 set  @condition_name='where creation_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',fee_pay_detail,',@objnames) > 0 
    begin 
 set  @objname='fee_pay_detail' 
 set  @condition_name='where pay_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',log_card_login,',@objnames) > 0 
    begin 
 set  @objname='log_card_login' 
 set  @condition_name='where creation_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',sys_operation_log,',@objnames) > 0 
    begin 
 set  @objname='sys_operation_log' 
 set  @condition_name='where operated_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',st_student_signin,',@objnames) > 0 
    begin 
 set  @objname='st_student_signin' 
 set  @condition_name='where statistic_date > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',st_teacher_signin,',@objnames) > 0 
    begin 
 set  @objname='st_teacher_signin' 
 set  @condition_name='where statistic_date > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',st_device,',@objnames) > 0 
    begin 
 set  @objname='st_device' 
 set  @condition_name='where stat_date > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',st_fjabt_student_record,',@objnames) > 0 
    begin 
 set  @objname='st_fjabt_student_record' 
 set  @condition_name='(index pk_st_fjabt_student_record) where statistic_date > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',st_fjabt_teacher_record,',@objnames) > 0 
    begin 
 set  @objname='st_fjabt_teacher_record' 
 set  @condition_name='(index pk_st_fjabt_teacher_record) where statistic_date > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',st_school,',@objnames) > 0 
    begin 
 set  @objname='st_school' 
 set  @condition_name='where statistic_date > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',st_user,',@objnames) > 0 
    begin 
 set  @objname='st_user' 
 set  @condition_name='where statistic_date > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
   if charindex(',log_module_access,',@objnames) > 0 
    begin 
 set  @objname='log_module_access' 
 set  @condition_name='where creation_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end  
   if charindex(',log_user_operation,',@objnames) > 0 
    begin 
 set  @objname='log_user_operation' 
 set  @condition_name='where creation_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end  
   if charindex(',eb_login_log,',@objnames) > 0 
    begin 
 set  @objname='eb_login_log' 
 set  @condition_name='where login_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end  
   if charindex(',fp_call_bill,',@objnames) > 0 
    begin 
 set  @objname='fp_call_bill' 
 set  @condition_name='where creation_time > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end  
   if charindex(',st_card_class,',@objnames) > 0 
    begin 
 set  @objname='st_card_class' 
 set  @condition_name='(index pk_st_card_class) where stat_date > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end  
   if charindex(',st_student_class,',@objnames) > 0 
    begin 
 set  @objname='st_student_class' 
 set  @condition_name='(index pk_st_student_class) where stat_date > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end  
   if charindex(',st_sms_analysis_class,',@objnames) > 0 
    begin 
 set  @objname='st_sms_analysis_class' 
 set  @condition_name='(index pk_st_sms_analysis_class) where stat_date > '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end  
   if charindex(',st_student_record,',@objnames) > 0 
    begin 
 set  @objname='st_student_record' 
 set  @condition_name='(index idx_school_id) where month >= convert(int,datepart(year,+'''+@history_time+'''))*100+convert(int,datepart(month,+'''+@history_time+'''))' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end  
 if charindex(',ew_score_detail,',@objnames) > 0 
    begin 
 set  @objname='ew_score_detail' 
 set  @condition_name='where process_time> '''+@history_time+'''' 
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme 
end 
end 
go 
sp_procxmode up_delete_history_data, anymode 
go

-------------------------------------------------------------------------------

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。