convert a partitioned table into a non-partitioned table

Today’s blogpost is about how to convert a partitioned table to a non-partitioned (un-partition) table, yes with Oracle 12c we got a single DDL command to Convert a non-partitioned table to a partitioned ones, using that DDL we
 

• Can Compress the partitions & put them in different tablespace if needed.
• Can update indexes to have them partitioned and compress & put them in different tablespace if needed
• Can pick up a subset of rows from the table and archive them.
• All of these can be done online (without downtime)

 
But there is no option available to turn a partitioned table into a non-partitioned table. In this demo we will see about how to get that done efficiently using partition maintenance operation.
 
First we will create a partitioned table with few indexes
 
demo@PDB1> create table t
  2  partition by range( created )
  3  interval ( numtoyminterval(1,'month') )
  4  ( partition pJan2023 values less than
  5     (to_date('01-feb-2023','dd-mon-yyyy')) )
  6  as
  7  select * from all_objects;
 
Table created.
 
demo@PDB1> create index t_idx1 on t(object_id) local;
 
Index created.
 
demo@PDB1> create index t_idx2 on t(object_name);
 
Index created.
 
demo@PDB1> create index t_idx3 on t(object_id,owner)
  2  global partition by hash(object_id)
  3  ( partition p1, partition p2);
 
Index created.
 
demo@PDB1> select partition_name , high_value
  2  from user_tab_partitions
  3  where table_name ='T';
 
PARTITION_NAME  HIGH_VALUE
--------------- ------------------------------
PJAN2023        TO_DATE(' 2023-02-01 00:00:00'
SYS_P12137      TO_DATE(' 2023-03-01 00:00:00'
SYS_P12138      TO_DATE(' 2023-11-01 00:00:00'
SYS_P12139      TO_DATE(' 2023-05-01 00:00:00'
SYS_P12140      TO_DATE(' 2023-04-01 00:00:00'
SYS_P12141      TO_DATE(' 2023-06-01 00:00:00'
SYS_P12142      TO_DATE(' 2023-07-01 00:00:00'
SYS_P12143      TO_DATE(' 2023-08-01 00:00:00'
SYS_P12144      TO_DATE(' 2023-09-01 00:00:00'
SYS_P12145      TO_DATE(' 2023-10-01 00:00:00'
 
 
One of the abilities of Oracle database is to go from one partition approach to another easily with a single DDL command. Using that we can modify the table to have single partition with all set of data in it.
 
demo@PDB1> alter table t
  2  modify partition by range( created )
  3  ( partition pmax values less than(maxvalue) )
  4  update indexes (
  5  t_idx1 local, t_idx2 local, t_idx3 local );
 
Table altered.
demo@PDB1> select partition_name , high_value
  2  from user_tab_partitions
  3  where table_name ='T';
 
PARTITION_NAME  HIGH_VALUE
--------------- ------------------------------
PMAX            MAXVALUE
 
demo@PDB1>
 
now all the partitions got merged/condensed into a single partition, but it is still a partitioned table.
 
Finally, all we need is exchange this partition into a non-partitioned table.
 
demo@PDB1> create table t1
  2  for exchange with table t;
 
Table created.
 
demo@PDB1>
demo@PDB1> create index t1_idx1 on t1(object_id);
 
Index created.
 
demo@PDB1> create index t1_idx2 on t1(object_name);
 
Index created.
 
demo@PDB1> create index t1_idx3 on t1(object_id,owner);
 
Index created.
 
demo@PDB1> alter table t
  2  exchange partition pmax
  3  with table t1
  4  including indexes;
 
Table altered.
demo@PDB1> select count(*) from t;
 
  COUNT(*)
----------
         0
demo@PDB1> select count(*) from t1;
 
  COUNT(*)
----------
     71007
demo@PDB1>

then finally drop and rename the tables as needed, so within a couple of steps we can turn a partitioned table into a non-partitioned table using the capabilities of Oracle partition techniques.

demo@PDB1> drop table t purge;
 
Table dropped.
demo@PDB1> rename t1 to t;
 
Table renamed.
demo@PDB1>