Ok, minimal test case that reproduces the error, showing it's the intermediate table PK that is the problem:
SQL> CREATE TABLE SO33070_ORIGINAL
2 (
3 SERIAL_ID NUMBER(15,0),
4 INSERTED_TIME DATE DEFAULT SYSDATE,
5 PRIMARY KEY (SERIAL_ID)
6 );
Table created.
SQL>
SQL> CREATE TABLE SO33070_NEW
2 (
3 SERIAL_ID NUMBER(15,0),
4 INSERTED_TIME DATE DEFAULT SYSDATE,
5 PRIMARY KEY (SERIAL_ID)
6 )
7 PARTITION BY RANGE ("INSERTED_TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
8 (
9 PARTITION "p1_1" VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
10 );
Table created.
SQL>
SQL>
SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'PHIL', orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 num_errors NUMBER;
3 BEGIN
4
5 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW', copy_indexes=>dbms_redefinition.cons_orig_params , copy_triggers=>TRUE, copy_constraints=>TRUE, copy_privileges=>TRUE, ignore_errors=>FALSE, num_errors=>num_errors, copy_statistics=>false);
6
7 END;
8 /
DECLARE
*
ERROR at line 1:
ORA-01408: such column list already indexed
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1364
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2026
ORA-06512: at line 5
SQL>
SQL> col object_owner for a6
SQL> col base_table_name for a20
SQL> col ddl_txt for a55
SQL>
SQL> select object_type, object_owner, base_table_name, ddl_txt
2 from dba_redefinition_errors;
OBJECT_TYPE OBJECT BASE_TABLE_NAME
------------ ------ --------------------
DDL_TXT
-------------------------------------------------------
INDEX PHIL SO33070_ORIGINAL
CREATE UNIQUE INDEX "PHIL"."TMP$$_SYS_C00110330" ON "PH
IL"."SO33070_NEW" ("SERIA
SQL>
SQL> exec DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname=>'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW');
PL/SQL procedure successfully completed.
SQL>
You have 2 options to fix this.
Option 1 - create the intermediate table without a primary key (best option!!):
SQL> CREATE TABLE SO33070_ORIGINAL
2 (
3 SERIAL_ID NUMBER(15,0),
4 INSERTED_TIME DATE DEFAULT SYSDATE,
5 PRIMARY KEY (SERIAL_ID)
6 );
Table created.
SQL>
SQL> CREATE TABLE SO33070_NEW
2 (
3 SERIAL_ID NUMBER(15,0),
4 INSERTED_TIME DATE DEFAULT SYSDATE
5 )
6 PARTITION BY RANGE ("INSERTED_TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
7 (
8 PARTITION "p1_1" VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
9 );
Table created.
SQL>
SQL>
SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'PHIL', orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 num_errors NUMBER;
3 BEGIN
4
5 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW', copy_indexes=>dbms_redefinition.cons_orig_params , copy_triggers=>TRUE, copy_constraints=>TRUE, copy_privileges=>TRUE, ignore_errors=>TRUE, num_errors=>num_errors, copy_statistics=>false);
6
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> col object_owner for a6
SQL> col base_table_name for a20
SQL> col ddl_txt for a55
SQL>
SQL> select object_type, object_owner, base_table_name, ddl_txt
2 from dba_redefinition_errors;
no rows selected
SQL>
SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW');
PL/SQL procedure successfully completed.
SQL>
Option 2 - simply tell DBMS_REDEFINITION
to ignore the error (ignore_errors=>true
).
It looks like there are some errors, but they don't matter as the PK constraint and index was created when the intermediate table was created:
SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'PHIL', orig_table => 'SO33070_ORIGINAL', int_table => 'SO33070_NEW', col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 num_errors NUMBER;
3 BEGIN
4
5 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW', copy_indexes=>dbms_redefinition.cons_orig_params , copy_triggers=>TRUE, copy_constraints=>TRUE, copy_privileges=>TRUE, ignore_errors=>TRUE, num_errors=>num_errors, copy_statistics=>false);
6
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> col object_owner for a6
SQL> col base_table_name for a20
SQL> col ddl_txt for a55
SQL>
SQL> select object_type, object_owner, base_table_name, ddl_txt
2 from dba_redefinition_errors;
OBJECT_TYPE OBJECT BASE_TABLE_NAME
------------ ------ --------------------
DDL_TXT
-------------------------------------------------------
INDEX PHIL SO33070_ORIGINAL
CREATE UNIQUE INDEX "PHIL"."TMP$$_SYS_C00110410" ON "PH
IL"."SO33070_NEW" ("SERIA
CONSTRAINT PHIL SO33070_ORIGINAL
ALTER TABLE "PHIL"."SO33070_NEW" ADD CONSTRAINT "TMP$$_
SYS_C00110410" PRIMARY KE
SQL>
SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'PHIL', orig_table=>'SO33070_ORIGINAL', int_table=>'SO33070_NEW');
PL/SQL procedure successfully completed.
SQL>
Best Answer
Oracle text search filter is capable of automatically parsing non-plain text documents, such as MS Word or PDF, while Postgres' text search can only handle plain text documents. You'll have to convert whatever is stored in your BLOB column to plain text before loading those data into a Postgres TEXT column, then build the Postgres text search index on that.