Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What to do when"index made unusable by..." on loading of large table?

Re: What to do when"index made unusable by..." on loading of large table?

From: Ian Cary <cary_at_gatwick.geco-prakla.slb.com>
Date: 1997/11/28
Message-ID: <347EE2F0.16BBC3C4@gatwick.geco-prakla.slb.com>#1/1

Hi again Kelly,

Adding a large datafile to the SYSTEM will help solve your problem, but it is not an approach I would recommend.

The problem you encountered is because there is insufficient space in the SYSTEM tablespace to allocate the temporary segments required to sort your indexes. It is normally poor practice to allow these segments to be created in the SYSTEM tablespace, and is far better to create them in a dedicated TEMP tablespace.
e.g.

create tablespace TEMP datafile 'temp location' size nM temporary;

where n is the size of the datafile in Megs. The temporary clause is a new feature (7.3 and above) and actually ensures that the tablespace can only contain temporary segments.

The tablespace that is actually used to store temporary segements is defined by the USERS temporary tablespace. If you run the command;

select username, temporary_tablespace from dba_users;

I suspect you will see a lot of lines saying SYSTEM. For each user affected you need to run the command;

alter user <username> temporary tablespace TEMP; -- where TEMP is the tablespace created above.

As far as your indexes go I have a feeling that you maybe able to recreate them with the following command;

alter index <index_name> rebuild unrecoverable;

The unrecoverable option is optional but will prevent re-do information from being written, thereby speeding up the process. If this does not work you may have to issue fresh create index commands

Hope all this helps.

Cheers,

Ian Received on Fri Nov 28 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US