9i分割槽表自動管理實現方法之一

需求cmd_sent,track保留最近三個月資料
當前天為2005年3月5日為例

[@more@]

分析:
1 2005年3月20日匯出2004.12分割槽資料

2 2005年3月21日增加2005.5分割槽
exec add_partition_procedure(2);

3 2005年3月20日刪除2004.12分割槽資料
exec drop_partition_procedure(3);

1 備份原有分割槽資料
[oracle@gpsbeta admin]$ cat cmd_sent_bk_by_partition.sh
#!/bin/sh
export ORACLE_HOME=/home/oracle/OraHome1
export ORACLE_SID=orcl
export NLS_LANG=american_america.ZHS16GBK

dateStr=`/bin/date +%Y%m`
dateAgo=`date --date='3 months ago' +%Y%m`

tabWithPar='CMD_SENT'$dateAgo
dmpLocation='/home/oracle/liyong/partitionMgr/cmd_sent/'$tabWithPar
$ORACLE_HOME/bin/exp sm2bk/sm2bk tables=cmd_sent:$tabWithPar file=$dmpLocation.dmp log=$dmpLocation.log rows=y FEEDBACK=10000 buffer=100000000 STATISTICS=NONE
[oracle@gpsbeta admin]$

[oracle@gpsbeta admin]$ cat track_bk_by_partition.sh
#!/bin/sh
export ORACLE_HOME=/home/oracle/OraHome1
export ORACLE_SID=orcl
export NLS_LANG=american_america.ZHS16GBK

dateStr=`/bin/date +%Y%m`
dateAgo=`date --date='3 months ago' +%Y%m`

tabWithPar='TRACK'$dateAgo
dmpLocation='/home/oracle/liyong/partitionMgr/track/'$tabWithPar
$ORACLE_HOME/bin/exp sm2bk/sm2bk tables=track:$tabWithPar file=$dmpLocation.dmp log=$dmpLocation.log rows=y FEEDBACK=10000 buffer=100000000 STATISTICS=NONE
[oracle@gpsbeta admin]$

2 刪除老分割槽過程

實現功能:
今天:2005.3月份 假設設定v_month_no為3 表示為刪除2004年12月份分割槽
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

/********************************
author: liyong
date : 2005-03-08
version: 1.0
功能: 刪除業務表分割槽
v_month_no: 2005.3 假設設定v_month_no為3
表示刪除2004.12分割槽
*********************************/
create or replace procedure drop_partition_procedure(v_month_no in number)
is
--刪除分割槽的年月形如200503
v_cmd_str varchar2(16);
v_track_str varchar2(16);
--刪除分割槽的ddl語句
v_ddl_str varchar2(4000);
begin
select 'cmd_sent'||to_char(add_months(sysdate,-v_month_no),'YYYYMM') into v_cmd_str from dual;
select 'track'||to_char(add_months(sysdate,-v_month_no),'YYYYMM') into v_track_str from dual;
--開始刪除分割槽
v_ddl_str := 'alter table cmd_sent drop partition '||v_cmd_str;
execute immediate v_ddl_str;
v_ddl_str := 'alter table track drop partition '||v_track_str;
execute immediate v_ddl_str;
end;

3 建立新分割槽過程
實現功能:
今天:2005.3 假設設定v_month_no為2 表示新增2005.5分割槽
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

/********************************
author: liyong
date : 2005-03-08
version: 1.0
功能: 為業務表新增分割槽
v_month_no: 2005.3 假設設定v_month_no為2
表示新增2005.5分割槽
*********************************/
create or replace procedure add_partition_procedure(v_month_no in number)
is
v_cmd_str varchar2(16);
v_track_str varchar2(16);
v_ddl_str varchar2(4000);
v_utc varchar2(11);
begin
select 'cmd_sent'||to_char(add_months(sysdate,v_month_no),'YYYYMM') into v_cmd_str from dual;
select 'track'||to_char(add_months(sysdate,v_month_no),'YYYYMM') into v_track_str from dual;
--獲取新分割槽的utc
select smtools.date2utc(trunc(add_months(last_day(sysdate)+1,v_month_no))) into v_utc from dual;
--開始刪除分割槽
v_ddl_str := 'alter table cmd_sent add partition '||v_cmd_str||' values less than ( '||v_utc||' )';
execute immediate v_ddl_str;
v_ddl_str := 'alter table track add partition '||v_track_str||' values less than ( '||v_utc||' )';
execute immediate v_ddl_str;
end;

