Hello,
i have a production database 50G.It seeems there is a lot of def. indexes.When i export and import on development server its 35G.
1)How do i implement in production to reduce size .I should take full backup then shrink tablespace then import!!!!.
2)We have exact same two development boxes.I am able to imp in one of
box and on other it looks like freeze after importing 10 tables ..any
clue
1) You seem to indicate that the majority of "lost" space in your production database comes from indexes? If you have alot of dead space in your indexes from massive inserts/deletes, you could just rebuild the indexes rather than perform the exp/imp routine. That will clear up space within a database. You didn't specify what version of Oracle you are using. If you are running 8i or better you could also rebuild/move tables to other tablespaces to reclaim space lost due to honeycomb fragmentation. Remember if you to this to use uniform extent sizes. Just having alot of extents does not indicate fragmentation.
2) As to this problem, are there any errors listed when the import stops?
Originally posted by kpate what will be the best way...
let say i have 10g tablespace after rebuilding indexes it uses 2gig.
can i shrink that tablespace to 5g....
Probably, it depends on where the the indexes physically end up in the TS. Usually they will be rebuilt towards the "bottom" end so this will work - it did for me. (I'm sure someone out there is now busy constructing a counter-example!).
If there are tables in the same TS, this will not work if you have one physically beyond the 5Gb mark.
(Note that you can use "alter index rebuild" to move indexes to a different TS, if that helps.)
Last edited by DaPi; 02-21-2003 at 11:02 AM.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Reply With Quote
Bookmarks