----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);