注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

expdp并drop分区的脚本  

2014-12-09 14:22:39|  分类: 分区表 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

--1. 获取分区名称的包:
create or replace package pkg_exp_part is

    function f_get_partname_day(i_tab_name in varchar2, --按天分区的分区表的表名
                                i_day_no      int --取出前几天的分区
                                ) return varchar2;

    function f_get_partname_month(i_tab_name in varchar2, --按月分区的分区表的表名
                                  i_month_no      int --取出前几天的分区
                                  ) return varchar2;
end pkg_exp_part;
/

create or replace package body pkg_exp_part is

    --获取按天分区的分区名
    function f_get_partname_day(i_tab_name in varchar2, --分区表名
                                i_day_no      int --取出前几天的分区
                                ) return varchar2 as
        v_part_name varchar2(2000);
    begin
        execute immediate 'alter session set nls_date_format=''yyyy-mm-dd''';
        for rec in (select partition_name, high_value
                      from user_tab_partitions
                     where table_name = i_tab_name
                     order by partition_name) loop
            if substr(rec.high_value, 11, 10) = to_char(sysdate - i_day_no,'yyyy-mm-dd') then
                v_part_name := rec.partition_name;
            end if;
        end loop;
        return v_part_name;
    end f_get_partname_day;

    --获取按月分区的分区名
    function f_get_partname_month(i_tab_name in varchar2, --分区表名
                                  i_month_no    int --取出前几月的分区
                                  ) return varchar2 as
        v_part_name varchar2(2000);
    begin
        execute immediate 'alter session set nls_date_format=''yyyy-mm''';
        for rec in (select partition_name, high_value
                      from user_tab_partitions
                     where table_name = i_tab_name
                     order by partition_name) loop
            if substr(rec.high_value, 11, 7) =
               to_char(add_months(sysdate, -i_month_no), 'yyyy-mm') then
                v_part_name := rec.partition_name;
            end if;
        end loop;
        return v_part_name;
    end f_get_partname_month;

end pkg_exp_part;
/

 


--2. 删除分区的包:
create or replace package pkg_drop_part is

    procedure p_drop_part_day(i_tab_name in varchar2, --按天分区的分区表名
                             i_day_no   int --删除前几天的分区
                             );

    procedure p_drop_part_month(i_tab_name in varchar2, --按月分区的分区表名
                               i_month_no int --删除前几个月的分区
                               );
end pkg_drop_part;
/

create or replace package body pkg_drop_part is

    --truncate i_day_no天前的分区
    procedure p_drop_part_day(i_tab_name in varchar2, i_day_no int) is
        v_sql varchar2(2000);
    begin
        execute immediate 'alter session set nls_date_format=''yyyy-mm-dd''';
        for rec in (select partition_name, high_value from user_tab_partitions where table_name = i_tab_name) loop
            if substr(rec.high_value, 11, 10) = to_char(sysdate - i_day_no,'yyyy-mm-dd') then
                execute immediate 'alter table  ' || i_tab_name || ' truncate partition '||rec.partition_name ||' update global indexes';
                v_sql := 'alter table ' || i_tab_name || ' drop partition ' || rec.partition_name || ' update indexes';
                dbms_output.put_line(v_sql);
            end if;
        end loop;
   
    exception
        when others then
            pkg_email.p_sendmail(f_get_proname,sqlerrm);
    end p_drop_part_day;
   
   
    --truncate i_month_no月前的分区
    procedure p_drop_part_month(i_tab_name in varchar2, i_month_no int) is
        v_sql varchar2(2000);
    begin
        execute immediate 'alter session set nls_date_format=''yyyy-mm-dd''';
        if to_char(sysdate,'DD')='1' then  --判断是否是1号,是则truncate 按月分区的表分区
        for rec in (select partition_name, high_value from user_tab_partitions where table_name = i_tab_name) loop
            if substr(rec.high_value, 11, 7) = to_char(add_months(sysdate, -i_month_no), 'yyyy-mm') then
                execute immediate 'alter table  ' || i_tab_name || ' truncate partition '|| rec.partition_name ||' update global indexes';
                v_sql := 'alter table ' || i_tab_name || ' drop partition ' || rec.partition_name || ' update indexes';
                dbms_output.put_line(v_sql);
            end if;
        end loop;
        end if;
   
    exception
        when others then
            pkg_email.p_sendmail(f_get_proname,sqlerrm);       
    end p_drop_part_month;

end pkg_drop_part;
/


3. expdp并drop分区的脚本
vi expdp_part.sh
#!/bin/bash
. /home/oracle/.bash_profile

#备份PO_POLICY_OPTIONS_LOG
partname=`sqlplus -S / as sysdba<<EOF
conn jpyj/jpyj123
set time off
set echo off
set head off
set trimspool on
set termout off
set pagesize 0
set trims on
set trimout on
set feedback off
alter session set nls_date_format='yyyy-mm-dd';
select pkg_exp_part.f_get_partname_month('PO_POLICY_OPTIONS_LOG',6) from dual;
exit
EOF`

cd /databack/expbak/dump_month
rm -rf PO_POLICY_OPTIONS_LOG_`date +%y%m%d`.dump
rm -rf PO_POLICY_OPTIONS_LOG_`date +%y%m%d`.log
#rm -rf *.log
ls -t | awk '{if(NR>600){print $0}}' |xargs rm -f #保留最近600个文件
echo PO_POLICY_OPTIONS_LOG.$partname >> /home/oracle/sh/partname.log
#expdp system/oracle123 tables=nticket.PO_POLICY_OPTIONS_LOG:$partname dumpfile=PO_POLICY_OPTIONS_LOG_`date +%y%m%d`.dump logfile=PO_POLICY_OPTIONS_LOG_`date +%y%m%d`.log DIRECTORY=DUMP_MONTH

 

#truncate按月分区
sqlplus -S scott/tiger <<EOF
exec pkg_del_part.p_del_part_month('PO_POLICY_OPTIONS_LOG',6);
exit
EOF


 

  评论这张
 
阅读(231)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018