Converting your tablespaces


Updates at the bottom

Most people will now have already seen the benefits of using locally managed tablespaces as a simple cure for fragmentation and relieving stress on the data dictionary extent tables UET$ and FET$. However, many people are yet to take advantage of this new feature because up until now, the only way to convert to locally managed tablespaces was to unload and reload your tablespace data.

Not any more...

8.1.6 now gives two built-in functions to allow you to switch back and forth between dictionary and locally managed tablespaces without unloading/reloading the data. It was 8.1.5 that introduced the dbms_space_admin package which is used 'behind the scenes' to probe the tablespace bitmaps in locally managed tablespace, mainly for use in the everyday views DBA_EXTENTS and DBA_SEGMENTS. In 8.1.6, two new procedures have been added to the package:

A few of things are important to note:

Finally, the documentation also provides a "tempter", that local management of the SYSTEM tablespace will also be supported in future..

Update

When converting from a dictionary managed tablespace to a locally managed one, then produce a bitmap that all extents can be mapped by, clearly each bit in the bitmap must represent a size that will divide evenly into:

and Oracle also ensures that if you have set the MINIMUM EXTENT clause, then this is respected as well. The documentation says that during conversion, it will try to find the largest suitable extent size, that is, the greatest commmon denominator of all of the used/free extents.

However, it would appear that one very critical factor is not documented. If a MINIMUM EXTENT clause has NOT been specified, then rather than this clause being ignored, Oracle assumes it to be the smallest possible size of an extent (whether it exists or not). In an 8k block size database, this is 40k (Oracle rounds extents bar the initial one up to 5 blocks), so no matter what distribution of extents you have, the bitmap will always be chosen as 1bit per 40k, unless you specify the MINIMUM EXTENT clause.

Example: SQL> create tablespace blah 2 datafile 'G:\ORA9I\ORADATA\DB9\BLAH.DBF' size 10m reuse 3 extent management dictionary; Tablespace created. SQL> drop table t1; drop table t1 * ERROR at line 1: ORA-00942: table or view does not exist SQL> drop table t2; drop table t2 * ERROR at line 1: ORA-00942: table or view does not exist SQL> drop table t3; drop table t3 * ERROR at line 1: ORA-00942: table or view does not exist SQL> col bytes format 999,999,999 SQL> select * from dba_free_space where tablespace_name = 'BLAH'; TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ---------------- ---------- ---------- ------------ ---------- ------------ BLAH 8 2 10,477,568 1279 8 SQL> create table t1 ( x number ) storage ( initial 400k) tablespace blah; Table created. SQL> create table t2 ( x number ) storage ( initial 800k) tablespace blah; Table created. SQL> create table t3 ( x number ) storage ( initial 1200k) tablespace blah; Table created. SQL> select * from dba_free_space where tablespace_name = 'BLAH'; TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ---------------- ---------- ---------- ------------ ---------- ------------ BLAH 8 302 8,019,968 979 8 SQL> select bytes from dba_extents where tablespace_name = 'BLAH'; BYTES ------------ 409,600 819,200 1,228,800 -- -- So at this stage, one would imagine that the unit size for a conversion -- to a locally managed tablespace is 50 ( 50 * 8192 = 400k ). But if we try... -- SQL> exec dbms_space_admin.TABLESPACE_MIGRATE_TO_LOCAL('BLAH',50); BEGIN dbms_space_admin.TABLESPACE_MIGRATE_TO_LOCAL('BLAH',50); END; * ERROR at line 1: ORA-03241: Invalid unit size ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0 ORA-06512: at line 1 -- -- But if we set the minimum extent... -- SQL> alter tablespace blah minimum extent 400k; Tablespace altered. SQL> exec dbms_space_admin.TABLESPACE_MIGRATE_TO_LOCAL('BLAH',50); PL/SQL procedure successfully completed. SQL>