How to convert a non-partitioned table into a partitioned table in Oracle version 12.2 and onwards

With Oracle 12.2 and higher versions, it is really simple to convert a non-partitioned table to a partitioned table: you can now use the "ALTER TABLE .... MODIFY" syntax.

Below I am showing how I used this feature for one of my tables.

First, find some basic info about the table as it is right now:

SELECT TABLE_NAME, PARTITIONED,NUM_ROWS,COMPRESSION
FROM  DBA_TABLES
WHERE TABLE_NAME='SEGMENT_SIZE_SAMPLES';

Output:

TABLE_NAME	PARTITIONED	NUM_ROWS	COMPRESSION
SEGMENT_SIZE_SAMPLES	NO	948401	DISABLED

Alter the table. I am taking the opportunity to compress the table at the same time as I am getting it partitioned. I am also using the ONLINE keyword, allowing DML against the table during the operation:

ALTER TABLE SEGMENT_SIZE_SAMPLES MODIFY 
PARTITION BY RANGE (SAMPLE_DATE)
INTERVAL
(
   NUMTOYMINTERVAL(1,'MONTH')
)
(
PARTITION P_INIT VALUES LESS THAN (TO_DATE('2014-01','YYYY-MM') )
)
ROW STORE COMPRESS ADVANCED
ONLINE;

Analyze the table:

exec DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'DBDRIFT',TabName => 'SEGMENT_SIZE_SAMPLES');

Check the table properties again:

SELECT TABLE_NAME, PARTITIONED,NUM_ROWS,COMPRESSION
FROM  DBA_TABLES
WHERE TABLE_NAME='SEGMENT_SIZE_SAMPLES';

Output:

TABLE_NAME	PARTITIONED	NUM_ROWS	COMPRESSION
SEGMENT_SIZE_SAMPLES	YES	1000719

How about compression?

Check the DBA_TAB_PARTITIONS (I am only showing the first 5 rows for brevity):

SELECT TABLE_NAME, PARTITION_NAME,NUM_ROWS,COMPRESSION, COMPRESS_FOR
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME='SEGMENT_SIZE_SAMPLES'
FETCH FIRST 5 ROWS ONLY;

Output:

TABLE_NAME	PARTITION_NAME	NUM_ROWS	COMPRESSION	COMPRESS_FOR
SEGMENT_SIZE_SAMPLES	P_INIT	0	ENABLED	ADVANCED
SEGMENT_SIZE_SAMPLES	SYS_P17074	19320	ENABLED	ADVANCED
SEGMENT_SIZE_SAMPLES	SYS_P17075	24955	ENABLED	ADVANCED
SEGMENT_SIZE_SAMPLES	SYS_P17076	24150	ENABLED	ADVANCED
SEGMENT_SIZE_SAMPLES	SYS_P17077	24934	ENABLED	ADVANCED

How about existing indexes?

By default, existing non-unique indexes are also partitioned during the alteration of the table.

There are two indexes on my table, one unique index supporting the primary key constraint, and one non-unique index.

Before the table was altered, both were unpartitioned:

SELECT INDEX_NAME,UNIQUENESS, PARTITIONED 
FROM DBA_INDEXES
WHERE TABLE_NAME='SEGMENT_SIZE_SAMPLES';

INDEX_NAME	UNIQUENESS	PARTITIONED
SEGMENT_SIZE_SAMPLES_IDX1	UNIQUE	NO
SEGMENT_SIZE_SAMPLES_IDX2	NONUNIQUE	NO

After the table was partitioned, the non-unique index was automatically partitioned, too:

INDEX_NAME	UNIQUENESS	PARTITIONED
SEGMENT_SIZE_SAMPLES_IDX1	UNIQUE	NO
SEGMENT_SIZE_SAMPLES_IDX2	NONUNIQUE	YES

Update 18.03.2020:

Another example using LIST partitioning:

alter table MUSIC.ALBUM modify
partition by list (GENRE)
(
    partition P_ROCK values (( 'ROCK')),
    partition P_POP values (( 'POP')),
    partition P_CLASSICAL values (( 'CLASSICAL')),
    partition P_MISC values (default)
)
online;

Update 12.03.2021:
An example using subpartitions:

-- First, give the LOB the desired attributes, if you wish to change any of them:
alter TABLE STOCKS MOVE
LOB (DOC)
STORE AS SECUREFILE(
  TABLESPACE  DATA1
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  COMPRESS    MEDIUM
);

