Oracle numtodsinterval函数

最新推荐文章于 2024-10-16 12:54:10 发布

hbhe0316

最新推荐文章于 2024-10-16 12:54:10 发布

阅读量825

点赞数

CC 4.0 BY-SA版权

文章标签：大数据 linux python sql unity

本文链接：https://blog.csdn.net/hbhe0316/article/details/122701106

本文详细介绍了如何在Oracle数据库中创建测试表并进行分区，包括按日期范围的分区、设置Interval命令以按不同时间单位自动分区，并展示了插入数据和验证Interval是否生效的过程。通过这些步骤，读者可以深入理解Oracle数据库的分区管理和时间间隔自动分区功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.创建测试表

CREATE TABLE "HBHE"."TT11" 
   (    "MACRO_COMMAND_ID" VARCHAR2(64 BYTE), 
    "COMMAND_COMPLETED_TIME" TIMESTAMP (9)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 4096000
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
  PARTITION BY RANGE ("COMMAND_COMPLETED_TIME") 
 (PARTITION "20210517"  VALUES LESS THAN (TIMESTAMP' 2021-05-18 00:00:00') SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING 
  STORAGE(INITIAL 4096000 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS");

2.查看分区https://www.cndba.cn/hbhe0316/article/22205

https://www.cndba.cn/hbhe0316/article/22205

select partition_name, interval, high_value from dba_tab_partitions where table_owner='HBHE' and table_name = 'TT11' order by partition_position;

3.执行Interval命令

alter table TT11 set interval (numtodsinterval(1,'DAY'));

如果是按年

ALTERTABLE HBHE.TT11 SET INTERVAL (numtoyminterval(1,'year'));

如果是按月https://www.cndba.cn/hbhe0316/article/22205 https://www.cndba.cn/hbhe0316/article/22205 https://www.cndba.cn/hbhe0316/article/22205

ALTERTABLE HBHE.TT11 SET INTERVAL (numtoyminterval(1,'month'));

如果是按日https://www.cndba.cn/hbhe0316/article/22205

alter table HBHE.TT11 set interval (numtodsinterval(1,'DAY'));

如果是按小时:https://www.cndba.cn/hbhe0316/article/22205

alter table HBHE.TT11 set interval (numtodsinterval(1,'HOUR'));

4.插入数据https://www.cndba.cn/hbhe0316/article/22205 https://www.cndba.cn/hbhe0316/article/22205

insert into HBHE.TT11 VALUES (1,'21-MAY-21 09.12.55.081000000 PM');

5.查看是否Interval是否生效

select partition_name, interval, high_value from dba_tab_partitions where table_owner='HBHE' and table_name = 'TT11' order by partition_position;

可以看到SYS_P1240为系统创建的表分区。

oracle,linux