Oracle表动态分区

问题描述

我正在研究Java代码，以根据INSERT_DATETIME字段(时间戳)每15分钟从oracle表中上传数据.我需要根据15分钟的间隔对表进行分区.有没有办法动态地做到这一点(分区).我在oracle SQL开发人员中使用oracle11g.

I'm working on a java code to upload data from oracle tables every 15mins based from the INSERT_DATETIME field (timestamp). I need to partition the tables based on 15minute intervals. is there a way to do it dynamically (partitioning). I'm using oracle11g in oracle SQL developer.

我创建的一个表的一个示例，我想添加一个分区:

A sample of one of the tables I created and I want to add a partition:

CREATE TABLE "PV_TNPM"."TEJAS_CARD_REPORT"
(   "INSERT_DATETIME" TIMESTAMP (6) NOT NULL ENABLE,
"NAME" VARCHAR2(100 BYTE),
"IPADDRESS" VARCHAR2(100 BYTE),
"PRODUCTCODE" VARCHAR2(100 BYTE),
"LCTNAME" VARCHAR2(100 BYTE),
"CARDTYPELABEL" VARCHAR2(100 BYTE),
"SOFTWAREVERSION" VARCHAR2(100 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 32768 NEXT 1523712 MINEXTENTS 1 MAXEXTENTS 1017
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;

我对SQL不熟悉，因为上面的代码只是从我创建的表中生成的.任何帮助表示赞赏.谢谢

I'm not familiar with SQL as the above code is just generated from the table I created. Any help is appreciated. thank you

推荐答案

使用间隔分区:

CREATE TABLE TEJAS_CARD_REPORT
(
    "INSERT_DATETIME" TIMESTAMP (6) NOT NULL ENABLE,
    "NAME"         VARCHAR2(100 BYTE),
    "IPADDRESS" VARCHAR2(100 BYTE),
    "PRODUCTCODE" VARCHAR2(100 BYTE),
    "LCTNAME" VARCHAR2(100 BYTE),
    "CARDTYPELABEL" VARCHAR2(100 BYTE),
    "SOFTWAREVERSION" VARCHAR2(100 BYTE)
) partition by range(insert_datetime) interval (interval '15' minute)
(
    partition initial_partition values less than (date '2000-01-01')
);

在插入数据时动态创建分区.

Partitions are dynamically created as data is inserted.

insert into tejas_card_report(insert_datetime) values (timestamp '2000-01-01 00:14:00');
insert into tejas_card_report(insert_datetime) values (timestamp '2000-01-01 00:29:00');
insert into tejas_card_report(insert_datetime) values (timestamp '2000-10-11 00:00:00');

SQL> select partition_name, high_value from dba_tab_partitions where table_name = 'TEJAS_CARD_REPORT';

PARTITION_NAME       HIGH_VALUE
-------------------- --------------------------------------------------------------------------------
SYS_P21516           TIMESTAMP' 2000-10-11 00:15:00'
SYS_P21515           TIMESTAMP' 2000-01-01 00:30:00'
SYS_P21514           TIMESTAMP' 2000-01-01 00:15:00'
INITIAL_PARTITION    TIMESTAMP' 2000-01-01 00:00:00'

在不相关的注释上，避免使用TABLESPACE "SYSTEM".您几乎永远都不想在SYSTEM表空间中存储任何用户或应用程序数据.

On an unrelated note, avoid TABLESPACE "SYSTEM". You almost never want to store any user or application data in the SYSTEM tablespace.

这篇关于Oracle表动态分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！