Newbie question on index vs data tablespaces

[jewilson2]

Hi.

We have 2 separate tablespaces for our data and our index. My question is, what causes the index space to grow (often times faster than the data tblspace)? For example, I have 2 separate Oracle instances (PROD and TEST) and we often truncate testdata and copy proddata into it. Everytime we do this, the index tablespace grows and grows and grows.....space is never "freed up" in the index tablespace when we truncate.

Another funny thing happened as well....we created 2 brand new tablespaces (one for index, one for data) on the TEST schema. From scratch we set the data and index tablespace to what PROD was currently using. We did a export from prod and an import into test and the index tblspace ran out of room before it was done. How is it possible that an exact copy of one index tablespace somehow be larger when imported into another?

I hope this makes sense...

Thanks to all.

 

[LKBrwnDBA]

GEMLINS???

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: Maybe you are using Windoze?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[jewilson2]

Nah, it's an AIX 5.2 box.

I just dropped and rebuilt all the indices and the size went down 7gig!

When I do a exp of a table with indexes=n and an import of that same table in another schema with indexes=n, what causes the index tablespace to grow?