Create yourself a script and rebuild it online...

Script......
set pages 5000
set echo off
set heading off
set feedback off
set linesize 180
spool scr_rbld_indx.sql
select 'spool dropdata.lis' from dual
/
SELECT 'ALTER INDEX '|| INDEX_NAME ||' rebuild nologging online
STORAGE(initial 10K next 10K maxextents unlimited pctincrease 0)
tablespace ' ||'&1'||';'
FROM USER_INDEXES
WHERE PARTITIONED='NO'
ORDER by INDEX_NAME
/
select 'exit;' from dual
/
exit;

then just run the scr_rbld_indx.sql script


could probably be done better but it works...This is assuming you are at
an oracle version that allows rebuilding online. the &1 variable is the
new tablespace you want to move the indexes too.

Ed


-----Original Message-----
From: SIDDY SOWA
[mailto:oracledba-ezmlmshield-x47760739.[Email address protected]
Sent: Friday, October 29, 2004 8:44 AM
To: LazyDBA Discussion
Subject: Indexes and tables in one tablespace


Hello Evryone,
Thank God its Friday!
But I have a headache, and please help, so that I get
a peaceful weekend.

My developers have placed about 200 indexes in the
same tablespace with about 200 tables.

We all know that this is not a good practice, not at
all. My first thought is to drop the indexes one after
the other and rebuild them in a different tablespace,
say indx_tbs, but this will of course take that long.

Could anyone think of a quicker way of doing this?

My sincere gratitude in advance.
Siddy



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html


Oracle LazyDBA home page