Dear Amit.
The first thing I see is that you are still using the old tablespace layout with DMTS tablespaces. This, on its own can be the cause for this huge waste of space. As you may already know in DMTS every object can have different extent sizes, this can lead to tablespace fragmentation.
If you would reorganize your tablespaces to the new tablespace layout and so to LMTS/ASSM (sap note 355771), the fragmentation will drop due to much more less different sizes of extents (64 KB,1MB,8MB,64MB,128MB). This makes your life as an administrator not just easier, but you can use some new features as of Oracle 10g. Because some features can not be used for DMTS tablespaces.
Long story short:
Short term solution: reorganization within the same tablespace structure
positiv effect: less fragmentation
negativ effect: fragmentation and so waste of space will arise again and again in a short time
can not use some of the new features of recent Oracle releases
Long term solution: reorganize to LMTS/ASSM
positiv effect: less fragmentation, easier administration, I/O load is slightly better, new fetures can be used
negativ effect: takes time to reorganize all tablespaces
Regards,
János
Bluesky
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.