Oracle Interval Partition 預設分割槽重新命名-指令碼
-
[oracle@yhbd08 scripts]$ crontab -l | tail -7
-
-
#每天expdp備份資料庫,Add by fjzcau 2015-12-22
-
00 19 * * * /yohodata/fjzcau/scripts/yhbd08_orcl_expdp.sh > /dev/null 2>&1
-
-
#每天自動重新命名分割槽表 yhbi_retail.fact_storage_sheet ,Add by fjzcau 2015-12-24
-
00 12 * * * /yohodata/fjzcau/scripts/fact_storage_sheet_rename_partition.sh > /dev/null 2>&1
-
--Oracle 預設分割槽名 SYS_P 開頭
-
select
-
table_name ,
-
partition_name ,
-
high_value
-
from user_tab_partitions
-
where table_name like 'FACT_STORAGE_SHEET_%' --and partition_name like 'SYS%'
- order by 1,2;
-
-
TABLE_NAME PARTITION_NAME HIGH_VALUE
-
--------------------- --------------------- ---------------
-
FACT_STORAGE_SHEET_2 P0 19900101
-
FACT_STORAGE_SHEET_2 SYS_P1000 20150602
-
FACT_STORAGE_SHEET_2 SYS_P1001 20150603
-
FACT_STORAGE_SHEET_2 SYS_P1002 20150604
-
FACT_STORAGE_SHEET_2 SYS_P1003 20150605
- FACT_STORAGE_SHEET_2 SYS_P1004 20150606
-
-
Oracle 自動建立分割槽,分割槽名字預設以 "SYS_P" 開頭,為方便日常查詢,每天中午12點自動重新命名分割槽。
- 儲存過程的執行指令碼:/yohodata/fjzcau/scripts/fact_storage_sheet_rename_partition.sh
-
#---------------------------------------------------------------------------#
-
# Scripts : /yohodata/fjzcau/scripts/torage_sheet_rename_partition.sh
-
# Author : fangjz/YOHO!
-
# Date : 2015/12/24
-
# Purpose : Rename Oracle partition
-
# Notes : This script can be run in crontab or in other shell script.
-
# Parameters :
-
#---------------------------------------------------------------------------#
-
# Oracle Env
-
if [ -f /home/oracle/.bash_profile ]
-
then
-
. /home/oracle/.bash_profile
-
fi
-
-
bakdate=`date '+%Y%m%d%H'`
-
i_pre="`hostname`_orcl"
-
log=${i_pre}_fact_storage_sheet_rename_partition_${bakdate}.log
-
-
sqlplus YHBI_RETAIL/yhbi_retail <<EOF > /yohodata/fjzcau/scripts/log_ora/$log
-
declare
-
v_sql varchar(400);
-
v_table_name user_tab_partitions.table_name%type;
-
v_partition_name user_tab_partitions.partition_name%type;
-
v_high_value varchar(200);
-
v_tmp_partition_name user_tab_partitions.partition_name%type;
-
-
cursor cur is
-
select
-
table_name ,
-
partition_name ,
-
high_value
-
from user_tab_partitions
-
where partition_name like 'SYS%' and table_name = 'FACT_STORAGE_SHEET' ;
-
-
begin
-
open cur;
-
loop
-
fetch cur into v_table_name,v_partition_name,v_high_value;
-
exit when cur%notfound;
-
v_tmp_partition_name := to_char(to_date(v_high_value - 1, 'yyyymmdd'),'yyyymmdd');
-
v_sql := 'alter table '|| v_table_name ||' rename partition '
-
||v_partition_name
-
||' to P' || v_tmp_partition_name;
-
dbms_output.put_line( v_sql );
-
execute immediate v_sql;
-
end loop;
-
close cur;
-
end;
-
/
-
exit
- EOF
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22661144/viewspace-1960905/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle Interval Partition 自動分割槽表-實驗Oracle
- Oracle查詢Interval partition分割槽表內資料Oracle
- partition 分割槽表重新命名
- 【實驗】【PARTITION】RANGE分割槽表重新命名錶分割槽(Rename Partition)
- 定期truncate 歷史間隔分割槽INTERVAL PARTITION
- 11g分割槽新特性之interval partition
- oracle分割槽partition及分割槽索引partition index(一)Oracle索引Index
- Oracle11g INTERVAL分割槽新增分割槽策略Oracle
- ORACLE 範圍分割槽 partition-range分割槽Oracle
- Oracle 分割槽(partition)技術Oracle
- Oracle分割槽表(Partition Table)Oracle
- INTERVAL分割槽表鎖分割槽操作
- oracle分割槽及分割槽索引partition_partition index_維護(一)Oracle索引Index
- oracle分割槽及分割槽索引partition_partition index_維護(二)Oracle索引Index
- oracle分割槽及分割槽索引partition_partition index_維護(三)Oracle索引Index
- oracle分割槽及分割槽索引partition_partition index_維護(四)Oracle索引Index
- oracle reference partition引用分割槽(一)Oracle
- oracle list partition列表分割槽(一)Oracle
- oracle partition分割槽_分割槽列為空測試(一)Oracle
- oracle分割槽及分割槽索引partition_partition index_維護(五)_快捷方法Oracle索引Index
- ORACLE 11g 範圍分割槽錶轉換INTERVAL分割槽表Oracle
- Oracle Partition 分割槽詳細總結Oracle
- oracle hash partition雜湊分割槽(一)Oracle
- Oracle Interval Partition 生產環境-建立表FACT_STORAGE_SHEET為分割槽的過程Oracle
- DATE型別INTERVAL分割槽型別
- oracle composite partition組合分割槽_composite partition rangeOracle
- oracle 生成預設的子分割槽Oracle
- interval partition自動新增分割槽引起的shared pool 4031錯誤
- 【實驗】【PARTITION】RANGE分割槽表截斷表分割槽(Truncate Partition)
- 【實驗】【PARTITION】RANGE分割槽表移動表分割槽(Move Partition)
- 分割槽表PARTITION table
- 使用Oracle Database 11g建立Interval分割槽表OracleDatabase
- Oracle的分割槽修剪介紹:Partition PruningOracle
- oracle hash partition雜湊分割槽(二)_操作限制Oracle
- oracle11g_system partition系統分割槽Oracle
- MySQL 分割槽表 partition線上修改分割槽欄位MySql
- 【實驗】【PARTITION】RANGE分割槽表合併分割槽
- 【實驗】【PARTITION】RANGE分割槽表增加分割槽