News

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

Yearly partitions in Oracle database

How to create and maintain Yearly partitions in Oracle database.

 

Creation of range partition

Creating a table with Yearly Partitions

CREATE TABLE “IC_OWNER”.”ESTIMATE”
( “ESTIMATE_SK” NUMBER(*,0),
“ESTIMATE_VER_NBR” NUMBER(20,0),
“ESTIMATE_NBR” VARCHAR2(20),
“ORIG_ESTIMATE_DT” DATE,
“VER_NBR” NUMBER(*,0),
“CUR_REC_IND” VARCHAR2(1),
“REC_STRT_DT” DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “TBS_ESTIMATE”
PARTITION BY RANGE (“ORIG_ESTIMATE_DT”)
PARTITION “ESTIMATE_2023” VALUES LESS THAN (TO_DATE(‘ 2024-01-01 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “TBS_ESTIMATE” ,
PARTITION “MAX” VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “TBS_ESTIMATE” )
PARALLEL 3
CREATE UNIQUE INDEX “IC_OWNER”.”IDX_PK_ESTIMATE” ON “IC_OWNER”.”ESTIMATE”
(“ESTIMATE_SK”, “ESTIMATE_VER_NBR”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “TBS_ESTIMATE_INDX”
PARALLEL 10
ALTER TABLE “IC_OWNER”.”ESTIMATE” ADD CONSTRAINT “ESTIMATEQ_PK” PRIMARY KEY
(“ESTIMATE_SK”, “ESTIMATE_VER_NBR”)
USING INDEX “IC_OWNER”.”IDX_PK_ESTIMATE” ENABLE;

Unique Index creation

CREATE UNIQUE INDEX “IC_OWNER”.”ESTIMATE_AK1″ ON “IC_OWNER”.”ESTIMATE” (“Q
UOTE_NBR”, “ORIG_ESTIMATE_DT”, “VER_NBR”, “REC_STRT_DT”)
PCTFREE 1 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “TBS_ESTIMATE_INDX”
PARALLEL 10

Partition Index creation

CREATE INDEX “IC_OWNER”.”ESTIMATE_HSFILE_IND” ON “IC_OWNER”.”ESTIMATE”
(“ESTIMATE_NBR”, “ORIG_ESTIMATE_DT”, “CUR_REC_IND”, “ESTIMATE_SK”)
PCTFREE 5 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “INDX_DATA” LOCAL
(PARTITION “ESTIMATE_2023” NOCOMPRESS
PCTFREE 5 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “INDX_DATA” ,
PARTITION “MAX” NOCOMPRESS
PCTFREE 5 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “INDX_DATA” )
PARALLEL 4;

Maintenance

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

creating a new range partition for the new year

set lines 300 pages 300;
set time on;
set timing on;
alter table IC_OWNER.ESTIMATE split partition MAX AT (TO_DATE(‘2025-01-01 00:00:00′,’SYYYY-MM-DDHH24:MI:SS’,’NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION ESTIMATE_2024 TABLESPACE TBS_ESTIMATE , PARTITION MAX) PARALLEL 4;

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=’ESTIMATE’
and DI.OWNER=’IC_OWNER’;

INDEX_NAME PAR OWNER STATUS
——————————— — ———————- ——–
ESTIMATE_HSFILE_IND YES IC_OWNER N/A
ESTIMATE_BT01 YES IC_OWNER N/A
ESTIMATE_LAST_URC_BM YES IC_OWNER N/A
ESTIMATE_AK1 NO IC_OWNER VALID
IDX_PK_ESTIMATE NO IC_OWNER VALID

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

 

See also