My Oracle Support Banner

Partitioned Table With User Defined Type Causes ORA-21700 On Update Of Partition Key After Dropping Type (Doc ID 1484903.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.7 to 11.2.0.3 [Release 11.1 to 11.2]
Information in this document applies to any platform.

Symptoms

After dropping a type used in a partitioned table with force option, column is no longer shown on table nor dictionary tables, but UPDATE command raises error:

ORA-21700: object does not exist or is marked for delete.

Error is reported for 11.1.0.7, and reproducible in 11.2.0.3 as well.

The following example illustrates the behaviour.

DROP TYPE test_tt FORCE;
DROP TABLE test1;

CREATE OR REPLACE TYPE test_tt AS VARRAY(4020) OF NUMBER (15);
/

CREATE TABLE test1 (col1_pk number, col2_part_key number, col3_data
varchar2(20), col4_udt test_tt)
PARTITION BY RANGE(col2_part_key) (PARTITION p1 VALUES LESS THAN (20120000),
PARTITION other VALUES LESS THAN (MAXVALUE));

CREATE UNIQUE INDEX test1_pk ON test1 (col1_pk, col2_part_key) LOCAL;
ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY (col1_pk,
col2_part_key);

ALTER TABLE test1 ENABLE ROW MOVEMENT;

INSERT INTO test1 VALUES (1,20120001, 'data 1',test_tt(1,2));
INSERT INTO test1 VALUES (2,20120002, 'data 2',test_tt(1,2));
COMMIT;
SELECT * FROM test1;
desc test1
UPDATE test1 set col2_part_key = 3 WHERE col1_pk = 2;
rollback;

DROP TYPE test_tt FORCE;
desc test1

SELECT * FROM test1;
UPDATE test1 set col2_part_key = 3 WHERE col1_pk = 2;

INSERT INTO test1 VALUES (3,20120001, 'data 1');
DELETE test1;

Sample output of the last few commands are the following:

SQL> SELECT * FROM test1;

   COL1_PK COL2_PART_KEY COL3_DATA
---------- ------------- -------------------------------------------
         1      20120001 data 1
         2      20120002 data 2

SQL>  UPDATE test1 set col2_part_key = 3 WHERE col1_pk = 2;
UPDATE test1 set col2_part_key = 3 WHERE col1_pk = 2
       *
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete


SQL> INSERT INTO test1 VALUES (3,20120001, 'data 1');

1 row created.

SQL> DELETE test1;

3 rows deleted.

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.