CODEWITHSUNDEEP
oracle-database
sumit vedi
sumit vedi

Reputation: 777

list partitioning in oracle tables

I have created a table with default partitioning, Now I am adding another partition/subpartition then I am getting oracle error

CREATE TABLE STR_MAINFRAME_DB
( 
    CATEGORY         VARCHAR(100) NOT NULL,
    SUBCATEGORY         VARCHAR(100) NOT NULL
)
PARTITION BY LIST (CATEGORY)
SUBPARTITION BY LIST (SUBCATEGORY)
(
    PARTITION P_DUMMY VALUES (DEFAULT)
    (
        SUBPARTITION S_P_DUMMY VALUES (DEFAULT)  
    ));

I am adding one more partition by using below SQL but it is throwing an error.

alter table str_mainframe_db add partition p_1 values ('A');

SQL Error: ORA-14323: cannot add partition when DEFAULT partition exists
14323. 00000 -  "cannot add partition when DEFAULT partition exists"
*Cause:    An ADD PARTITION operation cannot be executed when a partition
           with DEFAULT values exists

Please help?????????

Upvotes: 1

Views: 1590

Answers (1)

Justin Cave
Justin Cave

Reputation: 231871

You cannot partition an existing table.

You will need to create a new, partitioned table and then move the data from the old table to the new. Then you can drop the old table and rename the new one to use the old table's name.

Depending on your downtime window and the nature of your application's access to data, you may want to use the dbms_redefinition package to manage this. It will like take more time than doing it manually but the overhead it imposes allows the old table to be kept online while the new table is being populated.

Upvotes: 1

Related Questions