Rebuild indexes

[pascal01]

I need to move 1500 indexes to another tablespace. Instead of creating a script to rebuild the indexes, is there a DBS command that I can run? Please let me know. thanks.

[sreddy]

did you thought about import ?
$import with rows=n and ignore=y grants=n .This will take the same time to create indexs thru script, only avoids creating script part.

transpotable tablespaces you can take advantage of (8i onwards) if you have all indexes and in one tablespaces and figureout way to plugin/attach this tablespace.

[pascal01]

Is there a "dbms_schema_rebuild_index" command or something similar?

[dknight]

You can use something *like*

select "ALTER INDEX "¢®¢® index_name ¢®¢® "TABLESPACE blablabla"
from dba_indexes
where ...

The basic idea works to change the tablespace. You need to replace the ¢®¢® with concatenation and identify screening conditions.

Good luck.

[Bhavin Hingu]

Rebuild indexes
I need to move 1500 indexes to another tablespace. Instead of creating a script to rebuild the indexes, is there a DBS command that I can run? Please let me

-------------------------------------------------------------

Hi,
you can do it by writing a PL/SQL code that uses dynamic SQL.

i.e,

DECLARE
CURSOR cur_name IS
(LIST OF all the index from dba_indexes);
BEGIN
open the cursor for dynamic sql.
FOR vname IN cur_name LOOP
you can use dynamic sql here.
END LOOP;
close the opened cursor.
END;
/

Thanks..
Bhavin

[slimdave]

Take the opportunity to order the result of the select from dba_indexes by table_name -- if you're just going to run the generated script you'll get some performance benefits.

[marist89]

Originally posted by slimdave
Take the opportunity to order the result of the select from dba_indexes by table_name -- if you're just going to run the generated script you'll get some performance benefits.

I know I shouldn't even touch this with a 10 foot pole, but...

Why?

[slimdave]

You know, cancel that comment -- i was thinking of the situation in which you are rebuilding unusable indexes, where the table itself gets read. In this case it's the "old" indexes that get read, so never mind.

Too much data warehousing is taking it's toll on my mind. We rebuild unusable indexes so often it's developed into an obsession.

[grjohnson]

1500 indexes to another tablespace... one has to ask the question... why are 1500 indees in the incorrect tablespace in teh first place? And what is the qualifier to detemine they are in the incorect tablespace. Is it a space issue or a performance issue, or simple a cosmetic administrative issue?

[Shashi]

U r right johnson, I guess its an admistrative issue. According to me, i think moving the indexes in seperate tablespace and then take a backup of that, is the best idea .. isn't it guys??