ORA-00904 While Executing ADD PARTITION To The Table Whose Column Was Set UNUSED (Doc ID 420362.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.5 - Release: 9.2 to 10.2
Oracle Server - Enterprise Edition - Version: 9.2.0.7 and later    [Release: 9.2 and later]
Information in this document applies to any platform.

Symptoms

Getting ORA-00904 while executing ADD PARTITION to the table, whose column of ABSTRACT_TYPE was set UNUSED previously.


Example :

CREATE OR REPLACE TYPE "VAT" AS VARRAY(20) OF VARCHAR2(32)
/

DROP TABLE TAB1;
CREATE TABLE TAB1 (ID NUMBER, C CLOB, VA VAT)
PARTITION BY RANGE ("ID")
(
PARTITION P1 VALUES LESS THAN (10),
PARTITION P2 VALUES LESS THAN (20)
);

INSERT INTO TAB1 VALUES (1, 'ABC', VAT(1,2,3));
COMMIT;

ALTER TABLE TAB1 SET UNUSED(C);
ALTER TABLE TAB1 ADD PARTITION P3 VALUES LESS THAN (30);

Result:

 

ORA-00904: "SYS_C00002_09050814:26:11$": invalid identifier

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms