Re: Q: Reorg an SAP tablespace

Vince Laurent wrote:
> A bazillion years ago - give or take - we started with SAP.
> Right now one of the tablespaces, PSAPODSD, is 71G with about 6G free. The problem is it is SO fragmented that when the developers
> go to load more data that there isn't enough free space in the chunks it is looking for.
>
> My suggestion to the developers is to let me have some downtime and move it to a locally managed tablespace with same size extents
> so the 'chunks' it would be looking for would all the be same size. They are warm to the idea so I may get downtime soon.
>
> My question to the group is how to best size the tablespace?
>
> create tablespace PSAPODSD
> datafile ‘c:\oradata\blahblah.dbf’ size 2G
> extent management local
> uniform size ???K;
>
> The individiual tables that make up this tablespace range in sizes from 17G to 16K. For example:
>
> Object Type Tablespace KBytes
> /BIC/AZRC_O5000 TABLE PSAPODSD 5,488,656
> /BIC/B00002570000000000002 TABLE PART PSAPODSD 1,679,376
> /BIC/AZCO_O5000 TABLE PSAPODSD 1,515,536
> . . . .
> . . . .
> . . . .
> /BIC/B00003650000000000002 TABLE PART PSAPODSD 16
> /BIC/B00003640000000000002 TABLE PART PSAPODSD 16
> /BIC/B00003630000000000002 TABLE PART PSAPODSD 16
> Total 65,071,488
>
> Suggestions on how to tackle this? I thought of trying to pull the small ones out but since the developers told me that the names
> chnage (/BIC/blah blah) from dataload to dataload that moving them to a seperate tablespace wouldn't probably be worth it since it
> would not be predictable.
>
> I'm open for suggestions!
> Thanks!
> -----------------------------------------------------
> Come race with us!
> http://www.mgpmrc.org

In addition to the other advice provided, you may also want to review SAP Note Number 214995, "Oracle locally managed tablespaces in the SAP environment".

HTH,
Andy. Received on Mon May 29 2006 - 00:12:59 CDT