有没有一种方法可以动态创建表和一些初始分区? [英] Is there a way to create a table and some initial partitions dynamically?

问题描述

我需要在ORACLE上创建一个表，该表具有24个分区，从当前时间开始，一天中的每个小时一个. 因此，以下脚本将取决于DBA运行它的时间和日期. 如何提供一个考虑当前时间和日期来创建表和分区的动态脚本?

I need to create a table on ORACLE with 24 partitions, one for each hour of the day, starting for current time. So the following script will depend of the time and date the DBA is going to run it. How can I provide a dynamic script that create the table and the partitions considering the current time and date?

DROP TABLE TABLE_NAME CASCADE CONSTRAINTS; 
CREATE TABLE TABLE_NAME
(
  CODE1             NUMBER(9)           DEFAULT ( 0 ),
  CODE2             NUMBER(9)           DEFAULT ( 0 ),
  CODE3             VARCHAR2(50 BYTE)   DEFAULT ( ' ' ),
  VELOCITY          NUMBER(10,3)        DEFAULT ( 0 ),
  REALDATE          TIMESTAMP(6),
  LOCATION          NUMBER(7,3)         DEFAULT ( 0 ),
  VALIDLOCATION     NUMBER(1)           DEFAULT ( 0 ),
  STARTTIME         NUMBER(9)           DEFAULT ( 0 ),
  OUTBOUND          NUMBER(1)           DEFAULT ( 0 ),
  SERVICE_NAME      VARCHAR2(20 BYTE)   DEFAULT ( ' ' ),
  LOCATIONCODE      NUMBER(3)           DEFAULT 0,
  STARTDATE         TIMESTAMP(6),
  CODE4             VARCHAR2(1 BYTE)
)
NOCOMPRESS 
TABLESPACE TABLESPACE_NAME
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            BUFFER_POOL      DEFAULT
           )
PARTITION BY RANGE (REALDATE)
(  
  PARTITION TABLE_NAME_2016031612 VALUES LESS THAN (TIMESTAMP' 2016-03-16 13:00:00'),
  PARTITION TABLE_NAME_2016031613 VALUES LESS THAN (TIMESTAMP' 2016-03-16 14:00:00'),
  PARTITION TABLE_NAME_2016031614 VALUES LESS THAN (TIMESTAMP' 2016-03-16 15:00:00'),
  PARTITION TABLE_NAME_2016031615 VALUES LESS THAN (TIMESTAMP' 2016-03-16 16:00:00'),
  PARTITION TABLE_NAME_2016031616 VALUES LESS THAN (TIMESTAMP' 2016-03-16 17:00:00'),
  PARTITION TABLE_NAME_2016031617 VALUES LESS THAN (TIMESTAMP' 2016-03-16 18:00:00'),
  PARTITION TABLE_NAME_2016031618 VALUES LESS THAN (TIMESTAMP' 2016-03-16 19:00:00'),
  PARTITION TABLE_NAME_2016031619 VALUES LESS THAN (TIMESTAMP' 2016-03-16 20:00:00'),
  PARTITION TABLE_NAME_2016031620 VALUES LESS THAN (TIMESTAMP' 2016-03-16 21:00:00'),
  PARTITION TABLE_NAME_2016031621 VALUES LESS THAN (TIMESTAMP' 2016-03-16 22:00:00'),
  PARTITION TABLE_NAME_2016031622 VALUES LESS THAN (TIMESTAMP' 2016-03-16 23:00:00'),
  PARTITION TABLE_NAME_2016031623 VALUES LESS THAN (TIMESTAMP' 2016-03-17 00:00:00'),
  PARTITION TABLE_NAME_2016031700 VALUES LESS THAN (TIMESTAMP' 2016-03-17 01:00:00'),
  PARTITION TABLE_NAME_2016031701 VALUES LESS THAN (TIMESTAMP' 2016-03-17 02:00:00'),
  PARTITION TABLE_NAME_2016031702 VALUES LESS THAN (TIMESTAMP' 2016-03-17 03:00:00'),
  PARTITION TABLE_NAME_2016031703 VALUES LESS THAN (TIMESTAMP' 2016-03-17 04:00:00'),
  PARTITION TABLE_NAME_2016031704 VALUES LESS THAN (TIMESTAMP' 2016-03-17 05:00:00'),
  PARTITION TABLE_NAME_2016031705 VALUES LESS THAN (TIMESTAMP' 2016-03-17 06:00:00'),
  PARTITION TABLE_NAME_2016031706 VALUES LESS THAN (TIMESTAMP' 2016-03-17 07:00:00'),
  PARTITION TABLE_NAME_2016031707 VALUES LESS THAN (TIMESTAMP' 2016-03-17 08:00:00'),
  PARTITION TABLE_NAME_2016031708 VALUES LESS THAN (TIMESTAMP' 2016-03-17 09:00:00'),
  PARTITION TABLE_NAME_2016031709 VALUES LESS THAN (TIMESTAMP' 2016-03-17 10:00:00'),
  PARTITION TABLE_NAME_2016031710 VALUES LESS THAN (TIMESTAMP' 2016-03-17 11:00:00'),
  PARTITION TABLE_NAME_2016031711 VALUES LESS THAN (TIMESTAMP' 2016-03-17 12:00:00')
)
NOCACHE
NOPARALLEL
MONITORING;

