SQL Developer Create Table Partition

Since the syntax to create a table partitioning is really hard to keep in mind, we'd better use GUI tools to facilitate us to create. Before doing this, I'd recommend that you should read: how to create a normal table in SQL developer first.

Here we introduce the way to create a new partitioned table by SQL developer.

Let's see the procedure.

1. New a Table
2. Column Definition
3. Partition Type and Key
4. Add Partition
5. Preview DDL
6. Table Created

New a Table

Right-click on "Tables", it shows a function menu.

We enable "Advanced" option.

Column Definition

Add some columns.

Partition Type and Key

We select "Range" as our partition type.

Select the partition key. Columns which contain datetime factor are good candidates to be a partition key.

Add Partition

Click on + sign to add a new partition segment.

Historical Data (Cold Data)

We add the first partition segment with values less than TO_DATE('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS').

New Data (Hot Data)

The rest of rows go to the second partition segment, so we choose MAXVALUE to accommodate all new data.

Preview DDL

We review DDL for making sure that everything is ready.

The literal DDL is as below.

CREATE TABLE SALES
(
  PROD_ID        NUMBER                         NOT NULL,
  CUST_ID        NUMBER                         NOT NULL,
  TIME_ID        DATE                           NOT NULL,
  CHANNEL_ID     NUMBER                         NOT NULL,
  PROMO_ID       NUMBER                         NOT NULL,
  QUANTITY_SOLD  NUMBER(10,2)                   NOT NULL,
  AMOUNT_SOLD    NUMBER(10,2)                   NOT NULL
)
PARTITION BY RANGE (TIME_ID)
( 
  PARTITION SALES_2000 VALUES LESS THAN (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
  PARTITION SALES_CURRENT VALUES LESS THAN (MAXVALUE)
);

Then click OK to confirm.

Table Created

After we have created the new partitioned table, we can try to load some data.

SQL> insert into hr.sales select * from sh.sales;

918843 rows created.

SQL> commit;

Commit complete.

We made it.