Oracle-L: RE: Context indexes

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Wed, 14 Aug 2002 07:43:29 -0800
Message-ID: <F001.004B4922.20020814074329@fatcity.com>

When you create a context index, you create a number of tables. These tables are named DR$<INDEX_NAME>$I, DR$<INDEX_NAME>$K, DR$<INDEX_NAME>$N, and DR$<INDEX_NAME$>R. There are indexes on these tables. The "I" and the "R" tables contain BLOBS and thus have lob indexes. The "K" and "N" tables are IOT's and have a "IOT - TOP" indexes. In addition the "I" table indexed by normal index named "DR$<INDEX_NAME>$X. All these things were created in the users' default directory unless you created a storage preference and included that as a parameter when you created the indexes. You may also have a DR$<INDEX_NAME>$P table and its associated indexes.

In addition to these, in the system tablespace, there is an index which has the name of the context index you created. However there is no segment associated with the index, and thus no "segment" to move What the index comprises is listed in the first paragraph.

One way to move all the tings mentioned in the first paragraph is to do something like

alter index <index_name> rebuild
parameters('storage <storage_preference> ...)

I'm not sure if you need to include all the parameters you had when the index was first built; hence, the ellipsis.

You may be able to use alter table move to move the tables mentioned above and rebuild whatever indexes became "unusable." I have not tried this!!!

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu

-----Original Message-----
Sent: Wednesday, August 14, 2002 12:08 AM To: Multiple recipients of list ORACLE-L

Hello All,

Can somebody help me in changing the parameters/re-org of context indexes:

I have 10 context indexes in my database. I want

to move these indexes to different tablespace
change storage clause
Want to get the DDL for these indexes ( for re-organization)

PS: I guess the move to new tablespace and storage change can be done in single shot.(like normal indexes)

This is really urgent and needs to be done in production by tomorrow.

Thanks in advance
Srinivas

Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: kommareddy sreenivasa
INET: kommacnu_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: MacGregor, Ian A.
INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------