How to alter an existing partitioned table to add sub-partitions to it

oracleoracle-12cpartitioning

This forum has helped me earlier on many occasions and now I need someone to help me with this.

I am looking to convert a pre-existing partitioned (range) table – to sub-partitioned (composite range-list).

1) I Range Partitioned an existing table using the query below:

alter table PART_TEST 
modify PARTITION BY RANGE (CREATEDATE)
(
PARTITION p1 VALUES LESS THAN (TO_DATE('15-MAY-2019', 'DD-MON-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('16-MAY-2019', 'DD-MON-YYYY')),
PARTITION p3 VALUES LESS THAN (maxvalue)) online;

2) Then I tried to alter the table to add sub-partitions (list) using the query below:

ALTER TABLE PART_TEST
MODIFY PARTITION P1 
ADD SUBPARTITION SP1
VALUES ('1') tablespace TEST_PART;

But I see this error:

Error report – ORA-14253: table is not partitioned by composite
partition method
14253. 00000 – "table is not partitioned by Composite Range method"
*Cause: The table in a subpartition maintenance operation (ALTER TABLE
EXCHANGE/MODIFY/MOVE/TRUNCATE SUBPARTITION, or ALTER TABLE MODIFY
PARTITION ADD/COALESCE SUBPARTITION command must be partitioned
by Composite Range method
*Action: Ensure that the table is partitioned by Composite Range method

Below is what I am tasked with,

Scenario – 1:
==> I want to have my existing partitions(range) subpartitioned (list) so that my old data is usable. The data in each of my partitions have to be moved to a sub-partition,

Scenario – 2: Then revert back to partition level (reverse migrate),
==> I mean the data from all of the sub-partitions within each partition is moved to be at the partition level and no sub-partitions are left in the database.

Is it possible and is there a way to do so?
If so how to implement the above scenarios – convert existing partitions(range) to sub-partitioned (Range-list) – and how to move back the data from sub-partitions to each of its partition back and no-sub partitions are left.

Best Answer

Scenario 1 - redefine the table

Your SQL is adding a sub-partition, not redefining the table. I suspect you need to redefine the table to have sub-partitions before you can add a sub-partition.

12.2c+

The ability to change a non-partitioned table into a partitioned table via ALTER TABLE was introduced in 12.2c.

Looking at the documentation, it appears that you can change a RANGE partitioned table into a RANGE-LIST partition in the same way. I haven't tried; be cautious; use DEV for practice.

pre-12.2c

DBMS_REDEFINITION is the de facto way of changing a table between non-partitioned and partition designs.

Google is your friend. But, here is my recommended link:

Scenario 2 - convert back to non-partitioned table.

This requirement perplexes me. Doing such a massive change, multiple times, is probably a Tell-Tale sign that you have a more serious problem that you should be solving.

Any way, I suspect that the only way to do this is with DBMS_REDEFINITION.

Related Solutions

Why won’t Oracle alter the size of a column that is used for sub partitioning

The solution is dbms_redefinition package. Basically redefition will move, online, to a new table (called interim table), then move all dependent objets like indexes and last exchange the interim table with the original one.

What you have to do is:

Create the interim table withe correct column definition
Run redefinition
Drop the old table

Use dbms_redefinition in the following way:

-- DETERMINE IF THE ORIGINAL TABLE CAN BE REDEFINED ONLINE
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA','YOURTABLE', DBMS_REDEFINITION.CONS_USE_ROWID);   
END;
/

-- BEGIN THE REDEFINITION
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
UNAME => 'SCHEMA',
ORIG_TABLE => 'YOURTABLE',
INT_TABLE => 'INTERIM_YOURTABLE'
OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

-- IF THE TABLE HAD DEPENDENCIES (INDEXES ... CONSTRAINTS ... TRIGGERS)
-- THIS WOULD BE THE POINT AT WHICH THEY WOULD HAVE BEEN COPIED
DECLARE 
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCHEMA', 'YOURTABLE', 'INTERIM_YOURTABLE', 
dbms_redefinition.cons_orig_params, TRUE, TRUE, TRUE, FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

-- FINISH THE REDEFINITION
exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA','YOURTABLE','INTERIM_YOURTABLE');

If you run redefinition with SYSTEM user there is no problem. If you want to execute it with a less privileged user you have to trick some privileges in order to get it working. Privileges required are:

Execute privilege to DBMS_REDEFINITION
Create any table
Alter any table
Drop any table
Lock any table
Select any table

Tables with the following characteristics cannot be redefined online:

[9.0.1]Tables with no primary keys
Tables that have materialized view logs defined on them
[9i] Tables that are materialized view container tables and AQ tables
[10g] Tables that are replicated in an n-way master configuration can be redefined, but horizontal subsetting (subset of rows in the table), vertical subsetting (subset of columns in the table), and column transformations are not allowed.
The overflow table of an IOT table
Tables with fine-grained access control (row-level security)
Tables with BFILE columns
Tables with LONG columns can be redefined online, but those columns must be converted to CLOBS. Also, LONG RAW columns must be converted to BLOBS. Tables with LOB columns are acceptable.
Tables in the SYS and SYSTEM schema
Temporary tables

Other restrictions:

A subset of rows in the table
Only simple deterministic expressions, sequences, and SYSDATE can be used when mapping the columns in the interim table to those of the original table. For example, subqueries are not allowed.
If new columns are being added with no column mappings, then they must not be declared NOT NULL until the redefinition is complete.
There cannot be any referential constraints between the table being redefined and the interim table.
Table redefinition cannot be done NOLOGGING.
[10g] For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties.
You cannot convert a nested table to a VARRAY.

Oracle Exchange partition from non partitioned table

We have done the same operation using the following approach:

Create the destination table with the correct structure nologging
Insert append parallel into it (verifiy direct path load in the explain plan)
Create the indexes nologging
in some case add the FK novalidate
compute the stats
Alter the objects LOGGING
Drop the initial table