CODEWITHSUNDEEP
oracle-databaseoracle12cpartitioning
Jostein Topland
Jostein Topland

Reputation: 1020

Oracle 12.2: Unable to partition an un-partitioned table

Trying to create a partitioned table from a non-partitioned table. Table is listed as partitioned=NO from dba_tables and all indexes are index_type=NORMAL read from user_indexes. Using Oracle 12.2, also in compatible mode 12.2.

Trying to run the following code:

ALTER TABLE my_big_table MODIFY
 PARTITION BY RANGE (year) INTERVAL (5) (
  PARTITION p1 VALUES LESS THAN (2000),
  PARTITION p2 VALUES LESS THAN (2005),
  PARTITION p3 VALUES LESS THAN (2010),
  PARTITION p4 VALUES LESS THAN (2015),
  PARTITION p5 VALUES LESS THAN (2020))
UPDATE INDEXES ONLINE;

Error returned is ORA-14427: table does not support modification to a partitioned state DDL.

However, running the above SQL code on another big table from the same database, scheme and same user, successfully partitions another table.

How do I get more details on why my_big_table is unable to be partitioned?

Here's the DDL exported from dbms_metadata.get_ddl('TABLE', 'MY_BIG_TABLE', 'MY_SCHEME'). All names have been anonymized:

  CREATE TABLE "..."."..." 
   (
    "..." VARCHAR2(3) NOT NULL ENABLE, 
    "..." VARCHAR2(30) NOT NULL ENABLE, 
    "..." NUMBER NOT NULL ENABLE, 
    "..." NUMBER NOT NULL ENABLE, 
    "..." NUMBER NOT NULL ENABLE, 
    "..." NUMBER NOT NULL ENABLE, 
    "..." NUMBER NOT NULL ENABLE, 
    "..." NUMBER NOT NULL ENABLE, 
    "..." VARCHAR2(20), 
    "..." VARCHAR2(20) NOT NULL ENABLE, 
    "..." VARCHAR2(20) NOT NULL ENABLE, 
    "..." VARCHAR2(1) NOT NULL ENABLE, 
    "..." NUMBER, 
    "..." NUMBER, 
    "..." VARCHAR2(12), 
    "..." VARCHAR2(30), 
    "..." VARCHAR2(1) NOT NULL ENABLE, 
    "..." DATE, 
    "..." DATE, 
    "..." DATE, 
    "..." FLOAT(126) DEFAULT 0 NOT NULL ENABLE, 
    "..." FLOAT(126) DEFAULT 0 NOT NULL ENABLE, 
    "..." FLOAT(126) DEFAULT 0 NOT NULL ENABLE, 
    "..." FLOAT(126) DEFAULT 0 NOT NULL ENABLE, 
    "..." FLOAT(126) DEFAULT 0 NOT NULL ENABLE, 
    "..." FLOAT(126), 
    "..." VARCHAR2(20), 
    "..." VARCHAR2(20), 
    "..." FLOAT(126), 
    "..." NUMBER, 
    "..." NUMBER, 
    "..." NUMBER, 
    "..." NUMBER, 
    "..." VARCHAR2(30), 
    "..." VARCHAR2(3), 
    "..." VARCHAR2(80), 
    "..." VARCHAR2(30), 
    "..." VARCHAR2(30) DEFAULT '', 
    "..." VARCHAR2(30), 
    "..." VARCHAR2(30), 
    "..." VARCHAR2(30), 
    "..." VARCHAR2(30), 
    "..." VARCHAR2(30), 
    "..." FLOAT(126), 
    "..." FLOAT(126), 
    "..." FLOAT(126), 
    "..." NUMBER NOT NULL ENABLE, 
    "..." DATE NOT NULL ENABLE, 
    "..." FLOAT(126) DEFAULT 0, 
    "..." FLOAT(126), 
    "..." FLOAT(126) NOT NULL ENABLE, 
    "..." FLOAT(126) DEFAULT 0 NOT NULL ENABLE, 
    "..." DATE, 
    "..." FLOAT(126), 
    "..." FLOAT(126), 
    "..." FLOAT(126), 
    "..." FLOAT(126), 
    "..." FLOAT(126), 
    "..." FLOAT(126), 
    "..." FLOAT(126), 
    "..." FLOAT(126), 
    "..." FLOAT(126), 
    "..." FLOAT(126), 
    "..." VARCHAR2(38), 
    "..." DATE, 
    "..." NUMBER(10,8) DEFAULT 0 NOT NULL ENABLE, 
    "..." NUMBER, 
    "..." NUMBER, 
    "..." VARCHAR2(1) DEFAULT 'N' NOT NULL ENABLE, 
    "..." VARCHAR2(20), 
    "..." NUMBER, 
    "..." DATE, 
    "..." FLOAT(126), 
    "..." NUMBER, 
    "..." NUMBER, 
    "..." FLOAT(126), 
    "..." NUMBER, 
    "..." NUMBER, 
    "..." NUMBER, 
    "..." NUMBER, 
    "..." NUMBER, 
    "..." NUMBER, 
    "..." NUMBER, 
    "..." NUMBER, 
    "..." VARCHAR2(1), 
    "..." VARCHAR2(1) DEFAULT 'Y' NOT NULL ENABLE, 
    "..." FLOAT(126), 
    "..." DATE, 
    "..." NUMBER NOT NULL ENABLE, 
    "..." NUMBER, 
    "..." NUMBER, 
    "..." VARCHAR2(20), 
    "..." FLOAT(126), 
    "..." FLOAT(126), 
    "..." FLOAT(126), 
    "..." NUMBER, 
    "..." NUMBER, 
    "..." VARCHAR2(5) DEFAULT 'USD' NOT NULL ENABLE, 
     CONSTRAINT "..." UNIQUE ("...")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 11550720 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "..."  ENABLE, 
     CONSTRAINT "..." CHECK ( ASSUMED_OS_DATE_UPDATE_IND IN ('Y','N')) ENABLE, 
     CONSTRAINT "..." CHECK ( KEEP_ALL_QUOTES_IND IN ('Y','N')) ENABLE, 
     CONSTRAINT "..." PRIMARY KEY ("...", "...", "...", "...", "...", "...")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 8396800 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "..."  ENABLE, 
     CONSTRAINT "..." UNIQUE ("...")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "..."  ENABLE, 
     CONSTRAINT "..." FOREIGN KEY ("...")
      REFERENCES "..."."..." ("...") ENABLE, 
     CONSTRAINT "..." FOREIGN KEY ("...")
      REFERENCES "..."."..." ("...") ENABLE, 
     CONSTRAINT "..." FOREIGN KEY ("...", "...", "...", "...", "...")
      REFERENCES "..."."..." ("...", "...", "...", "...", "...") ON DELETE CASCADE ENABLE, 
     CONSTRAINT "..." FOREIGN KEY ("...")
      REFERENCES "..."."..." ("...") ENABLE, 
     CONSTRAINT "..." FOREIGN KEY ("...")
      REFERENCES "..."."..." ("...") ENABLE, 
     CONSTRAINT "..." FOREIGN KEY ("...")
      REFERENCES "..."."..." ("...") ENABLE, 
     CONSTRAINT "..." FOREIGN KEY ("...")
      REFERENCES "..."."..." ("...") ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 77905920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "..."

Upvotes: 0

Views: 2504

Answers (1)

Jostein Topland
Jostein Topland

Reputation: 1020

Solution:

Error was due to a Materialized View dependency. Removing the Materialized View resolved the issue, and the error about not being able to partitioning a non-partitioned table did not appear. Dependencies listed using SQL Developer on the table to be partitioned.

The Oracle database should be more specific about the error message. Error should inform about dependency issue, i.e. a specific name to a Materialized View, instead of showing the generic ORA-14427: table does not support modification to a partitioned state DDL.

An Oracle Support ticket created (SR 3-19188124751) and informed about this issue along with the fix.

Upvotes: 1

Related Questions