Home » RDBMS Server » Server Administration » problem with indexes
problem with indexes [message #369473] Thu, 28 September 2000 22:29 Go to next message
Berkan
Messages: 1
Registered: September 2000
Junior Member
Hi,

I'm a system admin, not a qualified oracle dba, but at
the moment I have to do both. And I think I've hit a
problem with indexes.

Some time ago, I decided to rebuild the indexes in our
main tablespace (MAIN), in order to both put them into
a separate tablespace (INDX) and change their storage
parameters. For that, I used something like

select 'alter index '||index_name||' rebuild storage '||
'(INITIAL 100k NEXT 100k pctincrease 0 maxextents unlimited minextents
1)'||
' tablespace INDX;'
from user_indexes where next_extent <= 100*1024;

The first time I ran this, however, I ran it as the
SYSTEM user, so some of the indexes in the SYSTEM
tablespace ended up in INDX (the ones that weren't
rebuilt were the ones oracle refused to do so). Hence
my first question: have I done something terribly
wrong? Are there any adverse consequences of having
SYSTEM related indexes in another tablespace? If so,
is there any way I can reverse the situation, and how?

I then ran the statement above as the correct user
and rebuilt the correct indexes (the ones in MAIN) into
INDX. However, I did this not once but several times,
and soon realized that the INDX tablespace has grown
considerably. My second question is whether or not
this is the normal behaviour (because initialy I had
thought that rebuilding an index is not much different
than dropping and recreating it, and hence this
wouldn't change its size if the indexed table hadn't
changed -- now I can see this may not be the case).

Now I have an INDX tablespace which is 100% full, and
possibly containing SYSTEM related indexes which aren't
supposed to be there. Even if there is nothing wrong
with the latter, I still would like correct the size
sitution properly (rather than simply enlarging the
INDX tablespace).

There are ~700 indexes, and I was wondering if somebody
can suggest an automated method to drop and recreate
them. In fact, and indeed more importantly, is this the
way to go, or is there another solution?

Cheers,

Berkan
Re: problem with indexes [message #369474 is a reply to message #369473] Fri, 29 September 2000 03:46 Go to previous message
Miki
Messages: 11
Registered: August 2000
Junior Member
I guess to recreate SYSTEM indexes just run
CATALOG.SQL and CATPROC.SQL scripts again
if you are running Oracle73.

To recreate non-system indexes take a look at
http://www.orafaq.com/scripts/sql/idxrecr8.txt
or
http://www.orafaq.com/scripts/sql/idxrebld.txt
Previous Topic: Complex Query Help
Next Topic: Oracle8 Transparent Gateway for Sybase
Goto Forum:
  


Current Time: Fri Mar 29 10:35:28 CDT 2024