Re: problems with lost datafile

I have never encountered this problem myself, but, try the following:

1. Create a new tablespace for these indexes, something with a different name than the one you lost.
2. Write sql to create sql to rebuild all invalid indexes or all indexes that were lost in the missing tablespace. Basically you want select 'alter index' ,owner||'.'||index_name, 'rebuild tablespace NEW_TABLESPACE;' from dba_indexes where tablespace_name = 'YOUR_LOST_TABLESPACE'
3. Take the output of #2 and run it to perform the rebuilds. I've done something similar to the above when I've needed to rebuild indexes.
4. Once all of the indexes are rebuilt from the missing tablespace, you should be able to perform an offline drop of the missing tablespace. Note that you must do the tablespace and not the datafile, as the datafile name will stick around in the data dictionary until the tablespace is completely dropped.

HTH,
Pete's

Edvinas_at_takas.lt (Edvinas) wrote in message news:<367c7fb8.0201230254.197bc43f_at_posting.google.com>...
> hi all,
>
> i have very interesnisg problem.
> One oracle database file was lost,
> it was a datafile of indexes tablespace.
> i wantes to startup database, so i droped it:
> alter database datafile 'xxx' ofline drop;
> then: alter database open;
> all is ok. database online... but of course indexes are not good. i
> tried to drop tablespace, but i could not.
> i tried to: alter database create datafile 'xxx';
> it creates the file, but what next ?
> what i must do, to rebuild all these bad indexes in new datafile ?
> i have not any ARC.
Received on Wed Jan 23 2002 - 08:17:19 CST