Re: How to split indexes from data?

That is a very old TAR with Oracle; at least since 6.*. The best thing  

to do is to rebuild the indexes. Here is a simplified script I used

after I applied patches to my Oracle Applications environments. Of

course with Apps, I had a multitude of users and tablespaces but you

will gert the idea. Please modify to your likings.

• rbld idx.sql set head off set pages 0 set term off set feed off spool rbld $$.sql

select 'alter index '||owner||'.'||segment name||' rebuild tablespace

USER INDEX;'
from dba extents
where segment type = 'INDEX'
and tablespace name = 'USER DATA'
/
spool off
set term on
@rbld $$.sql
exit  

Ron Leedy

Mike Adams wrote:
>
 

> I have a new database that I created and will load using an export from
 

> another database. The indexes in the "source" database are stored in t
 he
> same tablespace as the tables. In my new database I want to separate t
 he
> indexes and store them in the INDEX tablespace. Is there any easy way
 to do
> this? (I'm running Oracle 8i)
>
 

> And going forward, how do I enforce the splitting of the indexes from t
 he
> tables. I know that the "create table" and "create index" commands can
 

> specify the tablespace for the index, but is there any way just to set
 a
> default for all users so they don't have to worry about it?
>
 

> Please 'cc' mikea_at_best.com on responses as I'm not able to check newsg
 roups
> from work.
>
 

> Thanks in advance for ANY help!!!
>
 

> Mike
>
 

>
Received on Thu May 04 2000 - 00:00:00 CDT