Re: ORA-00376: what are my options?

"mar" <mar_at_foo.com> wrote in message
news:ba7nub$pdr28$1_at_ID-174077.news.dfncis.de...
> Hi group,
> My database croaked this morning and SQL*Plus returns
> ORA-00376: File 6 can not be read at this time
> ORA-01110: File 6: D:\ORACLE\ORADATA\DWPSS\INDX01.DBF
>
> What are my options? I don't have any backups, anything.
> Can I just drop this file somehow, and regenerate indexes?
>
> newbie on Win 2000 and Oracle 8.1.7 Enterprise.

Mmmmm. A newbie that needs to learn the values of backups, perhaps?

The problem you've got is that whilst you can merrily re-create indexes, dropping them is a whole lot harder. For a start, you can't drop indexes which are used to enforce Primary or Unique Key constraints. So you have to identify those constraints first, drop or disable them, and then you can drop the indexes.

Which is easier said than done.

So who said anything about dropping indexes??? You did. If you want to drop the file, you'll have to drop the tablespace. And you can't drop a tablespace if it's got anything in it. So you'll have to use the 'drop tablespace INDX including contents' command. Which won't work if any of the indexes within the tablespace are involved in Primary or Unique Key constraint enforcement.

Which brings us back to identifying which indexes are used to enforce which constaints. Which used to be easy when the constraint name was definitely equal to the index name... but that hasn't been true since 8.0. So it's hard.

All of which means that it might be a darn'd sight easier to invest in some decent backup and recovery procedures than to assume that indexes are disposable.

Regards
HJR Received on Sun May 18 2003 - 05:55:17 CDT