NONPARTITIONED ONLINE in Oracle SQL

In Oracle versions prior to 19c, if a table had only one partition, you could not drop that last partition directly. The reason: Oracle treats the partitioned table and its partitions as tightly linked — if you dropped the last partition, you would basically delete the entire table, which is not allowed that way. Before 19c, the workaround involved steps like: Create a new non-partitioned table. Copy data from the partitioned table into the new non-partitioned table (using INSERT INTO new_table SELECT * FROM partitioned_table). Drop the old partitioned table. Rename the new table to the original table name. In Oracle 19c and above, Oracle introduced a simpler way: ALTER TABLE with the MODIFY PARTITION clause using the NONPARTITIONED ONLINE keywords. The syntax is: ALTER TABLE table_name MODIFY PARTITION partition_name TO NONPARTITIONED ONLINE; Meaning: Oracle will convert the partitioned table into a normal non-partitioned table. ONLINE ensures that the table is available during the operation (minimal downtime). Example: Suppose your table is: CREATE TABLE sales ( id NUMBER, sale_date DATE ) PARTITION BY RANGE (sale_date) ( PARTITION sales_q1 VALUES LESS THAN (TO_DATE('2025-04-01','YYYY-MM-DD')) ); To remove partitioning now (after 19c), just run: ALTER TABLE sales MODIFY PARTITION sales_q1 TO NONPARTITIONED ONLINE; After this, sales becomes a normal table without partitions.

Apr 28, 2025 - 14:47

In Oracle versions prior to 19c, if a table had only one partition, you could not drop that last partition directly.
The reason: Oracle treats the partitioned table and its partitions as tightly linked — if you dropped the last partition, you would basically delete the entire table, which is not allowed that way.

Before 19c, the workaround involved steps like:

Create a new non-partitioned table.

Copy data from the partitioned table into the new non-partitioned table (using INSERT INTO new_table SELECT * FROM partitioned_table).

Drop the old partitioned table.

Rename the new table to the original table name.

In Oracle 19c and above, Oracle introduced a simpler way:

ALTER TABLE with the MODIFY PARTITION clause using the **NONPARTITIONED ONLINE **keywords.

The syntax is:

ALTER TABLE table_name
MODIFY PARTITION partition_name
TO NONPARTITIONED ONLINE;

Meaning:

Oracle will convert the partitioned table into a normal non-partitioned table.

ONLINE ensures that the table is available during the operation (minimal downtime).

Example:

Suppose your table is:

CREATE TABLE sales
(
id NUMBER,
sale_date DATE
)
PARTITION BY RANGE (sale_date)
(
PARTITION sales_q1 VALUES LESS THAN (TO_DATE('2025-04-01','YYYY-MM-DD'))
);

To remove partitioning now (after 19c), just run:

ALTER TABLE sales
MODIFY PARTITION sales_q1 TO NONPARTITIONED ONLINE;

After this, sales becomes a normal table without partitions.