统计全库时间字段内容的时间范围
建立存储用的表
create table time_limit
(
table_name varchar2(40),
column_name varchar2(40),
time_part varchar2(255)
)
执行下面的匿名存储
时间类型并不局限于Date,可以添加其他时间类型
DECLARE
v_table_name varchar2(40);
v_column_name varchar2(40);
v_str_time varchar2(255);
str_sql varchar2(1000);
str_sql_1 varchar2(1000);
cursor tmp_pinjie IS select table_name,column_name from user_tab_columns where data_type=‘DATE‘;
begin
open tmp_pinjie;
loop
fetch tmp_pinjie into v_table_name,v_column_name;
exit when tmp_pinjie%notfound;
str_sql:=‘select min(to_char(‘||v_column_name||‘,‘‘yyyy/mm/dd‘‘))||‘‘-‘‘||max(to_char(‘||v_column_name||‘,‘‘yyyy/mm/dd‘‘)) from ‘||v_table_name;
execute immediate str_sql into v_str_time;
str_sql_1:=‘insert into time_limit(table_name,column_name,time_part) values(‘‘‘||v_table_name||‘‘‘,‘‘‘||v_column_name||‘‘‘,‘‘‘||v_str_time||‘‘‘)‘;
execute immediate str_sql_1;
commit;
end loop;
close tmp_pinjie;
commit;
end;
然后坐等结果就ok了
Oracle统计全库表中的时间字段的内数据的时间范围