Re: ORA-00376: what are my options?

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote

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

My boss is management-challenged. I keep telling him about the value of backups, but he lives on another planet.

You know, he is one of those guys who think that RDBMSes are just a little bit better filesystems. His premise is "Oracle should run forever automatically, without supervision"! I'm serious.

A story for Dilbert, I guess.

But let's get back to the problem:

> 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.

I have TOAD. So I just have to find all index-related constraints, drop them, and DROP TABLESPACE INDX INCLUDING CONTENTS? Sounds easy. I have only around 30 tables. Thanks,

> 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.
Received on Sun May 18 2003 - 07:14:59 CDT