Forum Stats

  • 3,783,127 Users
  • 2,254,734 Discussions
  • 7,880,284 Comments

Discussions

Big table with clobs/Indexes into partition table

omegamark
omegamark Member Posts: 11 Blue Ribbon
edited Sep 30, 2020 5:06AM in General Questions

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

Answers