Oracle numtodsinterval函数

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

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/22205https://www.cndba.cn/hbhe0316/article/22205https://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/22205https://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