Skip to Main Content
Forums

Cloud Platform

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Big table with clobs/Indexes into partition table

omegamarkSep 30 2020 — edited Sep 30 2020

create table emp3 (empno number GENERATED ALWAYS AS IDENTITY,
text clob);
BEGIN
FOR x IN 1..100
LOOP
INSERT INTO emp3(text) SELECT DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(1,2100))) FROM dual;
END LOOP;
END;
/

alter table EMP3 ADD CONSTRAINT pk1 primary key(EMPNO);
CREATE INDEX pk2 ON emp3(text) INDEXTYPE IS CTXSYS.CONTEXT;

ALTER TABLE emp3 MODIFY
PARTITION BY RANGE (EMPNO)
( PARTITION P1 VALUES LESS THAN (5),
PARTITION P2 VALUES LESS THAN (10),
PARTITION P3 VALUES LESS THAN (maxvalue)
) UPDATE INDEXES
(
pk1 local
) ;
Error report -
SQL Error: ORA-14427: table does not support modification to a partitioned state DDL
How to partition keeping the text index.

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
"CORE 12.2.0.1.0 Production"
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

Comments

BEDE

So, you must rename the table and its indexes and its constraints, then create a table with the same name and partitioned as you wish, then insert select from the non-partitioned table to the partitioned table, or, eventually exchange partition.
See https://www.linkedin.com/pulse/oracle-partitioning-ill-designed-tables-bogdan-dincescu/

BEDE


omegamark

Will Work on this and will comeback. My biggest issue here is the Oracle Text(Clob) which is indexed(Domain Index). That's one which gives the error. Thanks a lot for the feedback.
CREATE INDEX pk2 ON emp3(text) INDEXTYPE IS CTXSYS.CONTEXT; -- This index which gives the error.

1 - 3

Post Details

Added on Sep 30 2020
#general-questions
3 comments
215 views