expdp_procedure

create or replace procedure expdp_clt_perf_p_mod_out(p_start_time in date,
                                                   p_end_time   in date) is
  v_int_sql    varchar2(3000);
  v_table_name varchar2(100);
  v_tab_name   varchar2(100);
begin
  for i in (select distinct table_name
              from user_tab_columns
             where column_name in ('STAMPTIME')
               AND TABLE_NAME LIKE 'CLT%') loop
    v_table_name := i.table_name;
    --
 
    for k in (select 'expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile='||v_table_name||'.dmp query='||v_table_name||
        ':\"where STAMPTIME  between to_date('''||p_start_time||''',''YYYY-MM-DD HH24:MI:SS'')'||
                     ' and to_date('''||p_end_time||''',''YYYY-MM-DD HH24:MI:SS'')  \" tables='||v_table_name||'' expdp_select_clt
                from dual) loop
     --
     --
      insert into wt_expdp_sel(expdp_text,table_name) values( k.expdp_select_clt, v_table_name ) ;
      commit;
    end loop;
  end loop;
  -----
  -----
  for f in (select distinct table_name
              from user_tab_columns
             where column_name in ('START_TIME')
               AND (TABLE_NAME LIKE 'PERF%'
               or TABLE_NAME LIKE 'P_%'
               or TABLE_NAME LIKE 'MOD_%')) loop
    v_tab_name := f.table_name;
 
      for m in (select 'expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile='||v_table_name||'.dmp query='||v_table_name||
        ':\"where STAMPTIME  between to_date('''||p_start_time||''',''YYYY-MM-DD HH24:MI:SS'')'||
                     ' and to_date('''||p_end_time||''',''YYYY-MM-DD HH24:MI:SS'')  \" tables='||v_table_name||'' expdp_select_more
                   
                from dual) loop
      insert into wt_expdp_sel(expdp_text,table_name) values( m.expdp_select_more, v_tab_name ) ;
      commit;
    end loop;
  end loop;
end expdp_clt_perf_p_mod_out;

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

2.wt_expdp_sel表：

-- Create table
create table WT_EXPDP_SEL
(
  expdp_text   VARCHAR2(3000),
  table_name VARCHAR2(40)
)
tablespace WCDMA ;
------------------------------

3.results:

1    expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile=CLT_CM_W_HW_2GNCELL.dmp query=CLT_CM_W_HW_2GNCELL:\"where STAMPTIME  between to_date('2012-05-16 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2012-05-17 00:00:00','YYYY-MM-DD HH24:MI:SS')  \" tables=CLT_CM_W_HW_2GNCELL   
2    expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile=CLT_CM_W_HW_ADJNODE.dmp query=CLT_CM_W_HW_ADJNODE:\"where STAMPTIME  between to_date('2012-05-16 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2012-05-17 00:00:00','YYYY-MM-DD HH24:MI:SS')  \" tables=CLT_CM_W_HW_ADJNODE   
3    expdp wcdma/uwaysoft2010@db122 directory=wcdmadump dumpfile=CLT_CM_W_HW_AICH.dmp query=CLT_CM_W_HW_AICH:\"where STAMPTIME  between to_date('2012-05-16 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2012-05-17 00:00:00','YYYY-MM-DD HH24:MI:SS')  \" tables=CLT_CM_W_HW_AICH