Oracle Tips and Tricks — David Fitzjarrell

March 19, 2009

Hanging With The Locals

Filed under: General — dfitzjarrell @ 14:48

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)

Let’s look at some information about tablespaces created as locally managed:

------------------------------ -------------- ----------- ---------- --------- ----------
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:

------------------------------ -------------- ----------- ---------- --------- ----------
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.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at

%d bloggers like this: