Oracle tablespace migrate from dictionary to local managed Oracle Tips by Burleson Consulting

Question:  I inherited an old database with the Oracle default of dictionary managed (system managed) tablespaces.  How do I change the tablespace definition to make it a locally managed tablespace?  I tried "alter tablespace xxx extent management local" but I got an error.

Answer: When the tablespaces were initially created, the tablespace had dictionary managed metadata "system managed tablespace", such that the data dictionary stores the tablespace metadata, meaning that Oracle must go to the dictionary everytime a tablespace needs to grow. 

The "extent management local" tablespace option removes the data dictionary as a point of contention because the tablespace metadata (i.e. the extent map) is now residing within the tablespace itself.  Local extent management has become the default in later releases of Oracle.

Below is the "alter tablespace" syntax.  Note that many of the initial parameters are "immutable", and cannot be changed with an "alter tablespace" statement:

ALTER TABLESPACE tablespace
  { datafile_tempfile_clauses
  | DEFAULT [ data_segment_compression ] storage_clause
  | MINIMUM EXTENT integer [ K | M ]
  | ONLINE
  | OFFLINE [ NORMAL | TEMPORARY | IMMEDIATE ]
  | { BEGIN | END } BACKUP
  | READ { ONLY | WRITE }
  | PERMANENT| TEMPORARY
  | COALESCE
  | logging_clause
  | [ NO ] FORCE LOGGING
  } ;

To migrate a tablespace from dictionary to local extent management, you must run the tablespace_migrate_to_local procedure in the dbms_space_admin package.  First, MAKE sure to take a full backup of the tablespace and all related tablespaces (inter-tablespace referential integrity) before running this procedure:

execute
   dbms_space_admin.tablespace_migrate_to_local (
   'my_tablespace_name'
   );