All posts, Oracle by Team Ajara

Partitioning table based on Quarter

How to create and maintain a quarterly partitions for a Table?

Range partitions

Creating a table with Quarterly Partitions

CREATE TABLE "STG"."QPT"
   (    "QPT_NBR" NUMBER(*,0),
        "START_DT" DATE
   ) 
  TABLESPACE "QPT_TBS"
  PARTITION BY RANGE ("START_DT")
 ( PARTITION "Q1_2023"  VALUES LESS THAN (TO_DATE(' 2023-04-01 00:00:00', '
SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
  TABLESPACE "QPT_PART_23" ,
 PARTITION "Q2_2023"  VALUES LESS THAN (TO_DATE(' 2023-07-01 00:00:00', '
SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
  TABLESPACE "QPT_PART_23" ,
 PARTITION "Q3_2023"  VALUES LESS THAN (TO_DATE(' 2023-10-01 00:00:00', '
SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  TABLESPACE "QPT_PART_23" ,
 PARTITION "Q4_2023"  VALUES LESS THAN (TO_DATE(' 2024-01-01 00:00:00', '
SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
  TABLESPACE "QPT_PART_25" ,
 PARTITION "AA_COD_PMAX"  VALUES LESS THAN (MAXVALUE) 
  TABLESPACE "QPT_TBS" );

Creating Index

  CREATE INDEX "STG"."QPT_PK" ON "STG"."QPT" ("QPT_NBR")
   TABLESPACE "QPT_INDX"
  PARALLEL 4

ALTER TABLE "STG"."QPT" ADD CONSTRAINT "QPT_PKEY" PRIMARY KEY ("QPT_NBR")
  USING INDEX "STG"."QPT_PK"  ENABLE

creating a new Quarterly range partitions.

set lines 300 pages 300;
set time on;
set timing on;
alter table STG.QPT split partition PMAX AT (TO_DATE
 ('2024-04-01 00:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
  INTO (PARTITION Q1_2024 TABLESPACE QPT_PART_24 , PARTITION PMAX);
alter table STG.QPT split partition PMAX AT (TO_DATE
 ('2024-07-01 00:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
 INTO (PARTITION Q2_2024 TABLESPACE QPT_PART_24 , PARTITION PMAX);
alter table STG.QPT split partition PMAX AT (TO_DATE
 ('2024-10-01 00:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
 INTO (PARTITION Q3_2024 TABLESPACE QPT_PART_24 , PARTITION PMAX);
alter table STG.QPT split partition PMAX AT (TO_DATE
 ('2025-01-01 00:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
  INTO (PARTITION Q4_2024 TABLESPACE QPT_PART_24 , PARTITION PMAX);

Verify table level Indexes status.

column INDEX_NAME for a33
col OWNER for a22select DI.INDEX_NAME,DI.PARTITIONED,DI.OWNER,di.status
from dba_indexes di
where DI.TABLE_NAME=’QPT’
and DI.OWNER=’STG’;no rows selected.

Verify Partition indexes status.

SQL> select DIP.PARTITION_NAME,DIP.INDEX_NAME,DIP.STATUS
from dba_ind_partitions dip,dba_indexes di
where DIP.INDEX_NAME=DI.INDEX_NAME
and DI.TABLE_NAME=’QPT’
and DI.OWNER=’STG’
and DIP.STATUS<>’USABLE’; 2 3 4 5 6no rows selected ===>Means all indexes are in USABLE status

See also