Create a partition table in Oracle

Create a partition table in Oracle

1. Create partitions by month

Create table T_MHDP

(

ID VARCHAR2 (50) not null,

FLT_AIRCODE VARCHAR2 (4 ),

FLT_NUMBER NUMBER (7 ),

FLT_DATE DATE

) Partition by range (FLT_DATE)

(

Partition PART201105 values less than (TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN '))

Tablespace TS_MHDP_1105

Pctfree 10

Initrans 1

Maxtrans 255

Storage

(

Initial 64 K

Next 1 M

Minextents 1

Maxextents unlimited

),

Partition PART201106 values less than (TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN '))

Tablespace TS_MHDP_1106

Pctfree 10

Initrans 1

Maxtrans 255

Storage

(

Initial 64 K

Next 1 M

Minextents 1

Maxextents unlimited

),

Partition PART201107 values less than (TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN '))

Tablespace TS_MHDP_1107

Pctfree 10

Initrans 1

Maxtrans 255

Storage

(

Initial 64 K

Next 1 M

Minextents 1

Maxextents unlimited

),

Partition PARTDEFAULT values less than (TO_DATE ('2017-12-30 00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN '))

Tablespace TS_MHDP

Pctfree 10

Initrans 1

Maxtrans 255

Storage

(

Initial 64 K

Next 1 M

Minextents 1

Maxextents unlimited

)

);

2. Create a global index

Create index INDEX_MHDP_PK on T_MHDP (ID)

Tablespace TS_INDEX_MHDP

Pctfree 10

Initrans 2

Maxtrans 255

Storage

(

Initial 64 K

Next 1 M

Minextents 1

Maxextents unlimited

);

3. Create a local index

Create index INDEX_MHDP_PK ON T_MHDP (ID) LOCAL (

PARTITION PART201105 TABLESPACE TS_MHDP_INDEX_1105,

PARTITION PART201106 TABLESPACE TS_MHDP_INDEX_1106,

PARTITION PART201107 TABLESPACE TS_MHDP_INDEX_1107,

Partition partdefault tablespace TS_MHDP_INDEX

);