-- Alter the table
alter TABLE STOCKS MODIFY
PARTITION BY LIST (stockname)
SUBPARTITION BY RANGE (LASTUPDATED)
(   -- First partition is called a_name
    PARTITION a_name VALUES('a-abc')
    (
    -- older values
    SUBPARTITION SP_a_name_1_older VALUES LESS THAN ( TO_DATE('2016-01', 'YYYY-MM')) TABLESPACE DATA1
    LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA1 ),
    -- 2016
    subpartition SP_a_name_201601 VALUES LESS THAN (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2016
    LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA_2016 ),

...continue with one subpartition per month per year...

...contine with  partitions b_name, c_name etc

-- Final partition is called z_name
 PARTITION z_name VALUES(default)
  (
    -- 2016
    SUBPARTITION SP_sp_z_name_older VALUES LESS THAN ( TO_DATE('2016-01', 'YYYY-MM')) TABLESPACE DATA1,
    subpartition SP_sp_z_name_201601 VALUES LESS THAN (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2016
    LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA_2016 ),

...continue with one subpartition per month per year...

 LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA_2022 ),
    subpartition SP_z_name_202212 VALUES LESS THAN (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2022
    LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA_2022 ),
    SUBPARTITION SP_z_name_DEFAULT VALUES LESS THAN (MAXVALUE) TABLESPACE DATA1
    LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA1)
    )
)
ONLINE
UPDATE INDEXES(
  IDX_1 LOCAL,
  IDX_2 LOCAL
);
  

Note that UPDATE INDEXES converts IDX_1 and IDX_2 and convert them to LOCAL indexes, on the fly.
Source: Oracle Documentation
I also recommend to look at Oracle-base.com for further reading.

How to lookup ip addresses from mac addresses on Windows

Use the arp utility (Adress Resolution Protocol).

arp -a

it will print the arp table for you, where you can map ip addresses to mac addresses.

How to create a multicolumn list-partitioned table in Oracle 12.2

From Oracle 12.2, you can LIST partition a table using multiple columns:

CREATE TABLE regional_orders(
  id            NUMBER,
  country_code  VARCHAR2(5),
  region_code   VARCHAR2(20),
  customer_id   NUMBER,
  order_date    DATE,
  order_total   NUMBER(8,2),
  CONSTRAINT regional_orders_pk PRIMARY KEY (id)
)
PARTITION BY LIST (country_code,region_code) 
(
                PARTITION NORWAY_SOUTH_WEST VALUES (
                                        ( 'NOR','AUST-AGDER'),
                                        ( 'NOR','VEST-AGDER'),
                                        ( 'NOR','ROGALAND')
                                         ),
                PARTITION NORWAY_WEST VALUES (
                                        ( 'NOR','HORDALAND'),
                                        ( 'NOR','SOGN OG FJORDANDE'),
                                        ( 'NOR','MØRE OG ROMSDAL')
                                         ),
                PARTITION NORWAY_CENTRAL VALUES (
                                        ( 'NOR','TRØNDELAG')
                                         ),
                PARTITION NORWAY_EAST VALUES (
                                        ( 'NOR','BUSKERUD'),
                                        ( 'NOR','HEDMARK'),
                                        ( 'NOR','TELEMARK')
                                         )
);

Incoming data that has any of the combinations below, will enter the partition NORWAY_SOUTH_WEST

'NOR','AUST-AGDER'
'NOR','VEST-AGDER'
'NOR','ROGALAND'

This functionality removes the need for LIST-LIST subpartitioning, as far as I can see.


Some queries to verify that the partitions exist:

select  partition_name,partition_position,tablespace_name
from user_tab_partitions 
where table_name='REGIONAL_ORDERS';

PARTITION_NAME	PARTITION_POSITION	TABLESPACE_NAME
NORWAY_SOUTH_WEST	1	USERS
NORWAY_WEST	2	USERS
NORWAY_CENTRAL	3	USERS
NORWAY_EAST	4	USERS

SELECT PARTITIONING_TYPE,PARTITION_COUNT,PARTITIONING_KEY_COUNT 
FROM USER_PART_TABLES 
where table_name='REGIONAL_ORDERS';

PARTITIONING_TYPE	PARTITION_COUNT	PARTITIONING_KEY_COUNT
LIST	4	2

SELECT column_name,column_position 
FROM USER_PART_KEY_COLUMNS
where name='REGIONAL_ORDERS';

COLUMN_NAME	COLUMN_POSITION
COUNTRY_CODE	1
REGION_CODE	2

Let's insert a row:

insert into regional_orders
values (1,'NOR','ROGALAND',3344,'04.01.2019', 1000);
commit;

Analyze the table:

exec DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'SCOTT',TabName => 'REGIONAL_ORDERS');

Verify:

select partition_name, num_rows
from user_tab_partitions 
where table_name='REGIONAL_ORDERS';