推荐答案

您可以通过基于sysdate构建SQL语句来尝试一些动态SQL:

You can try with some dynamic SQL, by building a SQL statement based on sysdate:

declare
    vSQL            varchar2(32767);
    vPartitions     varchar2(32767);
begin
    select listagg('PARTITION TABLE_NAME_' || to_char(sysdate + level/24, 'yyyymmddhh24') ||
                   ' VALUES LESS THAN (TIMESTAMP''' || to_char(sysdate + (level+1)/24, 'yyyy-mm-dd hh24') || ':00:00'')'
                   , ', ') within group (order by level) 
    into vPartitions
    from dual
    connect by level <= 24;
    --
    vSQL := q'[ CREATE TABLE TABLE_NAME
                (
                  CODE1             NUMBER(9)           DEFAULT ( 0 ),
                  CODE2             NUMBER(9)           DEFAULT ( 0 ),
                  CODE3             VARCHAR2(50 BYTE)   DEFAULT ( ' ' ),
                  VELOCITY          NUMBER(10,3)        DEFAULT ( 0 ),
                  REALDATE          TIMESTAMP(6),
                  LOCATION          NUMBER(7,3)         DEFAULT ( 0 ),
                  VALIDLOCATION     NUMBER(1)           DEFAULT ( 0 ),
                  STARTTIME         NUMBER(9)           DEFAULT ( 0 ),
                  OUTBOUND          NUMBER(1)           DEFAULT ( 0 ),
                  SERVICE_NAME      VARCHAR2(20 BYTE)   DEFAULT ( ' ' ),
                  LOCATIONCODE      NUMBER(3)           DEFAULT 0,
                  STARTDATE         TIMESTAMP(6),
                  CODE4             VARCHAR2(1 BYTE)
                )
                NOCOMPRESS 
                TABLESPACE TABLESPACE_NAME
                PCTUSED    40
                PCTFREE    10
                INITRANS   1
                MAXTRANS   255
                STORAGE    (
                            BUFFER_POOL      DEFAULT
                           )
                PARTITION BY RANGE (REALDATE)
                (]' || vPartitions ||
                ' )
                NOCACHE
                NOPARALLEL
                MONITORING';    
    execute immediate 'DROP TABLE TABLE_NAME CASCADE CONSTRAINTS'; 
    execute immediate vSQL;
end;

这篇关于有没有一种方法可以动态创建表和一些初始分区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！