If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

The only MOVE disadvantage I can think of is that you need to be able to store two copies of the data on the database at once during the move -- with exp/imp you would drop the original (though you could just rename it) before imp. Other than that the MOVE is much more robust.

Don't foget to rebuild the index partitions also. The move will make them invalid.

Basically have no space to move data around or to rebuild indexes in the same tablespace, cant do it :-(

so looks like exp/imp is only option here. As for the indexes they are shafted as well so they are going to be re-created.

Basic problem was huge tablespaces initial extent was only 32K as was next extent (for each partition and its index) each partition a million rows and therefore we have cracking 250,000 extents and during runtime of the application, truncates happen, so bad news. That why we need re-do the tables with a better next extent keeping the initial extent the same so hopefully will only extend once or twice.

As for the avg_row_length bit..

In user_tables you can find out the avg_row_len of a table (after analyzing it) and I wanted to do the same thing for an index.

I can do it manually by seeing what column the index is on in the table and finding its datatype, e.g a date filed takes up 8 bytes, number(10) 10 bytes and varchar2(35) 35 bytes. So i can add them all up and find out how many bytes one row of the index will take. This however will give me the maximum. i want the average size of a row becuase we dont use all 35 bytes of a varchar2(35).

Only thing i have come up with is to use
analyze index xxx validate structure and then look in index_stats for something, but I dont know what im looking at in this table so I need help :-)

what you could do to gain some space is to drop your indexes, because you will anyway have to rebuild them.
I guess that you put your indexes on a seperate tablespace, so once the indexes dropped you can drop the tablespace and reuse the space to move your tables/partitions or do your import exports. You should of course do an export of your indexes definitions before dropping them.

How dou you plan to do your export when you dont have enough disk space? on a tape?

about your index question:
I'm not an expert but because you don't just only have data in an index you cant have something like an average row length.
An index consists of a b-tree (branch blocks) and the diffent distinct values and related rowids (leaf block).

So if you want to know something like an average row lenth you could take from ALL_INDEXES the LEAF_BLOCKS value divide it by the NUM_ROWS and then multiplied by your block size to got the results in bytes.But the result for the same kind of index will depend a lot of the value of DISTINCT_KEYS. By doing this you will only know the average space for the leaf blocks the space need for the B-tree (branch blocks) is not included.

thanks for the info, i'll give that a go, as for the about dropping the indexes and moving tablespaces. The data would have to go back to the original tablespaces, due to the application specifying tablespaces for creating other things, so is it quicker to export then import partition by partition or drop index, move and then move back?

You dont need to move your table/partition on a new tablespace. You can move it to the same tablespace, the segment is then just rebuild. You need to have enough space to be able to have as well the new table/partition as the old one on the same tablespace. As I said you could drop your indexes and index tablespace and reuse it to increase the size of your "data" tablespaces.

When you use the move command dont forget to specify the new storage attributes.