PARTITION_NAME	NUM_ROWS
NORWAY_CENTRAL	0
NORWAY_EAST	0
NORWAY_SOUTH_WEST	1
NORWAY_WEST	0

Insert with a different value:

insert into regional_orders
values (2,'NOR','BUSKERUD',3345,'04.01.2019', 1200);

COMMIT;

Analyze again:

exec DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'SCOTT',TabName => 'REGIONAL_ORDERS');

Verify:

select partition_name, num_rows
from user_tab_partitions 
where table_name='REGIONAL_ORDERS';

PARTITION_NAME	NUM_ROWS
NORWAY_CENTRAL	0
NORWAY_EAST	1
NORWAY_SOUTH_WEST	1
NORWAY_WEST	0

If you send in a value that is not covered in your pre-specified list, your statement will fail:

insert into regional_orders
values (3,'NOR','NORDLAND',3346,'04.01.2019', 1100);

ORA-14400: inserted partition key does not map to any partition

The solution to this is to either create your table with a default partition or to use the Oracle 12.2 new feature of automatic list partitioning.

Using a default partition will be as follows:

CREATE TABLE regional_orders(
  id            NUMBER,
  country_code  VARCHAR2(5),
  region_code   VARCHAR2(20),
  customer_id   NUMBER,
  order_date    DATE,
  order_total   NUMBER(8,2),
  CONSTRAINT regional_orders_pk PRIMARY KEY (id)
)
PARTITION BY LIST (country_code,region_code) 
                (       
                PARTITION NORWAY_SOUTH_WEST VALUES (
                                        ( 'NOR','AUST-AGDER'),
                                        ( 'NOR','VEST-AGDER'),
                                        ( 'NOR','ROGALAND')
                                         ),
                PARTITION NORWAY_WEST VALUES (
                                        ( 'NOR','HORDALAND'),
                                        ( 'NOR','SOGN OG FJORDANDE'),
                                        ( 'NOR','MØRE OG ROMSDAL')
                                         ),
                PARTITION NORWAY_CENTRAL VALUES (
                                        ( 'NOR','TRØNDELAG')
                                         ),
                PARTITION NORWAY_EAST VALUES (
                                        ( 'NOR','BUSKERUD'),
                                        ( 'NOR','HEDMARK'),
                                        ( 'NOR','TELEMARK')
                                         ),
                PARTITION NORWAY_REST VALUES (DEFAULT) 
                )
;

Using the AUTOMATIC keyword will be as easy as adding the keyword AUTOMATIC to your partitioning-clause and to remove the specification for your overflow partition:

CREATE TABLE regional_orders(
  id            NUMBER,
  country_code  VARCHAR2(5),
  region_code   VARCHAR2(20),
  customer_id   NUMBER,
  order_date    DATE,
  order_total   NUMBER(8,2),
  CONSTRAINT regional_orders_pk PRIMARY KEY (id)
)
PARTITION BY LIST (country_code,region_code) AUTOMATIC
                (       
                PARTITION NORWAY_SOUTH_WEST VALUES (
                                        ( 'NOR','AUST-AGDER'),
                                        ( 'NOR','VEST-AGDER'),
                                        ( 'NOR','ROGALAND')
                                         ),
                PARTITION NORWAY_WEST VALUES (
                                        ( 'NOR','HORDALAND'),
                                        ( 'NOR','SOGN OG FJORDANDE'),
                                        ( 'NOR','MØRE OG ROMSDAL')
                                         ),
                PARTITION NORWAY_CENTRAL VALUES (
                                        ( 'NOR','TRØNDELAG')
                                         ),
                PARTITION NORWAY_EAST VALUES (
                                        ( 'NOR','BUSKERUD'),
                                        ( 'NOR','HEDMARK'),
                                        ( 'NOR','TELEMARK')
                                         )
                )
;

New values will be added automatically and given system-generated partition names.

There is an obvious trade-off here: if you use automation, you will not be able to give your partition logical names and place values logically belong together, in the same partition.

For example, the values

'NOR','NORDLAND'

may logically belong to the same partition as the values

'NOR','TROMS'
and
'NOR','FINNMARK'

If I knew such a value may be sent at the time of table creation, I would of course create another partition, like this:

PARTITION NORWAY_NORTH VALUES (
                                        ( 'NOR','NORDLAND'),
                                        ( 'NOR','TROMS'),
                                        ( 'NOR','FINNMARK')
                                         )

If you use a default partition, you will always be able to save your incoming rows, and you have full controll over your partition names and their values.
But new rows end up in an overflow partition together with any other record that cannot find its way into a properly named and configured partition.
You may need to split your default partition later, to satisify your business needs or to reduce the size of your default partition.