you can do it by writing a PL/SQL code that uses dynamic SQL.
CURSOR cur_name IS
(LIST OF all the index from dba_indexes);
open the cursor for dynamic sql.
FOR vname IN cur_name LOOP
you can use dynamic sql here.
close the opened cursor.
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...
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.
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?