Re: problems with lost datafile

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.

Edvinas, here is what you can do:

Use the information held in the rdbms dictionary tables to get a list of indexes in the tablespace, and generate their code unless you have the index code elsewhere. Also check for if any of the indexes are used to support primary or unique keys, generate their definitions as necessary. You should gererate the FK definitions for all involved FK since I think they can interfere with dropping the missing indexes.

Then when you have what you need to re-create the indexes 'drop tablespace lost including contents;'. Remove the remaining tablespace files at the OS level. Now recreate the tablespace and then re-create your indexes.

As they say BTDT.

• Mark D Powell --