Why can't we use DBMS_SPACE_ADMIN package?

[Former Member]

Hello Experts,

Why can't we use DBMS_SPACE_ADMIN package in SAP environment to convert tablespaces from DMT to LMT.It is so easy job, why we have to user transportable tablespace method as per note 748434. Is there any solid reason for not using DBMS_SPACE_ADMIN. What might be the impact if I have already converte the system tablespace too using the DBMS package.

[stefan_koehler]

Hello,

i know that SAP does not support the package method for converting DMTs to LMTs.

I know no reason why.. the only one that i can think of is to avoid customer errors by converting the tablespaces, because of if you forget to convert some non-system tablespaces before converting the system tablespace you have some trouble.

It is described in the package description and in the method itself:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_spadmn.htm#i1002966

Regards

Stefan

[Former Member]

Stefan,

Thanks for your response. I feel its lame reason if SAP thinks people do mistage while converting DMT to LMT on the production system. I had message created with SAP and came back with a one line answer saying "Using dbms_package will not provide all the benefits of LMT, so they are not recommending". But they are not telling what is the impact if we use DBMS_SPACE_ADMIN other than user. I can use dbms package and convert them but if tomrrow something goes wrong, I am scared SAP says they wont support bcoz we bypassed their blessings.

Thanks, please any one let me know if you know of any other reasons.

Kempegowda

[stefan_koehler]

Hello Kempegowda,

> Using dbms_package will not provide all the benefits of LMT, so they are not recommending"

SAP support is correct in this point. If you convert your DMT to LMT you have not all "features".

SAP is pointing to the "allocation type for the extents" as a lost feature .. here is a nice article about that:

http://www.dbasupport.com/oracle/ora9i/managed_tablespace.shtml

=> USER- The LMT behaves as DMT, allocating extents as per the storage clause provided with the object or defaulted at tablespace level. The advantage is that allocation of extents is managed at the datafile level and such tablespaces will not compete for ST enqueue. The disadvantage is that such tablespaces are not subject to uniform extent allocation policy. DMTs that are converted to LMTs fall under this type.

But in my opinion the other features (ST enqueue) are much more important.

Regards

Stefan

[former_member204746]

this package is easy to use but you do not get all the benifits. it also "messes" up the datafile layout as per Oracle gurus.

you can also consider using SAP note 646681 and reorg your tablespaces. This is what I have done. if using Oracle 10g, you can easily reorg 90% of SAP databases using online reorgs. you can even rename a tablespace!

so, reorg online PSAPBTABD to PSAPBTAB2D and PSAPBTAB2I, reorg offline the rest of objects that contains LON RAW field types. swhen finished, drop PSAPBTABD, rename PSAPBTAB2D to PSAPBTABD. there ya go.