oracle自动创建时间分区SP

最新推荐文章于 2024-04-20 18:40:30 发布
最新推荐文章于 2024-04-20 18:40:30 发布
阅读量3.7k 收藏 1
点赞数
分类专栏： oracle11G 
该分区以时间分区
<p>create or replace package PKG_AutoPartitions is</p><p> v_TableSpace VARCHAR2(30):= 'XXXUSER'; --命名空间 上线要修改成XXXUSER
 
 procedure P_RepairAutoPartition(p_EndPartitionDate in varchar2) ;
procedure P_RepairAutoMergePartition(p_BeginPartitionDate in varchar2,p_EndPartitionDate in varchar2,p_NewPartitionName in varchar2) ;
procedure P_AutoMergePartition(p_TableName in varchar2,p_BeginPartitionDate in varchar2,p_EndPartitionDate in varchar2,p_NewPartitionName in varchar2);                      
 procedure P_AutoPartition(p_TableName in varchar2,p_TableSpace in varchar2,p_PrePartitionName in varchar2, p_EndPartitionDate in varchar2, p_InterverType in varchar2, p_Interver in number);
 
end PKG_AutoPartitions;
/
  /*
      名称：PKG_AutoPartitions
      描述：XXX 系统 oracle添加分区表分区的存储过程     P_AutoPartition 是最根本的存储过程
      本存储过程支持按照年、天、月进行分区，
      使用方法：
     维修 P_AutoPartition('T_PHONEDEAL','XXXUSER','T_PHONEDEAL',p_EndPartitionDate,'M',1);
     
  */</p><p>create or replace package body PKG_AutoPartitions is</p><p>  procedure P_RepairAutoPartition(p_EndPartitionDate in varchar2) AS 
  BEGIN
   P_AutoPartition('T_PHONEDEAL',PKG_AutoPartitions.v_TableSpace,'T_PHONEDEAL',p_EndPartitionDate,'M',1);
  END P_RepairAutoPartition;
  
  procedure P_RepairAutoMergePartition(p_BeginPartitionDate in varchar2,p_EndPartitionDate in varchar2,p_NewPartitionName in varchar2) AS 
  BEGIN
   P_AutoMergePartition('T_PHONEDEAL',p_BeginPartitionDate,p_EndPartitionDate,p_NewPartitionName);
  END P_RepairAutoMergePartition;
  
  procedure P_AutoMergePartition(p_TableName in varchar2,p_BeginPartitionDate in varchar2,p_EndPartitionDate in varchar2,p_NewPartitionName in varchar2) AS
  v_SqlExec            VARCHAR2(2000); --DDL语句变量
  v_HighValue         varchar2(255); --less than value信息
   v_HighValuePartitionDate timestamp; ----less than value信息的 timestamp表示形式
   v_PartitionNames         varchar2(1500); --要合并的分区名字，逗号分隔
   v_PartitionCount        number := 0; --要合并的分区的个数</p><p>    
     v_BeginPartitionDate timestamp; ----分区的开始时间、
      v_EndPartitionDate timestamp; ----分区的结束时间
      </p><p>      
      v_CurrenntPartitionNameBox VARCHAR2(30);  --用于存放当前分区
      
      v_PartitionNameTemp01 VARCHAR2(30):= 'PartitionNameTemp_01'; 
      
     v_PartitionNameTemp02 VARCHAR2(30):= 'PartitionNameTemp_02'; 
     
     
    ---分区表信息
  cursor cur_utp(v_TableName 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(p_TableName)
             order by utp.partition_position asc;
   
  BEGIN
 
 
    v_BeginPartitionDate :=to_timestamp(p_BeginPartitionDate,  'syyyy-mm-dd hh24:mi:ss.ff'); 
    v_EndPartitionDate :=to_timestamp(p_EndPartitionDate,  'syyyy-mm-dd hh24:mi:ss.ff'); 
     --读取满足要求的分区
   for utp in cur_utp(p_TableName) loop
    v_HighValue         := substr(utp.high_value,
                                   11,
                                   10);                         
    v_HighValuePartitionDate := to_timestamp(v_HighValue,
                                         'syyyy-mm-dd hh24:mi:ss.ff');                                                                   
    
    IF ( v_HighValuePartitionDate>= v_BeginPartitionDate and v_HighValuePartitionDate<= v_EndPartitionDate) then
      if(v_PartitionCount=1) then 
          v_CurrenntPartitionNameBox :=utp.partition_name;
      end if;
      
      if(v_PartitionCount>=2) then 
         if(mod(to_number(v_PartitionCount),2)=1) then 
             v_PartitionNames :=v_CurrenntPartitionNameBox||','||utp.partition_name;
             v_CurrenntPartitionNameBox :=v_PartitionNameTemp01;
             
             v_SqlExec := 'ALTER TABLE ' || p_TableName || ' MERGE PARTITIONS ' ||
                         v_PartitionNames ||
                         ' INTO PARTITION ' ||v_CurrenntPartitionNameBox;
                         
                         dbms_output.put_line('合并 表分区' || v_PartitionNames ||'到 ' || v_CurrenntPartitionNameBox||'='||v_SqlExec);
            
                         DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
             
         else
             v_PartitionNames :=v_CurrenntPartitionNameBox||','||utp.partition_name;
             v_CurrenntPartitionNameBox :=v_PartitionNameTemp02;
             
             v_SqlExec := 'ALTER TABLE ' || p_TableName || ' MERGE PARTITIONS ' ||
                         v_PartitionNames ||
                         ' INTO PARTITION ' ||v_CurrenntPartitionNameBox;
                         
                         dbms_output.put_line('合并 表分区' || v_PartitionNames ||'到 ' || v_CurrenntPartitionNameBox||'='||v_SqlExec);
            
                         DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
         
         end if;
      
      end if;
      v_PartitionCount:=v_PartitionCount+1;  
    END IF;          
    end loop;  
    IF (v_PartitionCount=0) THEN 
       dbms_output.put_line('没有找到要合并的分区');
    ELSE
     v_SqlExec := 'ALTER TABLE ' || p_TableName || ' RENAME PARTITION ' ||
                     v_CurrenntPartitionNameBox ||
                     ' TO  ' ||p_NewPartitionName;
                     
       dbms_output.put_line('修改 表分区' || v_CurrenntPartitionNameBox ||'到 ' || p_NewPartitionName||'='||v_SqlExec);
        
        DBMS_Utility.Exec_DDL_Statement(v_SqlExec);</p><p>    END IF;
 </p><p>  END P_AutoMergePartition;
  
  
   procedure P_AutoPartition(p_TableName in varchar2,p_TableSpace in varchar2,p_PrePartitionName in varchar2, p_EndPartitionDate in varchar2, p_InterverType in varchar2, p_Interver in number) AS
   
   v_Interver           number := 1; --步长间隔 单位(月)
   v_InterverType       VARCHAR2(1) := 'M'; --Y(年)/D(天)M(月)/D(天) 方便扩展</p><p>   v_SqlExec            VARCHAR2(2000); --DDL语句变量
   
   v_TableSpace VARCHAR2(30):= PKG_AutoPartitions.v_TableSpace; --命名空间 上线要修改成XXXUSER
   v_PrePartitionName           VARCHAR2(30); --DDL语句变量
   v_EndPartitionDate     timestamp;
   v_PartitionName           VARCHAR2(50); --DDL语句变量   
   v_HighValue         varchar2(255); --less than value信息
   v_HighValuePartitionMaxDate timestamp; ----less than value信息的 timestamp表示形式
  
   ---分区表信息
  cursor cur_utp(v_TableName 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(p_TableName)
             order by utp.partition_position desc) utp
     where rownum = 1;
   BEGIN</p><p>     IF (UPPER(p_InterverType)='Y' or UPPER(p_InterverType)='M' or UPPER(p_InterverType)='D') THEN 
         v_InterverType :=p_InterverType; 
     END IF;
    
     IF (p_Interver IS NULL) THEN 
         v_Interver :=p_Interver; 
     END IF;  
  
    v_EndPartitionDate := to_timestamp(p_EndPartitionDate, 'syyyy-mm-dd hh24:mi:ss.ff');
    
    --获取最大分区时间 
   for utp in cur_utp(p_TableName) loop
    v_HighValue         := substr(utp.high_value,
                                   11,
                                   10);                         
    v_HighValuePartitionMaxDate := to_timestamp(v_HighValue,
                                         'syyyy-mm-dd hh24:mi:ss.ff');                                                                   
    
    
    
     --取前缀
    if (p_PrePartitionName is null) then
      v_PrePartitionName := SUBSTRB(utp.partition_name,1,INSTR(utp.partition_name,'_', 1,1)-1) ;
    else
      v_PrePartitionName := p_PrePartitionName;
    end if;
    v_PrePartitionName := UPPER(v_PrePartitionName);
   
   --取表空间
    if (p_TableSpace is null) then
      v_TableSpace :=utp.tablespace_name;
    else
      v_TableSpace := p_TableSpace;
    end if;
    v_TableSpace := UPPER(v_TableSpace);
                       
    end loop;  
    </p><p>    IF (v_InterverType='Y') THEN 
           v_EndPartitionDate:=  To_Date(to_char(v_EndPartitionDate, 'yyyy'), 'yyyy');--获取本年  
           
    ELSIF (v_InterverType='M') THEN
          v_EndPartitionDate:=trunc(add_months(last_day(v_EndPartitionDate), -1) + 1);--获取本月第一天   
    ELSIF (v_InterverType='D') THEN 
          v_EndPartitionDate :=  to_timestamp(p_EndPartitionDate, 'syyyy-mm-dd hh24:mi:ss.ff')  ;
    END IF;
   
                       
    IF ( v_HighValuePartitionMaxDate>= v_EndPartitionDate) then
       dbms_output.put_line('没有分区可以添加');
    ELSE
    
    while v_HighValuePartitionMaxDate < v_EndPartitionDate loop
         IF (v_InterverType='Y') THEN 
               v_HighValuePartitionMaxDate := add_months(v_HighValuePartitionMaxDate, 12*v_Interver);  
               v_HighValuePartitionMaxDate :=To_Date(to_char(v_HighValuePartitionMaxDate, 'yyyy')||'0101', 'yyyymmdd');
               v_PartitionName :=v_PrePartitionName ||'_' || to_char(v_HighValuePartitionMaxDate,'yyyy');
        ELSIF (v_InterverType='M') THEN
              v_HighValuePartitionMaxDate := add_months(v_HighValuePartitionMaxDate, v_Interver);  
              v_PartitionName :=v_PrePartitionName ||'_' || to_char(v_HighValuePartitionMaxDate,'yyyymm');
        ELSIF (v_InterverType='D') THEN 
               v_HighValuePartitionMaxDate := v_HighValuePartitionMaxDate+ v_Interver;
               v_PartitionName :=v_PrePartitionName ||'_' || to_char(v_HighValuePartitionMaxDate,'yyyymmdd');
        END IF;
        
        
        
        v_SqlExec := 'ALTER TABLE ' || p_TableName || ' ADD PARTITION ' ||
                     v_PartitionName ||
                     ' values less than(TIMESTAMP''' ||
                     to_char(v_HighValuePartitionMaxDate, 'syyyy-mm-dd hh24:mi:ss.ff') ||
                     ''') TABLESPACE ' || v_TableSpace;
        dbms_output.put_line('创建 表分区' || v_PrePartitionName ||'_' || to_char(v_HighValuePartitionMaxDate,'yyyymmdd') || '=' || v_SqlExec);
        
        DBMS_Utility.Exec_DDL_Statement(v_SqlExec);</p><p>      
    end loop;
    
    END IF;
  
  END P_AutoPartition;</p><p>
 
 
end PKG_AutoPartitions;
/
</p>
目光比月se寂寞
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql timestamp分区,时间戳上的MySQL表分区
weixin_36422707的博客
01-19 707
I have partitioned a table (because of an out of memory error - table got too big). I have partitioned it on a timestamp column as shown below:CREATE TABLE test (fname VARCHAR(50) NOT NULL,lname VARC...
oracle按时间戳创建分区
ma_anjun的博客
03-08 2103
​一般一张表超过2G的大小，ORACLE是推荐使用分区表的。pv表、接口调用记录表通常增长较快，且通常包含创建时间字段。创建时间多用date类型或者timestamp时间戳类型。关于按date类型分区，网上资料一搜一大把，这里就不详细介绍了。但是，关于按timestamp类型分区，网上资料就很少啦。其实也很简单，跟date类型很类似，估计是网上各位大佬懒得说才难以找到。这里我做简要记录，以便日后参...
Oracle练习系列一：表自动分区
最新发布
2301_79422212的博客
04-20 579
Oracle12.2版本以后，通过AUTOMATIC关键字可实现插入一个不存在的分区键，并自动分区。Oracle12.2版本前，如果LIST分区插入一个不存在的分区键，将会报错。查看TEST_PART表分区情况，发现自动多了一个分区，插入一条不存在的分区键验证自动分区功能​​​​​​​。将TEST_PART表修改为自动分区表​​​​​​​。修改TEST_PART表为不自动分区​​​​​​​。查看TEST_PART表是否为自动分区。此时可以插入该数据​​​​​​​。微信公众号：今晚穿越zhibo。
oracle分区表创建（自动按年、月、日分区）实战
力哥讲技术
03-12 9051
前言：工作中有一张表一年会增长100多万的数据，量虽然不大，可是表字段多，所以一年下来也会达到 1G，而且只增不改，故考虑使用分区表来提高查询性能，提高维护性。如果已经存在的表需要改分区表，就需要将当前表 rename后，再创建新表，然后复制数据到新表，然后删除旧表就可以了。如果开启了这个选项，就会把这行从这个分区中 delete 掉，并加到更新后所属的分区，此时就会发生 rowid 的改变。2、这张表主要是查询，而且可以按分区查询，只会修改当前最新分区的数据，对以前的不怎么做删除和修改。
Oracle中创建默认时间戳的表,Oracle创建按时间戳分区表
weixin_33586594的博客
04-05 836
1 建分区表-- Create tablecreate table PARTITION_TABLE(field1 INTEGER,field2 VARCHAR2(32),field3 INTEGER,)partition by range(field3)interval (86400)(partition p1 values less than (1431964800) ) nolog...
oracle存储过程创建表分区实例
09-10
Oracle存储过程创建表分区实例 Oracle存储过程是指在Oracle数据库管理系统中的一种编程手段，它允许用户根据自己的需求创建出符合自己业务逻辑的数据库处理程序。在 Oracle中，存储过程可以用来创建表分区实例，...
Oracle开发之报表函数
12-16
方法① 是一种直观但相对低效的方法，它首先计算每个分区（如地区）的销售总额，然后计算每个客户在这个分区内的销售额占比： ```sql select all_sales.*, 100 * round(cust_sales / region_sales, 2) || '%' ...
linux安装oracle11g.txt
07-24
Linux下安装Oracle11g服务器 安装环境 Linux服务器：SuSe10 sp2 64位 Oracle服务器：Oracle11gR2 64位 系统要求 ...交换空间 一般为内存的2倍，例如：1G的内存可以设置swap 分区为3G大小 硬盘 5G以上
oracle 精确到小数秒的timestamp类型分区表
敖尔其楞的专栏
12-14 2652
CREATE TABLE TEST_CARD(ID NUMBER(19) NOT NULL,CREATEDATE TIMESTAMP(6)) 2 PARTITION BY RANGE (CREATEDATE)(PARTITION P201104 VALUES LESS THAN (TIMESTAMP' 2011-04-01 14:30:30.3333'),PARTITION P201207
oracle自动建立表分区脚本
08-23
oracle自动建立表分区脚本 适合oracle数据库dba使用 包含详细的操作说明
oracle自动分区重命名.txt
05-30
oracle按时间自动分区后，将自分区的系统名根据时间重命名。如：SYS_P20000重命名为P20190101。 输入两个参数表名 +类型 1：日 2：月 3：年 默认日期 把后面注释的执行语句释放，测试打印的时候注释了，上传的时候忘了 切记：没数据的子分区会被删除
mysql表根据时间戳字段创建表分区的存储过程
奥法vx的笔记
01-05 3622
这篇文章与mysql存储过程按月创建表分区 方式一 写的是相同的内容，只是在表分区创建方式上不一样。 标题已经写的很清楚了，直接贴sql语句啦。-- 普通添加第一个表分区语句 ALTER TABLE iot_divice_info PARTITION by range(UNIX_TIMESTAMP(createtime))(partition par0 values less than (UNIX_
Oracle表分区(12.2 automatic 表自动分区)
wj的博客
08-21 4859
查看分区数据： select * from test partition(p1) 在Oracle Database 12.2 之前，如果使用列表分区，当插入的数据超过了分区列表值设定，则会抛出异常；而如果存在大量的列表值需要定义，则可能需要一一设置。 在12.2引入的新特性中 - Auto-List Partitioning 可以针对新的列表值，进行自动的分区创建，从而减少了维护的复杂性。 通过以下测试来简单验证一下这个特性的表征,如果是常规的列表分区，在分区缺失时会遇到ORA-14400错误： SQL&
Oracle 创建/调用函数小示例
汉南最後の読書人
05-02 2305
函数与过程非常相似，它也是一种存储在数据库中的命名程序块，并且函数也可以接受零个或多个输入参数。函数与过程的主要区别在于，函数必须有返回值，并且可以作为一个表达式的一部分，函数不能作为一个完整的语句使用，函数返回值的数据类型在创建函数时定义。     # 创建一个求平均数的函数 SQL> create or replace function average(num1 number,num2 n
ORACLE创建按月和按天的自动递增分区
热门推荐
jarniyy的博客
08-30 2万+
原文链接：http://blog.chinaunix.net/uid-21943216-id-4062400.html 一、建按月自增分区表： 1.1建表 SQL> create table month_interval_partition_table (id number,time_col date) partition by range(time_col) 2 interval
oracle11g自动分区使用
u012516914的专栏
06-23 6014
为什么使用自动分区？ 在oracle11g之前，oracle是不支持自动分区功能的，这就可能导致我们系统在运行一段时间之后，就需要看看分区是否创建或者写触发器进行创建分区，否则就会导致数据无法入库或者大量的数据全部都进入了默认分区。维护起来容易出错而且复杂。但oracle11g出现了通过interval自动分区，这就使我们系统在使用分区功能时更加自如。 如何自动创建分区？ or...
关于oracle 11g自动分区+分区改名+定时任务综合使用实现自动分区后可以进行分区查询
LostChris的博客
02-17 6721
一直想用oracle 11g 的interval分区（间断分区），这需要配合分区改名才能让我们可以使用分区查询，因为自动分区出来的分区名会是SYSPxxx这样。。 下面直接代码 drop tablespace bank_data including contents and datafiles; create tablespace bank_data logging datafile
oracle通过 触发器自动创建分区
09-03
Oracle通过使用触发器可以自动创建分区。 触发器是一种在数据库操作之前或之后自动触发的特殊存储过程。通过在表上创建触发器，可以在满足特定条件时执行预定义的操作。 在Oracle数据库中，我们可以设计一个触发器来响应特定的事件，如在插入或更新数据时自动创建新的分区。 首先，我们需要在目标表上创建一个触发器，指定触发器类型为BEFORE INSERT或BEFORE UPDATE。在触发器的主体中，我们可以编写PL/SQL代码来检查当前数据的条件，如日期或特定的指标值，以决定是否需要创建新分区。 如果满足条件，我们可以使用ALTER TABLE语句来创建新的分区。在ALTER TABLE语句中，我们可以指定分区的名称、范围和其他属性。通过使用触发器中的PL/SQL代码来动态生成ALTER TABLE语句，我们可以自动化地创建新的分区。 当插入或更新数据时，触发器会在操作之前执行，根据定义的条件判断是否需要创建新的分区。如果满足条件，触发器会执行ALTER TABLE语句来创建新的分区。 这样，我们可以通过使用触发器在Oracle数据库中自动创建分区。触发器提供了一种灵活和自动化的方法来管理和扩展分区，帮助我们更好地组织和管理数据。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值