Oracle统计全库表中的时间字段的内数据的时间范围

统计全库时间字段内容的时间范围

建立存储用的表

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了