12.2 New Features -- 2 : Partitioning an Existing Table

A non-partitioned table can be Partitioned (without having to use DBMS_REDEFINITION) online.

SQL> connect hr/Oracle_4U@PDB1
Connected.
SQL> select count(*) from employees;

  COUNT(*)
----------
       107

SQL> create table employees_part as select * from employees;

Table created.

SQL> select table_name from user_part_tables;

no rows selected

SQL> alter table employees_part
  2  modify
  3  partition by range (last_name)
  4  (partition p_N values less than ('O'),
  5   partition p_Q values less than ('R'),
  6   partition p_LAST values less than (MAXVALUE))
  7  online;

Table altered.

SQL>
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'EMPLOYEES_PART'
  4  order by partition_position
  5  /

PARTITION_NAME
--------------------------------------------------------------------------------
HIGH_VALUE
------------
P_N
'O'

P_Q
'R'

P_LAST
MAXVALUE


SQL>
SQL> select table_name, partitioning_type, partition_count
  2  from user_part_tables
  3  where table_name = 'EMPLOYEES_PART'
  4  /

TABLE_NAME
--------------------------------------------------------------------------------
PARTITION PARTITION_COUNT
--------- ---------------
EMPLOYEES_PART
RANGE                   3


SQL>
SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'EMPLOYEES_PART'
  4  order by partition_position
  5  /

PARTITION_NAME
--------------------------------------------------------------------------------
  NUM_ROWS
----------
P_N
        71

P_Q
        10

P_LAST
        26


SQL>

I was able to convert a Non-Partitioned Table to a Range-Partitioned Table online.
.
.
.
There was a question in the Comments about the Object_ID.  The table actually now consists of *multiple* objects (the Partitions) each with it's own Object_ID.

SQL> REM This test in 19c 19.3.  I expect the same behaviour in 12.2.0.1
SQL> create table employees_part as select * from employees;

Table created.

SQL> select object_id, data_object_id from user_objects
  2  where object_name = 'EMPLOYEES_PART';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     73228          73228

SQL> alter table employees_part
  2  modify
  3  partition by range (last_name)
  4  (partition p_N values less than ('O'),
  5   partition p_Q values less than ('R'),
  6   partition p_LAST values less than (MAXVALUE));

Table altered.

SQL> select object_id, data_object_id, subobject_name
  2  from user_objects
  3  where object_name = 'EMPLOYEES_PART'
  4  order by 1,2
  5  /

 OBJECT_ID DATA_OBJECT_ID SUBOBJECT_NAME
---------- -------------- ------------------
     73228
     73233          73233 P_N
     73234          73234 P_Q
     73235          73235 P_LAST

SQL>

(You can search this blog for "DATA_OBJECT_ID" being different from OBJECT_ID.  See this earlier post.)