-
a while back I posted about having thousands and thousands of extents across our database. Its been decided that we are going to export that data, redo the tables and then import that data back in.
However some of the tables contain loads and loads of data and we simply dont have the diskspace available to export it it one go.
The tables are partitioned so I would like to export the data partition by partition and do it this way.
So...... how do I export a partition at a time or many at once..
I asked this before but ill ask again anyway, how do I find out the average row length of an index?
cheers
-
Hi,
Instead of exporting/importing I would simply use the ALTER TABLE MOVE (PARTITION) command and move the tables to a new tablespace.
http://download-east.oracle.com/docs...2a.htm#2055687
With ALTER INDEX REBUILD you can also move indexes.
I have to admit that I dont know if there is any disadvantage compared to an export/import. But it will certainly be faster because you dont need anymore to write the data to a flat file.
About your question how to export partitions: to export the partitions P1 and P2 of the table EMP you have to specify TABLES=(EMP:P1,EMP:P2)
Because an index is organized like a tree I'm not sure that you can find something like an average row length of an index (or what do you mean exactly by that?).
-
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 :-)
Thanks
-
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).
have a look at
http://download-east.oracle.com/docs...schem.htm#3380
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?
Im up for doing a bit of everything, least ill learn :-)
-
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.
FYI: it is not posible to rename a tablespace
