We want to move the huge table with one BLOB filed and Oracle TEXT index from Small file tablespace to bigfile tablespace . therefore we have created new table in bogfile tablespace and we inserted data from old tables to this new table .
But creating Oracle TEXT index with the following syntax takes so long time and it dose not finish even after 4 days of ruuning .
the small file tablespace is TBSA and the new bigfile tablespace is TBSB . the main purpose is to migrate to new bogfile tablespace.
the table creation script is as following:
create table NEW
(
a VARCHAR2(30) not null,
b BLOB,
c VARCHAR2(60) not null,
d VARCHAR2(60) not null
) TBSB
I have populated data in this new table in new table tablespace with the following command:
insert / * + APPEND NOLOGGING PARALLEL * / into NEW (select / * + PARALLEL * / * from OLD;
----------------------
I wanted to create Oracle Text index on this new table but it was extremly slow :
-- Create/Recreate indexes
create index IDX_NEW on NEW (b) indextype is CTXSYS.CONTEXT;
---------------------------------------------
Could you plesae tell us how to create new oracle TEXT in this new table ? and generaly if it is possible could you tell us another solution to migrate such a huge table from one tablespace to another tablespace with data and indexes including text index?
I have done the following steps also :
Hi, the number of rows in source table with Text Index is 83102926. as I mentioned before it has BLOB filed .the table structure is : SAP NEW ( dok_key VARCHAR2(30) not null, befund BLOB, db_modtstamp VARCHAR2(60) not null, db_stotstamp VARCHAR2(60) not null ) the BLOB segment size is 9TB . I executed the index creation with PARALLEL but it was extremly slow. I extracted the index creation script from dbms_metadata from existing table . CREATE INDEX IDX_NEW ON NEW (BEFUND) INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('filter ctxsys.auto_filter LEXER CTXSYS.KISDC_BEFUND_LEXER DATASTORE CTXSYS.DEFAULT_DATASTORE' ----------------------------- I also run the create index to see the output logs but there was no log generated : EXEC CTX_OUTPUT.START_LOG('ctx_log'); EXEC CTX_OUTPUT.ADD_EVENT(CTX_OUTPUT.EVENT_INDEX_PRINT_ROWID); CREATE INDEX IDX_REOG2_LF ON NEW (BEFUND) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('filter ctxsys.auto_filter LEXER CTXSYS.KISDC_BEFUND_LEXER DATASTORE CTXSYS.DEFAULT_DATASTORE'); |
|
|