oracle定时添加或删除分区表的分区 存儲過程 增刪分區 操作分區表, 包 pl/sql
本定时创建的是以时间 做为分区表的分区字段 ,字段类型是timestamp
先创建包头:
create or replace package pkg_partition_alter is
/**
date:2010-09-14
author:wanggang
*/
/**
删除分区表中,指定时间段内的分区
####
参数:
v_table_name 分区表名
v_begin_date 删除分区的开始时间 (null 不限定开始时间) 包含
v_end_date 删除分区的结束时间 (null 不限定结束时间) 不包含
####
使用注意:
1.假定分区表中分区是按时间戳 timestamp 字段 来分区的
2.删除的指定时间段的分区,其是根据分区表创建时的less than value时间指定的
3.删除时如果指定 v_begin_date,v_end_date 则删除时是[v_begin_date,v_end_date)区间
4.只有最后一个分区时是无法删除的
**/
procedure drop_partition(v_table_name in varchar2 ,
v_begin_date timestamp default null,
v_end_date timestamp default null);
/**
根据分区信息,以及指定的时间,添加分区
*/
procedure add_partition(v_table_name in varchar2 ,
v_tablespace_name_in in varchar2 default null,
v_begin_date timestamp default null,
v_end_date timestamp);
/**
根据原来的最后分区信息,自动追加分区
*/
procedure append_partition(v_table_name in varchar2 ,v_tablespace_name_in in varchar2 default null,
v_end_date in timestamp);
end;
再创建包的实现部分
create or replace package body pkg_partition_alter is
v_pos number := 11;--截断high_value中信息的字符部分起始位
/**
删除分区表中,指定时间段内的分区
####
参数:
v_table_name 分区表名
v_begin_date 删除分区的开始时间 (null 不限定开始时间) 包含
v_end_date 删除分区的结束时间 (null 不限定结束时间) 不包含
####
使用注意:
1.假定分区表中分区是按时间戳 timestamp 字段 来分区的
2.删除的指定时间段的分区,其是根据分区表创建时的less than value时间指定的
3.删除时如果指定 v_begin_date,v_end_date 则删除时是[v_begin_date,v_end_date)区间
4.只有最后一个分区时是无法删除的
**/
procedure drop_partition(
v_table_name in varchar2 ,
v_begin_date timestamp default null,
v_end_date timestamp default null
)
is
---分区表信息
cursor cur_utp(
v_table_name in user_tab_partitions.table_name%TYPE
)
is
select utp.table_name,utp.tablespace_name,utp.partition_name,utp.high_value,utp.high_value_length,utp.partition_position from user_tab_partitions utp
where utp.table_name = UPPER(v_table_name)
order by utp.partition_position ;
v_high_value varchar2(255);--less than value信息
v_partition_max_date timestamp;----less than value信息的 timestamp表示形式
v_sqlexec VARCHAR2(2000); --DDL语句变量
v_count number := 0;
begin
for utp in cur_utp(v_table_name) loop
/* dbms_output.put_line('table_name:'|| utp.table_name ||' tablespace_name:'|| utp.tablespace_name||' partition_name:'||utp.partition_name
||' high_value:'||utp.high_value ||' high_value_length:'||utp.high_value_length||' partition_position:'||utp.partition_position);
*/
v_high_value := substr(utp.high_value , v_pos, (utp.high_value_length - v_pos) );
v_partition_max_date := to_timestamp(v_high_value , 'syyyy-mm-dd hh24:mi:ss.ff');
-- dbms_output.put_line(v_partition_max_date);
if ((v_begin_date is null or v_begin_date <= v_partition_max_date)
and (v_end_date is null or v_partition_max_date < v_end_date)) then
-- dbms_output.put_line('v_begin_date > v_partition_max_date = true:');
-- dbms_output.put_line('v_begin_date:'||to_char(v_begin_date,'syyyy-mm-dd hh24:mi:ss.ff')||' v_partition_max_date:'||to_char(v_partition_max_date,'syyyy-mm-dd hh24:mi:ss.ff'));
v_sqlexec := 'ALTER TABLE ' || utp.table_name || ' DROP PARTITION ' ||
utp.partition_name;
-- dbms_output.put_line('删除' || utp.table_name || '表分区=' || v_SqlExec);
DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
v_count := v_count + 1;
end if;
------判断是否不需要再删除 ,跳出循环
if(v_partition_max_date > v_end_date) then
-- dbms_output.put_line('exit start');
exit;
end if;
end loop;
dbms_output.put_line('drop partition count:'||v_count);
end;
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
/**
根据分区信息,以及指定的时间,添加分区
*/
procedure add_partition(v_table_name in varchar2 ,
v_tablespace_name_in in varchar2 default null,
v_begin_date timestamp default null,
v_end_date timestamp)
is
-- Local variables here
i integer;
---分区表信息
cursor cur_utp(
v_table_name in user_tab_partitions.table_name%TYPE
)
is
select * from (select utp.table_name,utp.tablespace_name,utp.partition_name,utp.high_value,utp.high_value_length,utp.partition_position from user_tab_partitions utp
where utp.table_name = UPPER(v_table_name)
order by utp.partition_position desc) utp
where rownum = 1;
v_high_value varchar2(255);--less than value信息
v_partition_max_date timestamp;----less than value信息的 timestamp表示形式
v_sqlexec VARCHAR2(2000); --DDL语句变量
v_count number := 0;
v_interver number := 1;--步长间隔 单位(天)
v_part_name_header varchar2(20) :='part_';
v_part_name varchar2(2000);--分区名
v_partition_num number := 0;
v_tablespace_name varchar2(200) ;
v_end_date_inner timestamp;
begin
v_interver := 1/24;
v_part_name_header := upper(v_part_name_header);
v_end_date_inner := trunc(v_end_date);
--取值
for utp in cur_utp(v_table_name) loop
dbms_output.put_line('table_name:'|| utp.table_name ||' tablespace_name:'|| utp.tablespace_name||' partition_name:'||utp.partition_name
||' high_value:'||utp.high_value ||' high_value_length:'||utp.high_value_length||' partition_position:'||utp.partition_position);
v_high_value := substr(utp.high_value , v_pos, (utp.high_value_length - v_pos) );
v_partition_max_date := to_timestamp(v_high_value , 'syyyy-mm-dd hh24:mi:ss.ff');
if(v_begin_date > v_partition_max_date) then
v_partition_max_date := v_begin_date;
end if;
v_part_name := utp.partition_name ;
--如果没有给默认值
if(v_tablespace_name_in is null) then
-- v_tablespace_name
v_tablespace_name := utp.tablespace_name;
else
v_tablespace_name := v_tablespace_name_in;
end if;
dbms_output.put_line(v_partition_max_date);
end loop;
if( v_part_name_header = substr(v_part_name,1,length(v_part_name_header))) then
dbms_output.put_line('expect part_name header:'||v_part_name);
v_partition_num := to_number(substr(v_part_name,length(v_part_name_header)+1,length(v_part_name)));
v_partition_num := v_partition_num + 1;
else
dbms_output.put_line('not expect part_name header user default:'||v_part_name);
v_partition_num := 0;
end if;
i := 0;
v_partition_max_date := v_partition_max_date + v_interver;
dbms_output.put_line('v_partition_max_date:'||to_char(v_partition_max_date, 'syyyy-mm-dd hh24:mi:ss.ff')||' v_end_date_inner:' ||to_char(v_end_date_inner, 'syyyy-mm-dd hh24:mi:ss.ff') );
while v_partition_max_date < v_end_date_inner
loop
v_SqlExec := 'ALTER TABLE ' || v_table_name || ' ADD PARTITION ' ||
v_part_name_header || (v_partition_num + i)
|| ' values less than(TIMESTAMP'''||
to_char(v_partition_max_date, 'syyyy-mm-dd hh24:mi:ss.ff')
|| ''') TABLESPACE ' || v_tablespace_name;
dbms_output.put_line('创建 表分区' || i || '=' || v_SqlExec);
--alter table IP_CONNRATE_LIMITEX_LOG add partition part_0002 values less than(TIMESTAMP'2010-08-31 00:00:00.000000') tablespace NASP_IP_LOG_TABLESPACE;
DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
v_partition_max_date := v_partition_max_date + v_interver;
i := i +1;
end loop;
v_count := v_count + i;
dbms_output.put_line('add partition count:'||v_count);
end;
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
/**
根据原来的最后分区信息,自动追加分区
*/
procedure append_partition(v_table_name in varchar2 ,v_tablespace_name_in in varchar2 default null,
v_end_date in timestamp)
is
begin
add_partition(v_table_name, v_tablespace_name_in, null,v_end_date);
end;
end;
下面是调用的示例
在job中调用 设定 15天调用一次,为安全,创建和删除 30天的分区
pkg_partition_alter.drop_partition ('test_partition_log',null,sysdate - 30);
--可以指定表空间
pkg_partition_alter.append_partition('test_partition_log','TEST_TABLESPACE',sysdate + 30);
job( oracle)
begin
sys.dbms_job.submit(job => :job,
what => pkg_partition_alter.drop_partition ('test_partition_log',null,sysdate - 30);pkg_partition_alter.append_partition('test_partition_log','TEST_TABLESPACE',sysdate + 30);',
next_date => to_date('05-10-2010', 'dd-mm-yyyy'),
interval => 'TRUNC(SYSDATE + 15)');
commit;
end;
/
参见我的163博客:http://1985wanggang.blog.163.com/blog/static/7763833201081455532590/
分享到：
相关推荐
表及索引的定义操作 视图、同义词和序列 簇与分区 复杂查询语句的使用 一些高级的用法 安全管理 其它一些常见问题及技巧 常用技巧 Oracle PL/SQL基础 PL/SQL 块结构和组成元素 PL/SQL 处理流程 光标的使用 错误处理 ...
6.4表分区的几种类型及操作方法 6.5有关表分区的一些维护性操作 第5章 PL/SQL程序设计 1. PL/SQL简介 2. PL/SQL基础 2.1声明 2.2条件控制 2.3循环控制 2.4游标 2.5动态SQL 3.创建存储过程 4.创建自定义...
①:增/删/改 ②:约束 ③:一些查询 五.锁,表分区 ①:锁的概念 ②:并发问题 ③:锁机制 ④:锁的类型 ②:常见分区 六.数据库对象管理 ①:同义词 ②:序列 ③:视图 ④:索引 七.PL/SQL基础 ①:什么是PL/SQL ...
第一章：oracle体系结构 ORACLE 10G的卸载与安装 客户端连接工具 Oracle 10g体系结构 第二章：sql基础 ORACLE数据类型 ...分区表操作 分区表的管理 第十二章：数据库优化 概述 SQL的优化
本文档详细讲解ORACLE数据库的内部体系结构，包含数据库实例，物理存储结构，逻辑存储结构，配置与维护，非常详细地介绍了数据库模式对象的应用于管理，包括表，约束，索引，分区表，分区索引，视图，簇，外部表，...
24.2. 创建分区表 161 24.3. 范围分区（Range） 161 24.4. 列表分区(List) 164 24.5. 散列分区(Hash) 165 24.6. 组合范围散列分区 167 24.7. 复合范围散列分区 168 24.8. 维护表分区 169 二十四、 PL/SQL基础 173 ...
第三章 锁和表分区； 第四章 数据库对象； 第五章 Oracle 中的 OOP 概念； 第六章 PL/SQL 简介； 第七章 异常和游标管理； 第八章 子程序和程序包； 第九章 数据库触发器和内置程序包； 第十章 集合和成员函数。
§5.2.4 维护表分区和索引分区 167 §5.3 簇与分区有关的数据字典 169 §5.3.1 分区、簇数据字典列表 169 §5.3.2 基本的分区、簇信息查询 169 第六章 使用SQL 进行数据操作 170 §6.1 INSERT操作 170 §6.1.1 用...
一、SQL&PL SQL 1.怎么样大批量的更新数据而不影响正常业务 2.怎么对IN子查询使用绑定变量 3.并发容易出现的问题与并发控制 4.怎么使用object与record类型返回表类型数据 5.怎么样在业务繁忙时期正确的创建表约束...
作者通过总结各自多年的软件开发和教学培训经验，与大家分享了掌握Oracle SQL所独有的丰富功能的技巧所在，内容涵盖SQL执行、联结、集合、分析函数、子句、事务处理等多个方面。读者可以学习到以下几个方面的技巧：...
主要包括各种类型的表创建以及适用情形，如外部表、分区表、嵌套表、全局临时表等；完整性约束的管理；索引，包括B树索引、基于函数的索引、位图索引、反向索引、降序索引、压缩索引等的使用方法及其适用情形等。在...
Oracle常用命令4（表分区） Oracle常用命令5（同义词、视图、索引） Oracle常用命令6（PL/SQL） Oracle常用命令7（游标） Oracle常用命令8（过程） Oracle常用命令9（函数） Oracle常用命令10（程序包） ...
简单来说是本身可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据运行新增、截取、更新、删除等操作。 常见的数据模型 1. 层次结构模型: 层次结构模型实质上是一种有根结点的定向有序树，IMS...
通过PL/SQL 自己手动生成DDL，不过目前我只写了 表、主键、注释、索引。其他的分区、约束还没写。后期加上 缺陷：每个字段后面都有","需要手动剔除，修改过后的已上传，csdn中不知道怎么修改，只好重新上传
第35章 过程、函数与程序包 第36章 使用本地动态sql 和dbms_sql 第37章 pl/sql 调整 第ⅴ部分 对象关系数据库 第38章 实现对象类型、对象视图和方法 第39章 收集器(嵌套表和可变数组) 第40章 使用大...
《Oracle Database 11g初学者指南》能使读者快捷地掌握Oracle Database 11g的基础知识。通过自我评估教程，介绍了核心数据库技术、管理员职责、高可用性以及大型数据库特性。《Oracle Database 11g初学者指南》带领...
5.7 如何创建存储过程以及创建存储过程的原因 146 5.8 函数的创建和使用 149 5.9 调用PL/SQL程序 151 5.10 本章测验 152 第6章 数据库管理员 153 6.1 了解DBA的工作 154 6.2 执行日常操作 154 6.2.1 体系结构...