20230917_数据库过程_表数据清理过程存档

----20230806 表数据清理

drop table shzc.dba_segments ;

create table shzc.dba_segments as
select * from  sys.dba_segments t1
where SEGMENT_TYPE='TABLE'
and (t1.owner='SHZC' 
or upper(t1.segment_name) like 'CJHJ%'
 or upper(t1.segment_name) like '%SHZC%'
  or upper(t1.segment_name) like '%BLD%'
 or upper(t1.segment_name) like 'CJHD%' 
 or upper(t1.segment_name) like 'TMM_%')
 and round(bytes/1024/1024,2)>10;
 
drop table shzc.ALL_TAB_COLUMNS ;
 
 create table shzc.ALL_TAB_COLUMNS as
select a.owner,a.table_name,a.column_name,a.data_type,a.data_length,a.column_id from ALL_TAB_COLUMNS a
where a.owner in ('SHZC','ZHYW','ZIBO','LWZ');

  ----查看数据表更新时间
  drop table shzc.all_objects ;
  
   create table shzc.all_objects as
select a.owner,a.object_name,a.created,a.object_type 
from all_objects a where a.owner in ('SHZC','ZHYW','ZIBO','LWZ')
and object_type='TABLE'; 

------------------------上面是数据准备，下面是执行过程------因为 sys.表无法过程处理

----列出目标表
zhyw.shc_drop_retable(upper('shzc_table_name_list'),'SHZC');
SQL_STRING:='create table shzc.shzc_table_name_list as
select  distinct ''drop table ''||t1.owner||''.''||t1.segment_name||''  purge;'' dr, round(bytes/1024/1024,2) bagm ,bytes,
t1.owner,t1.segment_name,t1.segment_type,t1.tablespace_name,b.created
from shzc.dba_segments t1,
shzc.all_objects b   
where SEGMENT_TYPE=''TABLE''
and (t1.owner=''SHZC'' 
or upper(t1.segment_name) like ''CJHJ%''
 or upper(t1.segment_name) like ''%SHZC%''
  or upper(t1.segment_name) like ''%BLD%''
 or upper(t1.segment_name) like ''CJHD%'' 
 or upper(t1.segment_name) like ''TMM_%'')
 and round(bytes/1024/1024,2)>10
 and t1.owner=b.owner
 and t1.segment_name=b.object_name
 and b.created<sysdate-5
order by bytes desc   ';
EXECUTE IMMEDIATE (SQL_STRING);



----关联出第一个日期字段

zhyw.shc_drop_retable(upper('shzc_table_name_list_rqzd'),'SHZC');
SQL_STRING:='create table shzc.shzc_table_name_list_rqzd as
select * from
(select a.owner,a.table_name,a.column_name,a.data_type,a.data_length,a.column_id,
b.dr , row_number() over (partition by a.owner,a.table_name  order by a.column_id  ) 排名
from shzc.ALL_TAB_COLUMNS a,
shzc.shzc_table_name_list b
 where a.TABLE_NAME =b.segment_name
 and a.owner=b.owner
 and (a.DATA_TYPE =''DATE'' 
 or a.COLUMN_NAME like ''%日期%''
 or a.COLUMN_NAME like ''%时间%''
 or a.COLUMN_NAME like ''%day%''
 or a.COLUMN_NAME like ''%time%''
 or a.COLUMN_NAME like ''%cycle%'')) a
 where 排名=1 ';
EXECUTE IMMEDIATE (SQL_STRING);

 
 ----获取涉及表里日期字段
 
zhyw.shc_drop_retable(upper('shzc_table_name_list_rqzda'),'SHZC');
SQL_STRING:='create table shzc.shzc_table_name_list_rqzda as
 select a.*,b.column_name,b.data_type,b.data_length,b.column_id 
 from shzc.shzc_table_name_list a,
 shzc.shzc_table_name_list_rqzd b
 where a.dr=b.dr ';
EXECUTE IMMEDIATE (SQL_STRING);
 


 zhyw.shc_drop_retable(upper('shzc_table_name_list_rqzdb'),'SHZC');
SQL_STRING:='create table shzc.shzc_table_name_list_rqzdb as
 select a.*,''select ^''||a.owner||''^ owner,^''||a.segment_name||''^ segment_name,sysdate in_time,max(''||(case 
 when a.data_type=''DATE'' then ''to_char(''||a.column_name||'',^yyyymmdd^)) ''||a.column_name 
 when a.data_type=''NUMBER'' then ''to_char(''||a.column_name||'')) ''||a.column_name 
 when a.data_type in (''CHAR'',''VARCHAR2'') then a.column_name||'') ''||a.column_name
 end)||'',count(*) count_num from ''||a.owner||''.''||a.segment_name zd_chuli
 from  shzc.shzc_table_name_list_rqzda a ';
EXECUTE IMMEDIATE (SQL_STRING);

-------------------- shzc.ghjk_bcv_bcvb_mxh 表内容变更
update shzc.shzc_table_name_list_rqzdb a set zd_chuli=replace(a.zd_chuli,'^','''');
commit;

SQL_STRING:='delete shzc.shzc_table_name_list_rqzd_cd  ';
EXECUTE IMMEDIATE (SQL_STRING);
commit;

            
    FOR a IN (select  zd_chuli from shzc.shzc_table_name_list_rqzdb ) LOOP
        SQLSTMT0 := 'insert into shzc.shzc_table_name_list_rqzd_cd ';
        SQLSTMT0 := SQLSTMT0||a.zd_chuli ;
        execute immediate (SQLSTMT0);
        commit;
    END LOOP;  

update shzc.shzc_table_name_list_rqzd_cd a set a.start_datetime=substr(replace(a.start_datetime,'-',''),1,8);
commit;

  zhyw.shc_drop_retable(upper('shzc_table_name_list_rqzdd'),'SHZC');
SQL_STRING:='create table shzc.shzc_table_name_list_rqzdd as
 select a.*,b.start_datetime,b.count_num,zhyw.shzc_zfc_zftq_tscl(''^''||a.segment_name,''^'',''_'') kt 
 from shzc.shzc_table_name_list_rqzda a,
 shzc.shzc_table_name_list_rqzd_cd b
 where a.owner=b.owner
 and a.segment_name=b.segment_name ';
EXECUTE IMMEDIATE (SQL_STRING);