-
defragment tablespace
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,
-
-
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?
Eric Hanson
There are 10 types of people in the world:
Those who understand binary and those who don't!
-
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....
-
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 12:02 PM.
"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
-
As Dapi allready said, I would rebuild your index on a new tablespace so that afterwards you can drop your old 10GB tbs.
-
Originally posted by kpate
NO ERRORS .IT JUST STUCK THERE
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.
OCP 8i, 9i DBA
Brisbane Australia
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|