BR0970W Database administration alert - level: WARNING,

[Former Member]

Hi,

getting warning in checks.

BR0970W Database administration alert - level: WARNING, type: CRITICAL_SEGMENT, object: (table) SAPR3.COIX_DATA40, value: 6553

60 KB * 1 / PSAPBTABD (> 507904/507904/507904/507904/507904 KB)

Does that mean next extend cannot be found but free space is 3 GB.

Thanks,

Anu

[Former Member · Accepted solution]

Hello Stephan,

You are right.

I have not migrated System tablespace to locally managed tablespace. It is still dictionary managed.

Could you please tell me the right way to do it( note number).

Thanks

Anu.

[stefan_koehler · Accepted solution]

Hello Anu,

> I have not migrated System tablespace to locally managed tablespace. It is still dictionary managed.

No, it has nothing to do with the SYSTEM tablespace. You can run the PSAPBTABD as a LMT and the SYSTEM as a DMT, but you are not "allowed" to migrate the tablespaces with the PL/SQL prodecures of dbms_space_admin in a SAP environment.

Just use the BR*Tools to convert your PSAPBTABD tablespace in a supported way. Check sapnote #214995 for the supported procedure.

Regards

Stefan

[Former Member]

Thanks a lot Stephan,

that means only solution is to recreate new tablespaces and importing data into them.

I ll update u, if I can find anything else.

Anu.

[Former Member]

Also let me know if u can convert the tablespace to LMT in 10g if not converted before.

Thanks,

Anu

[stefan_koehler]

Hello Anu,

> But i want to know if it is not the proper way then what is the exact way of doing it.

You can also create a different tablespace (as LMT) by hand and move the database objects to that with a reorg.

Or you can do an export / import of the data with R3load.

But please do NOT convert the SYSTEM tablespace, before all other tablespace are migrated, because of the following restrictons:

• No dictionary-managed tablespace in the database can be READ WRITE.

• You cannot create new dictionary managed tablespaces

• You cannot convert any dictionary managed tablespaces to local

Thus, it is best only to convert the SYSTEM tablespace to LMT after all other tablespaces are migrated to LMT.

So just use the BR*Tools... they are leading you.

> Also let me know if u can convert the tablespace to LMT in 10g if not converted before.

Yes, this is possible.

Regards

Stefan

[Former Member]

Thanks a lot Stephan,

It is of great help.

But by going throgh these notes I came to know that it can be converted by reorganisation or by brspace.

And system tablespace by "brspace -f dbcreate".

But I havn't got the steps to be executed for it.

As u said i was aware that it is not recommended.

But i want to know if it is not the proper way then what is the exact way of doing it.

Thanks,

Anu.

[Former Member]

Hello,

Thanks for ur quick response

I have checked the tablespace is locally managed.

I have migrated it by executing

exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');

I have one doubt, if tablespace is locally managed then y it is not getting free extends as free space is 3GB.

Anu.

[stefan_koehler]

Hello Anu,

>I have migrated it by executing

>exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');

Ok that is explainig the strange behavior... this is NOT supported by SAP, because of you are not using the complete LMT features. You are facing this situation now with the extent management. Normally in a LMT tablespace smaller extent size are used to fulfill the requests, but if you migrate your tablespace you still have the allocation type USER. You can check this with my query from above.

Please check sapnote #706625 for the restriction:

> 5. The migration from a dictionary-managed SYSTEM tablespace to a locally-managed tablespace using the PL/SQL procedure DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL is not supported in the SAP environment

Now you can extend your tablespace or defrag it with an reorg. I strongly recommend to migrate it in a supported way (export/import).

Regards

Stefan

[former_member1351727]

Hi,

For an object there are two things:

One is maximum number of extents allocated.(there is a limit i think it is 300 ..this limit is no longer there withh later versions of oracle > 8 however SAP recommends to have it )

Secondly next extent size .

If we have next extent size say 1 GB but you do not have that much continuous space in the Table space then we will get error.

And also if we exuausted the allocated number of maximum extents 300 then even we have the next extent asked size taht is 1 GB we can not extend as it reached the maximum limit of taht object.

So only SAP recommends to have a limit for the number extents for an objects if not the object will grow big and big.

Later we need to manage the next extent size also depending on the way the object grows.

If it is big we may not get that much continous space if it is small we may reach the limit for the maximum number of extents allocated for the object.

If your case:

Check the next extent size ..if that much space is available in the table space then probably it is the problem with the Maximum number of extents alloacted for that object.May be you need to increase that.

More in this link:

http://help.sap.com/saphelp_nw70/helpdata/en/29/581633f72011d2952900a0c930df15/frameset.htm

Experts please corrects me if I am wrong in my understanding ....

Anu and also if it not relevent kindly ignore...

Thanks in Advance.

[Former Member]

Also could you please tel me how u figured out 655360 KB. exact size.

Anu.

[stefan_koehler]

Hello Anu,

> Also could you please tel me how u figured out 655360 KB. exact size.

This information stands in your beginning post:

> Value: 655360 KB * 1 / PSAPBTABD (> 507904/507904/507904/507904/507904 KB)

> tablespace is locally managed.

Are you sure? Have you converted your tablespace to LMT?

Please verify this:

>SQL> SELECT ALLOCATION_TYPE, EXTENT_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'PSAPBTABD';

Regards

Stefan

[Former Member]

thanks,

But this means that although it has 3GB free, it is not getting enough contiguous memmory.

Can we defragment it?

tablespace is locally managed.

Thanks,

Anu

[Former Member]

For the next extend of this table you need contiguously 655360 KB in your files.

You have only 507904 / 507904 / 507904 / 507904 / 507904 KB in your datafiles free. Not enough to fit the 655360 Kb.

So in total you have enough space, but not contiguously .

You have 2 options to solve it. Add more space, or decrease the next extent value of that table.

Kind regards

Boudewijn