Big table with clobs/Indexes into partition table

omegamark

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

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.