alter table cmd_sent add partition CMD_SENT200508 values less than (1125504000);

4 系統級呼叫 建立新分割槽 刪除老分割槽
[oracle@stardb1 oas]$ cat /oas/liyong/scheduletask/add_partition_job.sh
#!/bin/sh
export ORACLE_BASE=/oas
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0
export ORACLE_SID=starmap
$ORACLE_HOME/bin/sqlplus -s sm2bk/sm2bk<spool add_partition_procedure.log
exec add_partition_procedure(2);
spool off
exit
!

[oracle@stardb1 oas]$ cat /oas/liyong/scheduletask/drop_partition_job.sh
#!/bin/sh
export ORACLE_BASE=/oas
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0
export ORACLE_SID=starmap
$ORACLE_HOME/bin/sqlplus -s sm2bk/sm2bk<spool drop_partition_procedure.log
exec drop_partition_procedure(3);
spool off
exit
!

設定crontab
[oracle@gpsbeta admin]$ pwd
/home/oracle/liyong/partitionMgr/admin
[oracle@gpsbeta admin]$ ls
add_partition_job.sh drop_partition_job.sh
cmd_sent_bk_by_partition.sh track_bk_by_partition.sh

每月10日12點10分左右執行
10 12 10 * * sh /home/oracle/liyong/partitionMgr/admin/cmd_sent_bk_by_partition.sh
20 12 10 * * sh /home/oracle/liyong/partitionMgr/admin/track_bk_by_partition.sh
30 12 10 * * sh /home/oracle/liyong/partitionMgr/admin/add_partition_job.sh
35 12 10 * * sh /home/oracle/liyong/partitionMgr/admin/drop_partition_job.sh

5 分割槽表維護過程
比如2005年1月份時，04年8月的業務資料就不會再用到
那麼我們可以將04年8月的歷史資料匯出.

首先倒出歷史分割槽cmd_sent200408
1>exp starmap_gd/starmap_gd tables=(cmd_sent:cmd_sent200408) file=cmd_sent0408 STATISTICS=none FEEDBACK=10000 buffer=100000000 log=exp_cmd_sent.log

2>資料庫級刪除歷史分割槽
SQL> show user
USER 為"STARMAP_GD"
SQL> alter table cmd_sent drop partition cmd_sent200408;

表已更改。

假設2005年年底計費,需要04年的資料
那麼首先要在資料庫級建立相應歷史分割槽
透過split一級一級的恢復到2004年8月

3> 資料庫級split 04年9月資料，產生cmd_sent200408分割槽

/********************************
新增新的分割槽cmd_sent200408
1093968000為2004-9-1的utc
********************************/
SQL> alter table cmd_sent
2 split partition cmd_sent200409 at (1093968000)
3 into (partition cmd_sent200408,partition cmd_sent200409);

表已更改。

4>倒入2004年8月曆史資料
imp starmap_gd/starmap_gd tables=(cmd_sent:cmd_sent200408) file=cmd_sent0408 ignore=y indexes=n log=imp_cmd_sent.log
這樣2004年8月的歷史資料已經倒入
SQL> set autotrace on
SQL> select count(*) from cmd_sent partition(cmd_sent200408);

COUNT(*)
----------
6460

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'CMD_SENT_TU_ID_IDX' (NON-UNIQ
UE) (Cost=4 Card=6460)

5 分析表cmd_sent
analyze table cmd_sent estimate statistics sample 15 percent;

6 分割槽表相關資料字典檢視
1> 使用者分割槽表相關資訊
col TABLE_NAME for a20
col PARTITION_NAME for a20
col HIGH_VALUE for a15
select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,NUM_ROWS
from USER_TAB_PARTITIONS
order by TABLE_NAME,PARTITION_NAME;

7 注意
alter user sm23 QUOTA UNLIMITED ON business;