Oracle Interval Partition 默认分区重命名-脚本

1. [oracle@yhbd08 scripts]$ crontab -l | tail -7
   
2. 
   
3. #每天expdp备份数据库,Add by fjzcau 2015-12-22
   
4. 00 19 * * * /yohodata/fjzcau/scripts/yhbd08_orcl_expdp.sh > /dev/null 2>&1
   
5. 
   
6. #每天自动重命名分区表 yhbi_retail.fact_storage_sheet ,Add by fjzcau 2015-12-24
   
7. 00 12 * * * /yohodata/fjzcau/scripts/fact_storage_sheet_rename_partition.sh > /dev/null 2>&1
   
8. 
   

--Oracle 默认分区名 SYS_P 开头

1. select
   
2.   table_name ,
   
3.   partition_name ,
   
4.   high_value
   
5. from user_tab_partitions
   
6. where table_name like 'FACT_STORAGE_SHEET_%' --and partition_name like 'SYS%'
   
7. order by 1,2;
8. 
   
9. TABLE_NAME              PARTITION_NAME        HIGH_VALUE
   
10. ---------------------  --------------------- ---------------
    
11. FACT_STORAGE_SHEET_2    P0                    19900101
    
12. FACT_STORAGE_SHEET_2    SYS_P1000             20150602
    
13. FACT_STORAGE_SHEET_2    SYS_P1001             20150603
    
14. FACT_STORAGE_SHEET_2    SYS_P1002             20150604
    
15. FACT_STORAGE_SHEET_2    SYS_P1003             20150605
    
16. FACT_STORAGE_SHEET_2    SYS_P1004             20150606
17. 
    

1. Oracle 自动创建分区，分区名字默认以 "SYS_P" 开头，为方便日常查询，每天中午12点自动重命名分区。
   
2. 存储过程的执行脚本：/yohodata/fjzcau/scripts/fact_storage_sheet_rename_partition.sh

1. #---------------------------------------------------------------------------#
   
2. # Scripts : /yohodata/fjzcau/scripts/torage_sheet_rename_partition.sh
   
3. # Author : fangjz/YOHO!
   
4. # Date : 2015/12/24
   
5. # Purpose : Rename Oracle partition
   
6. # Notes : This script can be run in crontab or in other shell script.
   
7. # Parameters :
   
8. #---------------------------------------------------------------------------#
   
9. # Oracle Env
   
10. if [ -f /home/oracle/.bash_profile ]
    
11. then
    
12.    . /home/oracle/.bash_profile
    
13. fi
    
14. 
    
15. bakdate=`date '+%Y%m%d%H'`
    
16. i_pre="`hostname`_orcl"
    
17. log=${i_pre}_fact_storage_sheet_rename_partition_${bakdate}.log
    
18. 
    
19. sqlplus YHBI_RETAIL/yhbi_retail <<EOF > /yohodata/fjzcau/scripts/log_ora/$log
    
20. declare
    
21. v_sql varchar(400);
    
22. v_table_name user_tab_partitions.table_name%type;
    
23. v_partition_name user_tab_partitions.partition_name%type;
    
24. v_high_value varchar(200);
    
25. v_tmp_partition_name user_tab_partitions.partition_name%type;
    
26. 
    
27. cursor cur is
    
28. select
    
29.   table_name ,
    
30.   partition_name ,
    
31.   high_value
    
32. from user_tab_partitions
    
33. where partition_name like 'SYS%' and table_name = 'FACT_STORAGE_SHEET' ;
    
34. 
    
35. begin
    
36.   open cur;
    
37.   loop
    
38.     fetch cur into v_table_name,v_partition_name,v_high_value;
    
39.     exit when cur%notfound;
    
40.     v_tmp_partition_name := to_char(to_date(v_high_value - 1, 'yyyymmdd'),'yyyymmdd');
    
41.     v_sql := 'alter table '|| v_table_name ||' rename partition '
    
42.     ||v_partition_name
    
43.     ||' to P' || v_tmp_partition_name;
    
44.     dbms_output.put_line( v_sql );
    
45.     execute immediate v_sql;
    
46.   end loop;
    
47.   close cur;
    
48. end;
    
49. /
    
50. exit
    
51. EOF

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22661144/viewspace-1960905/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22661144/viewspace-1960905/