9i分区表自动管理实现方法之一

最新推荐文章于 2020-06-28 09:01:22 发布

cuhutai2494

最新推荐文章于 2020-06-28 09:01:22 发布

阅读量78

点赞数

需求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;

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

转载于:http://blog.itpub.net/76065/viewspace-823744/