News

  1. Home
  2. All posts
All posts, Oracle by Team Ajara

Partitioning table based on Month

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

 

Range partitions

Creating a table with Monthly Partitions

CREATE TABLE “FORM_STG”.”QPT”
( “NBR” number,
“FORM_DATE” DATE
)
PARTITION BY RANGE (“FORM_DATE”)
(PARTITION “MPT_PJAN_23” VALUES LESS THAN (TO_DATE(‘ 2023-02-01 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ,
PARTITION “MPT_PFEB_23” VALUES LESS THAN (TO_DATE(‘ 2023-03-01 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ,
PARTITION “MPT_PMAR_23” VALUES LESS THAN (TO_DATE(‘ 2023-04-01 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ,
PARTITION “MPT_PAPR_23” VALUES LESS THAN (TO_DATE(‘ 2023-05-01 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ,
PARTITION “MPT_PMAY_23” VALUES LESS THAN (TO_DATE(‘ 2023-06-01 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ,
PARTITION “MPT_PJUN_23” VALUES LESS THAN (TO_DATE(‘ 2023-07-01 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ,
PARTITION “MPT_PJUL_23” VALUES LESS THAN (TO_DATE(‘ 2023-08-01 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ,
PARTITION “MPT_PAUG_23” VALUES LESS THAN (TO_DATE(‘ 2023-09-01 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ,
PARTITION “MPT_PSEP_23” VALUES LESS THAN (TO_DATE(‘ 2023-10-01 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ,
PARTITION “MPT_POCT_23” VALUES LESS THAN (TO_DATE(‘ 2023-11-01 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ,
PARTITION “MPT_PNOV_23” VALUES LESS THAN (TO_DATE(‘ 2023-12-01 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ,
PARTITION “MPT_PDEC_23” VALUES LESS THAN (TO_DATE(‘ 2024-01-01 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” );		 
 Maintenance

Every year-end new partition should be created for the coming year. For example, for the new year 2024, we need to create monthly partitions before 31st of dec -2023.

creating a new monthly range partitions.

set time on
set timing on
Alter table FORM_STG.MPT Add partition MPT_PJAN_24 VALUES LESS THAN
(TO_DATE(‘ 2024-02-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ;
Alter table FORM_STG.MPT Add partition MPT_PFEB_24 VALUES LESS THAN
(TO_DATE(‘ 2024-03-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ;
Alter table FORM_STG.MPT Add partition MPT_PMAR_24 VALUES LESS THAN
(TO_DATE(‘ 2024-04-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ;
Alter table FORM_STG.MPT Add partition MPT_PAPR_24 VALUES LESS THAN
(TO_DATE(‘ 2024-05-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ;
Alter table FORM_STG.MPT Add partition MPT_PMAY_24 VALUES LESS THAN
(TO_DATE(‘ 2024-06-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ;
Alter table FORM_STG.MPT Add partition MPT_PJUN_24 VALUES LESS THAN
(TO_DATE(‘ 2024-07-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ;
Alter table FORM_STG.MPT Add partition MPT_PJUL_24 VALUES LESS THAN
(TO_DATE(‘ 2024-08-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ;
Alter table FORM_STG.MPT Add partition MPT_PAUG_24 VALUES LESS THAN
(TO_DATE(‘ 2024-09-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ;
Alter table FORM_STG.MPT Add partition MPT_PSEP_24 VALUES LESS THAN
(TO_DATE(‘ 2024-10-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ;
Alter table FORM_STG.MPT Add partition MPT_POCT_24 VALUES LESS THAN
(TO_DATE(‘ 2024-11-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ;
Alter table FORM_STG.MPT Add partition MPT_PNOV_24 VALUES LESS THAN
(TO_DATE(‘ 2024-12-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL” ;
Alter table FORM_STG.MPT Add partition MPT_PDEC_24 VALUES LESS THAN
(TO_DATE(‘ 2025-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “FORM_USERS_TBL”;

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=’MPT’
and DI.OWNER=’FORM_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=’MPT’
and DI.OWNER=’FORM_STG’
and DIP.STATUS<>’USABLE’; 2 3 4 5 6no rows selected ===>Means all indexes are in USABLE status

 

See also