There are still databases in existence using dictionary-managed tablespaces, which means that the SYSTEM tablespaces in these databases are also dictionary-managed (which presents other issues besides the extent management, such as using the SYSTEM tablespace as a temporary tablespace and having a TEMP tablespace utilizing permanent files). Converting these tablespaces ‘in-place’ is possible with a packaged procedure provided by Oracle, but this method doesn’t work very well in terms of making the dictionary-managed tablespace a true locally managed one. Let’s look at the results of a conversion and see where this method falls short of the mark.
DBA_TABLESPACES contains information on the extent management, extent sizing, block_size, status and other various aspects of all of the tablespaces in a given database. The current description is:
SQL> desc dba_tablespaces
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
SQL>
Let’s look at some information about tablespaces created as locally managed:
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATIO MIN_EXTLEN
------------------------------ -------------- ----------- ---------- --------- ----------
UNDOTBS1 65536 LOCAL SYSTEM 65536
SYSAUX 65536 LOCAL SYSTEM 65536
Notice that the ALLOCATION_TYPE is listed as SYSTEM, meaning these are created AUTOALLOCATE, with extents starting at 64K and systematically increasing in size. Notice also that the next extent is NULL, and that the minimum extent length is 64 K. Let’s now look at tablespaces which were dictionary-managed that were converted with the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure:
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATIO MIN_EXTLEN
------------------------------ -------------- ----------- ---------- --------- ----------
TOOLS 32768 32768 LOCAL USER 32768
USERS 131072 131072 LOCAL USER 131072
Here we see a different picture: the ALLOCATION_TYPE is still set to USER, the INITIAL_EXTENT and NEXT_EXTENT fields are populated, not necessarily with matching values and (although not shown here) the PCT_INCREASE values are retained (a true locally managed tablespace has a PCTINCREASE of NULL). Such omissions can create problems later, as free space in these ‘mongrel’ tablespaces may not coalesce, even by brute force methods and next extent sizing can grow since the PCTINCREASE is not ignored. The initial and next extents can also provide problems as they may not be multiples of 64K (as are the extent sizes of a locally managed, autoallocate tablespace) or they may not be uniform in size (due to the pctincrease). Remember, too, that any objects in these tablespaces won’t be rebuilt with the ‘standard’ extent sizes provided by true locally managed tablespaces, and as the free space coalescing may not be reliable in a converted tablespace free space fragmentation can be a serious issue. In true locally managed tablespaces free space fragmentation isn’t a problem because either the extents are all multiples of 64K or all of the extents are uniformly sized. In either case the freed extents can be reused by any new object which needs them. Not so with a converted tablespace, as extent sizes can vary and can be, well, ‘interesting’ sizes so that fragmentation can be an issue, especially when the coalesce functionality fails.
So, how best to convert dictionary-managed tablespaces to locally managed? The most reliable method is to create a new tablespace, locally managed, and move the objects from the old dictionary-managed tablespace to the new, locally managed one and then drop the old dictionary-managed tablespace. Of course you can’t do this if it’s the SYSTEM tablespace; you’re then stuck using the DBMS_SPACE_ADMIN package to migrate this tablespace, and all other dictionary-managed tablespace, in place (if that’s possible as there are restrictions for using DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL). No dictionary-managed tablespaces can exist in read/write mode in a database where the SYSTEM tablespace is locally managed, so any and all dictionary-managed tablespaces other than SYSTEM need to be migrated BEFORE the SYSTEM tablespace is migrated. The only other way to do this is to create a new database with a locally managed SYSTEM tablespace and, using a full export, relocate the objects from the source database.
So hanging with the locals is possible, but taking shortcuts usually isn’t the best way to get the job done because you may find yourself in worse shape than if you did nothing at all.
Oracle Tips and Tricks -- David Fitzjarrell 