News

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

Partitioning Table based on Weekly

Partitioning Table based on Weekly.

Range Partitions:

Creating a table with weekly Partitions

CREATE TABLE “STG”.”WPT”
( “END_DATE” DATE,
“STATE” VARCHAR2(20),
“NBR” VARCHAR2(20)
) TABLESPACE USERS
PARTITION BY RANGE (“END_DATE”)
(PARTITION “WPT_20230102” VALUES LESS THAN (TO_DATE(‘ 2023-01-02 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230109” VALUES LESS THAN (TO_DATE(‘ 2023-01-09 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230116” VALUES LESS THAN (TO_DATE(‘ 2023-01-16 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230123” VALUES LESS THAN (TO_DATE(‘ 2023-01-23 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230130” VALUES LESS THAN (TO_DATE(‘ 2023-01-30 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230206” VALUES LESS THAN (TO_DATE(‘ 2023-02-06 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230213” VALUES LESS THAN (TO_DATE(‘ 2023-02-13 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230220” VALUES LESS THAN (TO_DATE(‘ 2023-02-20 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230227” VALUES LESS THAN (TO_DATE(‘ 2023-02-27 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230306” VALUES LESS THAN (TO_DATE(‘ 2023-03-06 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230313” VALUES LESS THAN (TO_DATE(‘ 2023-03-13 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230320” VALUES LESS THAN (TO_DATE(‘ 2023-03-20 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230327” VALUES LESS THAN (TO_DATE(‘ 2023-03-27 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230403” VALUES LESS THAN (TO_DATE(‘ 2023-04-03 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230410” VALUES LESS THAN (TO_DATE(‘ 2023-04-10 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230417” VALUES LESS THAN (TO_DATE(‘ 2023-04-17 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230424” VALUES LESS THAN (TO_DATE(‘ 2023-04-24 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230501” VALUES LESS THAN (TO_DATE(‘ 2023-05-01 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230508” VALUES LESS THAN (TO_DATE(‘ 2023-05-08 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230515” VALUES LESS THAN (TO_DATE(‘ 2023-05-15 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230522” VALUES LESS THAN (TO_DATE(‘ 2023-05-22 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230529” VALUES LESS THAN (TO_DATE(‘ 2023-05-29 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230605” VALUES LESS THAN (TO_DATE(‘ 2023-06-05 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230612” VALUES LESS THAN (TO_DATE(‘ 2023-06-12 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230619” VALUES LESS THAN (TO_DATE(‘ 2023-06-19 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230626” VALUES LESS THAN (TO_DATE(‘ 2023-06-26 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230703” VALUES LESS THAN (TO_DATE(‘ 2023-07-03 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230710” VALUES LESS THAN (TO_DATE(‘ 2023-07-10 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230717” VALUES LESS THAN (TO_DATE(‘ 2023-07-17 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230724” VALUES LESS THAN (TO_DATE(‘ 2023-07-24 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230731” VALUES LESS THAN (TO_DATE(‘ 2023-07-31 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230807” VALUES LESS THAN (TO_DATE(‘ 2023-08-07 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230814” VALUES LESS THAN (TO_DATE(‘ 2023-08-14 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230821” VALUES LESS THAN (TO_DATE(‘ 2023-08-21 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230828” VALUES LESS THAN (TO_DATE(‘ 2023-08-28 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230904” VALUES LESS THAN (TO_DATE(‘ 2023-09-04 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230911” VALUES LESS THAN (TO_DATE(‘ 2023-09-11 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230918” VALUES LESS THAN (TO_DATE(‘ 2023-09-18 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20230925” VALUES LESS THAN (TO_DATE(‘ 2023-09-25 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20231002” VALUES LESS THAN (TO_DATE(‘ 2023-10-02 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20231009” VALUES LESS THAN (TO_DATE(‘ 2023-10-09 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20231016” VALUES LESS THAN (TO_DATE(‘ 2023-10-16 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20231023” VALUES LESS THAN (TO_DATE(‘ 2023-10-23 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20231030” VALUES LESS THAN (TO_DATE(‘ 2023-10-30 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20231106” VALUES LESS THAN (TO_DATE(‘ 2023-11-06 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20231113” VALUES LESS THAN (TO_DATE(‘ 2023-11-13 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20231120” VALUES LESS THAN (TO_DATE(‘ 2023-11-20 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20231127” VALUES LESS THAN (TO_DATE(‘ 2023-11-27 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20231204” VALUES LESS THAN (TO_DATE(‘ 2023-12-04 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20231211” VALUES LESS THAN (TO_DATE(‘ 2023-12-11 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20231218” VALUES LESS THAN (TO_DATE(‘ 2023-12-18 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_20231225” VALUES LESS THAN (TO_DATE(‘ 2023-12-25 00:00:00
‘, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_PART_23” ,
PARTITION “WPT_MAX” VALUES LESS THAN (TO_DATE(‘ 9999-12-31 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “WPT_TBL” );

 

Adding a new weekly range partitions.

set time on
set timin on
alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240101 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-01-08 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240108 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-01-15 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240115 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-01-22 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240122 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-01-29 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240129 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-02-05 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240205 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-02-12 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240212 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-02-19 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240219 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-02-26 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240226 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-03-04 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240304 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-03-11 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240311 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-03-18 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240318 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-03-25 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240325 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-04-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240401 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-04-08 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240408 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-04-15 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240415 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-04-22 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240422 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-04-29 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240429 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-05-06 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240506 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-05-13 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240513 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-05-20 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240520 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-05-27 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240527 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-06-03 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240603 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-06-10 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240610 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-06-17 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240617 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-06-24 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240624 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-07-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240701 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-07-08 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240708 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-07-15 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240715 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-07-22 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240722 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;
alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-07-29 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240729 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-08-05 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240805 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-08-12 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240812 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-08-19 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240819 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-08-26 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240826 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-09-02 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240902 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-09-09 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240909 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-09-16 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240916 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-09-23 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240923 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;
alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-09-30 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20240930 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-10-07 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20241007 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-10-14 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20241014 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-10-21 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20241021 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-10-28 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20241028 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-11-04 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20241104 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-11-11 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20241111 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-11-18 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20241118 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-11-25 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20241125 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-12-02 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20241202 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-12-09 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20241209 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-12-16 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20241216 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-12-23 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20241223 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

alter table STG.WPT split partition WPT_MAX AT (TO_DATE(‘2024-12-30 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION WPT_20241230 TABLESPACE WPT_PART_24 , PARTITION WPT_MAX) PARALLEL 4;

Verify table level Indexes status.

select DI.INDEX_NAME,DI.PARTITIONED,DI.OWNER,di.status
from dba_indexes di
where DI.TABLE_NAME=’WPT’
and DI.OWNER=’STG’;

 

Verify Partition indexes status.

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=’WPT’
and DI.OWNER=’STG’
and DIP.STATUS<>’USABLE’;

 

See Also: