Oracle 全文索引的表空间转移

   工作中经常会转移表空间，表和一般的索引都没有什么问题，如果表有全文索引的话就需要做特别的处理，不能将全文索引和普通索引一样通过rebuild index来处理，需要将此全文索引先删除然后重建。具体方法如下：

1.如果没有建立preference的话，那么Oracle会将这些对象存放到默认表空间中，并根据默认表空间的存储参数设置这些对象的存储参数。

CONTEXT索引生成表的规则是DR$+索引名+$+表用途标识。

DR$IND_T_DOCS$I存储的是索引数据表(Index data table)；

DR$IND_T_DOCS$K存储的是键值映射表(Keymap table)；

DR$IND_T_DOCS$R是ROWID表(Rowid table)；

DR$IND_T_DOCS$N是负键值链表(Negative list table)；

DR$IND_T_DOCS$P这个表只有在CONTEXT索引中设置BASIC_WORDLIST的SUBSTRING_INDEX属性后才会生成，用来保存单词的部分内容。

用户tnsh的默认表空间是TNS2008,所以这些索引对象都放在了TNS2008.

SQL> select default_tablespace from dba_users where username='TNSH';

DEFAULT_TABLESPACE
------------------------------
TNS2008

SQL> drop index TNSH.I_PRODUCT_NAME_3;

Index dropped.

SQL> CREATE INDEX TNSH.I_PRODUCT_NAME_3 ON TNSH.PRODUCT_NAME
  2  (PRODUCT_NAME)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS('SYNC ( ON COMMIT)')
  5  NOPARALLEL;

SQL> select owner,index_name,index_type,table_name,tablespace_name
  2  from dba_indexes where owner='TNSH' and table_name  like '%PRODUCT_NAME_3%';

OWNER  INDEX_NAME                     INDEX_TYPE      TABLE_NAME                               TABLESPACE
------ ------------------------------ --------------- ---------------------------------------- ----------
TNSH   SYS_IOT_TOP_494187             IOT - TOP       DR$I_PRODUCT_NAME_3$N                    TNS2008
TNSH   SYS_IOT_TOP_494182             IOT - TOP       DR$I_PRODUCT_NAME_3$K                    TNS2008
TNSH   SYS_IL0000494184C00002$$       LOB             DR$I_PRODUCT_NAME_3$R                    TNS2008
TNSH   SYS_IL0000494179C00006$$       LOB             DR$I_PRODUCT_NAME_3$I                    TNS2008
TNSH   DR$I_PRODUCT_NAME_3$X          NORMAL          DR$I_PRODUCT_NAME_3$I                    TNS2008

由于这个索引 没有使用BASIC_WORDLIST的SUBSTRING_INDEX，所以没有DR$IND_T_DOCS$P的表。

更改默认表空间后试一下：

SQL> alter user tnsh default tablespace  tns2008_TMP;

User altered.

SQL> drop index TNSH.I_PRODUCT_NAME_3;

Index dropped.

SQL> CREATE INDEX TNSH.I_PRODUCT_NAME_3 ON TNSH.PRODUCT_NAME
  2  (PRODUCT_NAME)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS('SYNC ( ON COMMIT)')
  5  NOPARALLEL;

Index created.

SQL> select owner,index_name,index_type,table_name,tablespace_name
  2  from dba_indexes where owner='TNSH' and table_name  like '%PRODUCT_NAME_3%';

OWNER  INDEX_NAME                     INDEX_TYPE      TABLE_NAME                               TABLESPACE_NAME
------ ------------------------------ --------------- ---------------------------------------- ---------------
TNSH   SYS_IOT_TOP_494199             IOT - TOP       DR$I_PRODUCT_NAME_3$N                    TNS2008_TMP
TNSH   SYS_IOT_TOP_494194             IOT - TOP       DR$I_PRODUCT_NAME_3$K                    TNS2008_TMP
TNSH   SYS_IL0000494196C00002$$       LOB             DR$I_PRODUCT_NAME_3$R                    TNS2008_TMP
TNSH   SYS_IL0000494191C00006$$       LOB             DR$I_PRODUCT_NAME_3$I                    TNS2008_TMP
TNSH   DR$I_PRODUCT_NAME_3$X          NORMAL          DR$I_PRODUCT_NAME_3$I                    TNS2008_TMP

这些索引的表空间使用了默认的表空间。

所以在没有使用preferences的时候，更改用户的默认表空间后重建全文索引就可以了。

 

如果不能通过改变默认表空间的办法，就需要指定preferences了。

建立一个新的preference，最好在ctxsys用户下建立：

SQL> conn ctxsys/ctxsys
Connected.
SQL>  exec CTX_DDL.CREATE_PREFERENCE('TNSH_STORAGE', 'BASIC_STORAGE');

PL/SQL procedure successfully completed.

SQL> begin
  2   CTX_DDL.SET_ATTRIBUTE('TNSH_STORAGE', 'I_TABLE_CLAUSE', 'TABLESPACE TNS2008_TMP');
  3   CTX_DDL.SET_ATTRIBUTE('TNSH_STORAGE', 'K_TABLE_CLAUSE', 'TABLESPACE TNS2008_TMP');
  4   CTX_DDL.SET_ATTRIBUTE('TNSH_STORAGE', 'R_TABLE_CLAUSE', 'TABLESPACE TNS2008_TMP');
  5   CTX_DDL.SET_ATTRIBUTE('TNSH_STORAGE', 'N_TABLE_CLAUSE', 'TABLESPACE TNS2008_TMP');
  6   CTX_DDL.SET_ATTRIBUTE('TNSH_STORAGE', 'P_TABLE_CLAUSE', 'TABLESPACE TNS2008_TMP');
  7   CTX_DDL.SET_ATTRIBUTE('TNSH_STORAGE', 'I_INDEX_CLAUSE', 'TABLESPACE TNS2008_TMP');
  8   end;
  9  /

PL/SQL procedure successfully completed.

SQL> conn l5m/l5m
Connected.

SQL> alter user tnsh default tablespace  tns2008;

User altered.

SQL> drop index TNSH.I_PRODUCT_NAME_3;

Index dropped.

SQL> CREATE INDEX TNSH.I_PRODUCT_NAME_3 ON TNSH.PRODUCT_NAME
  2  (PRODUCT_NAME)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS('SYNC ( ON COMMIT) STORAGE CTXSYS.TNSH_STORAGE');  

Index created.

SQL> select owner,index_name,index_type,table_name,tablespace_name
  2  from dba_indexes where owner='TNSH' and table_name  like '%PRODUCT_NAME_3%';

OWNER  INDEX_NAME                     INDEX_TYPE      TABLE_NAME                               TABLESPACE_NAME
------ ------------------------------ --------------- ---------------------------------------- ---------------
TNSH   SYS_IOT_TOP_494214             IOT - TOP       DR$I_PRODUCT_NAME_3$N                    TNS2008_TMP
TNSH   SYS_IOT_TOP_494209             IOT - TOP       DR$I_PRODUCT_NAME_3$K                    TNS2008_TMP
TNSH   SYS_IL0000494211C00002$$       LOB             DR$I_PRODUCT_NAME_3$R                    TNS2008_TMP
TNSH   SYS_IL0000494206C00006$$       LOB             DR$I_PRODUCT_NAME_3$I                    TNS2008_TMP
TNSH   DR$I_PRODUCT_NAME_3$X          NORMAL          DR$I_PRODUCT_NAME_3$I                    TNS2008_TMP

这时全文索引所使用的表空间就使用了preference所设定的表空间了。

preference相关的视图：

查看所有preference，CTS_PREFERENCES

SQL> select * from CTX_PREFERENCES where pre_name='TNSH_STORAGE';

PRE_OWNER                      PRE_NAME                       PRE_CLASS                      PRE_OBJECT
------------------------------ ------------------------------ ------------------------------ ------------------------------
CTXSYS                         TNSH_STORAGE                   STORAGE                        BASIC_STORAGE

查看preference的值,CTX_PREFERENCE_VALUES

SQL>  select * from CTX_PREFERENCE_VALUES  where prv_preference='TNSH_STORAGE';

PRV_OWNER  PRV_PREFERENCE       PRV_ATTRIBUTE        PRV_VALUE
---------- -------------------- -------------------- --------------------------------------------------
CTXSYS     TNSH_STORAGE         I_TABLE_CLAUSE       TABLESPACE TNS2008_TMP
CTXSYS     TNSH_STORAGE         K_TABLE_CLAUSE       TABLESPACE TNS2008_TMP
CTXSYS     TNSH_STORAGE         R_TABLE_CLAUSE       TABLESPACE TNS2008_TMP
CTXSYS     TNSH_STORAGE         N_TABLE_CLAUSE       TABLESPACE TNS2008_TMP
CTXSYS     TNSH_STORAGE         I_INDEX_CLAUSE       TABLESPACE TNS2008_TMP
CTXSYS     TNSH_STORAGE         P_TABLE_CLAUSE       TABLESPACE TNS2008_TMP

6 rows selected.

更多的视图参考文档:

https://docs.oracle.com/cd/B19306_01/text.102/b14218/toc.htm