Forum Stats

  • 3,824,780 Users
  • 2,260,417 Discussions
  • 7,896,310 Comments

Discussions

Moving text tablespace

Bud Light
Bud Light Member Posts: 70 Blue Ribbon
edited Nov 9, 2017 7:59AM in Text

I think I already know the answer to this but wanted to confirm with the Experts.

If I create a Text index with my own storage preference so everything goes to a specific tablespace:

begin
ctx_ddl.create_preference('TEST_STORAGE', 'BASIC_STORAGE');
ctx_ddl.set_attribute('TEST_STORAGE', 'I_TABLE_CLAUSE', 'tablespace ctx_objects');
ctx_ddl.set_attribute('TEST_STORAGE', 'K_TABLE_CLAUSE', 'tablespace ctx_objects');
ctx_ddl.set_attribute('TEST_STORAGE', 'R_TABLE_CLAUSE', 'tablespace ctx_objects lob (data) store as (disable storage in row cache)');
ctx_ddl.set_attribute('TEST_STORAGE', 'N_TABLE_CLAUSE', 'tablespace ctx_objects');
ctx_ddl.set_attribute('TEST_STORAGE', 'I_INDEX_CLAUSE', 'tablespace ctx_objects compress 2');
ctx_ddl.set_attribute('TEST_STORAGE', 'P_TABLE_CLAUSE', 'tablespace ctx_objects');
ctx_ddl.set_attribute('TEST_STORAGE', 'S_TABLE_CLAUSE', 'tablespace ctx_objects');
END;
/

Can I move the base tables to a new tablespace without dropping and recreating the index?

Best Answer

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Nov 8, 2017 4:30PM Answer ✓

    Basically, no. The indexes contain ROWID values which would change if you moved the base table to a different tablespace.

    If you wanted to be adventurous, it just might be possible.  The $K table contains all the ROWID values in readable form. If you kept track of how the ROWIDs changed when you moved the table, you could update the $K table to reflect the new ROWID values. You could then rebuild the $R table from the $K table (we have a script for that), and that should give you a working index.  But you'd still need to convince the kernel that the index was still valid, which would probably require something like dropping the index, recreating it with NOPOPULATE after moving the base table, then manually populating the $I, $R, $K etc tables from previously saved contents. Needless to say this is risky, and wouldn't be supported.

    Oracle Text indexes work fine with transportable tablespaces, but I'm not sure that will help you here.

Answers

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Nov 8, 2017 4:30PM Answer ✓

    Basically, no. The indexes contain ROWID values which would change if you moved the base table to a different tablespace.

    If you wanted to be adventurous, it just might be possible.  The $K table contains all the ROWID values in readable form. If you kept track of how the ROWIDs changed when you moved the table, you could update the $K table to reflect the new ROWID values. You could then rebuild the $R table from the $K table (we have a script for that), and that should give you a working index.  But you'd still need to convince the kernel that the index was still valid, which would probably require something like dropping the index, recreating it with NOPOPULATE after moving the base table, then manually populating the $I, $R, $K etc tables from previously saved contents. Needless to say this is risky, and wouldn't be supported.

    Oracle Text indexes work fine with transportable tablespaces, but I'm not sure that will help you here.

  • Bud Light
    Bud Light Member Posts: 70 Blue Ribbon
    edited Nov 9, 2017 7:59AM

    I wasn't even thinking of the actual base table the index was on.  I was initially speaking about the underlying base Text index tables but you answered the question anyway.  We are moving the main tables so we'll need to rebuild the index anyway.

    Thanks again!

This discussion has been closed.