extent management 1

[beechill4]

Hi,

i ran some sql to find out whether the tablespace in a database i have been reviewing were dictionary or locally managed:

this is the result:

TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
SYSTEM DICTIONARY
FERMATT_TEMP LOCAL
FERMATT_RBS LOCAL
FERMATT_INDEX LOCAL
FERMATT_DATA LOCAL
FERMATT_TOOLS LOCAL
FERMATT_USERS LOCAL

should the system tablespace be changed to locally managed? if so why?

Thanks

hill

 

[eagertotry]

SYSTEM can be locally managed and should be for the same reasons why other tablespaces should be locally managed - to reduce dictionary contention and avoid fragmentation. Oracle recommends it and I've tons of databases with locally managed SYSTEM tablespace without any problems. Still, I know some Oracle shops that as a rule, don't use locally managed SYSTEM tablespace, but they've not convinced me why.

The only restriction (if you want to call it that) is that if SYSTEM is locally managed, that database cannot contain any dictionary managed tablespace. However, I don't see it as a restriction. This could be a problem if you want to plug in a DICTIONARY tablespace from somewhere (using TTS), but the workaround exists - migrate that tablespace first to LOCAL and then plug it in.

As to your question of changing SYSTEM to LOCAL, while this can be done, it is not same as a tablespace created as LOCAL. Oracle does provide PL/SQL package to migrate from DICTIONARY to LOCAL but that is only a workaround for situations as above. It doesn't inherently change the extent allocation and management of the tablespace. The extent allocation continues to remain user managed with different size extents possible.

HTH

 

[beechill4]

thanks

thats a great explanation

!