SQL Error: ORA-14006: invalid partition name

Question

I am trying to partition an existing table in Oracle 12C R1 using below SQL statement.

ALTER TABLE TABLE_NAME MODIFY
PARTITION BY RANGE (DATE_COLUMN_NAME)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION part_01  VALUES LESS THAN (TO_DATE('01-SEP-2017', 'DD-MON-RRRR'))
) ONLINE;

Getting error:

Error report -
SQL Error: ORA-14006: invalid partition name
14006. 00000 -  "invalid partition name"
*Cause:    a partition name of the form <identifier> is
           expected but not present.
*Action:   enter an appropriate partition name.

Partition needs to be done on the basis of data datatype column with the interval of one month.

Min value of Date time column in the Table is 01-SEP-2017.

Solution

You can't partition an existing table like that. That statement is modifying the partition that hasn't been created yet. I don't know the automatic way to do this operation and I am not sure that you can do it.

Although I have done this thing many times but with manual steps. Do the following if you can't find an automated solution:

1. Create a partitioned table named table_name_part with your clauses and all your preferences.
2. Insert into this partitioned table all rows from original table. Pay attention to compression. If you have some compression on table (Basic or HCC) you have to use + APPEND hint.
3. Create on partitioned table your constrains and indexes from the original table.
4. Rename the tables and drop the original table. Do not drop it until you make some counts on them.

I saw that your table has the option to auto-create partition if it does not exists. (NUMTOYMINTERVAL(1,'MONTH')) So you have to create your table with first partition only. I assume that you have here a lot of read-only data, so you won't have any problem with consistency instead of last month. Probably there is some read-write data so there you have to be more careful with the moment when you want to insert data in new table and switch tables.

Hope to help you. As far as I know there might be a package named DBMS_REDEFINITION that can help you with an automated version of my steps. If you need more details or need some help on my method, please don't hesitate.

UPDATE: From Oracle 12c R2 you can convert a table from an unpartitioned to a partitioned one with your method. Find a link below. Now this is a challenge for me and I am trying to convert, but I think there is no way to make this conversion online in 12c R1.

In previous releases you could partition a non-partitioned table using EXCHANGE PARTITION or DBMS_REDEFINITION in an "almost online" manner, but both methods require multiple steps. Oracle Database 12c Release 2 makes it easier than ever to convert a non-partitioned table to a partitioned table, requiring only a single command and no downtime.

https://oracle-base.com/articles/12c/online-conversion-of-a-non-partitioned-table-to-a-partitioned-table-12cr2

Solution

I found a solution for you. Here you will have all of my steps that I run to convert table online. :)

1. Create regular table and populate it.

CREATE TABLE SCOTT.tab_unpartitioned
(
    id              NUMBER,
    description     VARCHAR2 ( 50 ),
    created_date    DATE
);
INSERT INTO tab_unpartitioned
        SELECT LEVEL,
               'Description for ' || LEVEL,
               ADD_MONTHS ( TO_DATE ( '01-JAN-2017', 'DD-MON-YYYY' ),
                            -TRUNC ( DBMS_RANDOM.VALUE ( 1, 4 ) - 1 ) * 12 )
          FROM DUAL
    CONNECT BY LEVEL <= 10000;
COMMIT;

2. Create partitioned table with same structure.

--If you are on 11g create table with CREATE TABLE command but with different name. ex: tab_partitioned

CREATE TABLE SCOTT.tab_partitioned
(
    id              NUMBER,
    description     VARCHAR2 ( 50 ),
    created_date    DATE
)
PARTITION BY RANGE (created_date)
INTERVAL( NUMTOYMINTERVAL(1,'YEAR'))
(PARTITION part_2015 VALUES LESS THAN (TO_DATE ( '01-JAN-2016', 'DD-MON-YYYY' )),
 PARTITION part_2016 VALUES LESS THAN (TO_DATE ( '01-JAN-2017', 'DD-MON-YYYY' )),
 PARTITION part_2017 VALUES LESS THAN (TO_DATE ( '01-JAN-2018', 'DD-MON-YYYY' )));

--this is an alter command that works only in 12c.
ALTER TABLE tab_partitioned
    MODIFY
        PARTITION BY RANGE (created_date)
        (PARTITION part_2015 VALUES LESS THAN (TO_DATE ( '01-JAN-2016', 'DD-MON-YYYY' )),
         PARTITION part_2016 VALUES LESS THAN (TO_DATE ( '01-JAN-2017', 'DD-MON-YYYY' )),
         PARTITION part_2017 VALUES LESS THAN (TO_DATE ( '01-JAN-2018', 'DD-MON-YYYY' )));

3. Check if the table can be converted. This procedure should run without any error. 
Prerequisites: table should have an UNIQUE INDEX and a Primary Key constraint.

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','TAB_UNPARTITIONED');

4. Run the following steps like I have done.

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT','TAB_UNPARTITIONED','TAB_PARTITIONED'); 
var num_errors varchar2(2000);
EXEC DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT','TAB_UNPARTITIONED','TAB_PARTITIONED', 1,TRUE,TRUE,TRUE,FALSE,:NUM_ERRORS,FALSE);
SQL> PRINT NUM_ERRORS -- Should return 0
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT','TAB_UNPARTITIONED','TAB_PARTITIONED');
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT','TAB_UNPARTITIONED','TAB_PARTITIONED');

At the end of the script you will see that the original table is partitioned.

Comments

[Tajinder]

Thanks for the detailed description. Previously, I am using the same method you have described. Just need to do it in a simple way. I don't know the best approach.

[adimoise91]

I just added the solution. As you can see in my picture, i already tested on my database. Should work on your database too. Let me know if you need something else.

[Tajinder]

Thanks for doing so much effort. Your step 2 is the same what I am doing except online keyword. and this is creating the same error in Oracle 12c R1. Please confirm me if i am not getting your steps correctly.

[adimoise91]

Yes, you are right. I tested on 12c R2 and is working. Your statement is for 12c R2. I am glad that I could help you! Thanks for appreciation! Have a nice day!