Printable View
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
thanks,
HELP ME....
Hi
well if you are on 8i ...then no need to do import export..just create a new tablespace locally managed and then do
1)alter table move new_tablespace
2)rebuild all the indexes on that table in nologging mode thats it..
You will reduce fragmentattion automagically..:-)
i dont have a clue about your second problem..
regards
Hrishy
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?
NO ERRORS .IT JUST STUCK THERE
AND ALSO REBUILDING INDEXES WILL FREE UP SPACE BUT NOT SHRINK DATAFILE
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!).Quote:
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....
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.)
As Dapi allready said, I would rebuild your index on a new tablespace so that afterwards you can drop your old 10GB tbs.
Are you using the COMMIT=Y for the IMP? If in 9i and using RESUMABLE, it could be waiting for an extension of the datafile.Quote:
Originally posted by kpate
NO ERRORS .IT JUST STUCK THERE