Question;

Is there any way that we can index a table on one machine, and then move or
copy the index to another machine which has the same table ?

If so, how can we do that ??

Answer 1:

1.Not possible, You need to recreate the index on the new machine.If you are
just moving one or more table using export/import or using a db link.This is
coz, index hold the physical address of rows ( ROW IDS ). Row IDS change when
you move data.
2.If your are duplicating the entire database by restoring the cold backup, no
need to recreate the index.

Answer2:

No way... the index stores the rowids of the data, which would be
different if you move it to another machine.

Otherwise, the only way would be to cold backup the whole database and
move it over.

Answer 3:

Although the indexes effectively have to be recreated as others have stated,
the easiest way to do this is to get the details from the source database's
data
dictionary via a db link.  Interrogate the ALL_INDEXES (or USER_INDEXES)
view to
determine the indexes available, then get all the columns in each index via
ALL_IND_COLUMNS.

Then it's straightforward to generate the CREATE INDEX statement and execute
it
on the target database using dynamic SQL.

If you'd like the source code for this, email me directly.

Question 2:

Okay I have this situation.

I am using Oracle 8i (8.1.5)
I have a machine which is to be constantly online and is used as a datastore
for searching

Can I use another machine to re-index the database on the online machine ?
So that the online machine will not take any cpu or memory when indexing.

Answer1 to Question 2:

the method i mentioned will not fulfil your requirements.  it will replicate
indexes on the second database from the first, however ROWIDs will no doubt
be different between both so can't see how the indexes on the second could
be used to satisfy a search on the first.

Answer 2 to question 2:

Yes and No....

In general, no, the indexing process is done by the database engine.  In a
standard configuration -- you have one instance mounting a single database.  the
indexing would take place on the machine the database instance is running on.
It should be noted that the index may be built 'online' which allows for full
read/write access to the base tables (you would have full read access if you do
not do this online).
 

Under Oracle Parallel Server, yes.  You have >1 instance that mounts and opens
the same database (set of files).  One instance can index, the other does other
stuff.

Answer 3:

You may want to look into Transportable
Tablespaces.  You can easily move tables and
indexes between instances by essentially copying
the associated data files (along with some
metadata).  You'll have to move the Index AND the
associated table but I think you'll like it.