Skip to Content
avatar image
Former Member

Tablespace growth after DB reorg - Drastic


My environment is 4.6C, Oracle8i, HP UX 11i, SAPDBA

We perform archiving on regular basis(once in a year) and DB reorg after archiving to gain the space deleted by archiving and to eliminate fragmentation.

Recently, I did DB reorg in my production system by selecting top 20 large tables from PSAPBTABD tablespace.

Before, reorg the tablespace PSAPBTABD was 73% used. After reorganizing around 10 tables, the used % got reduced to 66%. But, with in 3 weeks gap, the PSAPBTABD tablespace has grown drastically and now the used % is back to 73%. We have 10 more tables left to perform DB reorganization in PSAPBTABD.

Could any one share your experience on the reason why PSAPBTABD`s growth is drastic after DB reorg? What should be done to avoid this drastic growth? In my next reorg of 10 tables, where should I concentrate to avoid drastic growth...

I suspect the next extend size which we choose during the DB reorg.

Please share your valuable experience on this regard.

Best regards,


Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 26, 2007 at 06:43 AM


    it is difficult to provide a good answer without a proper analysis ( and I do not think the forum is the best place to do a depth analysis )

    You are on oracle 8i and you have used SAPDBA for the reorganization.

    I assume you are using Dictionary managed tablespaces.

    In this case, it is possible that the reorganization has set ( or left if it was already set ) a big next extent on the reorganized tables ( for example lets assume that the next extent is set to 1Gb ). In that case, if the 20 tables need a new extent soon after the reorganization, they will allocate 1 Gb each causing a growth of 20 Gb.

    Some times it is not "worth" the reorganizations after the archiving. If the tables are not very static, then the "freed" space will be reused.

    Oracle 8i is long "out of customer care support", I even think the Extended maintenance has finished. The recommendation would be to upgrade to a newer release and use locally managed tablespaces.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Yeah, no matter what you set the next extent job to, if you schedule in the 'adapt next extent' job then this will adapt extents for all objects in the PSAP* tablespaces as you've configured.

      So, the next extent size is then determined by the actual size category of the table. If you are happy with your next extent sizes, stop the job, change your large next extents to a lower value. You can monitor tables with high number of extents and adjust manually in the time before you upgrade to a later version of Oracle.

      As for deleting entries out of tables, if you weren't to reorg, the PCTUSED/FREELISTS just determines whether the free space in the oracle block will be reused - worth having a read up on it. In later versions of Oracle, handled by ASSM.

  • avatar image
    Former Member
    Sep 26, 2007 at 02:13 AM

    Hi Vinod,

    I have been doing DB reorganizations and has been fine, the reason is the database version, we run on oracle 9i and some on 10g where extent allocation is uniform. Space management is improved in 9i and I would suggest to upgrade to 9i or 10g if its easy.

    I would advise you keep track of DBA_SEGMENTS view before reorganization, after reorganization and then periodically to compare and identify the object growth and extent allocations.



    Add comment
    10|10000 characters needed characters exceeded