Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 536.4K On-Premises Infrastructure
- 138.3K Analytics Software
- 38.6K Application Development Software
- 5.8K Cloud Platform
- 109.5K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
Big table with clobs/Indexes into partition table

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
-
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/
-
-
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.