SAMEER WADHWA

Locally managed tablespace feature is available from oracle 8.1.5 . By using this you can have better space management, higher reliability and less fragmentation.

Locally Managed Tablespace (LMT) manages its own space by maintaining a bitmap in each of the datafile it belongs to. It keeps track of the free and used blocks or set of blocks in the datafile. Each time when the extent is allocated and reused , bitmap is updated to reflect the new status. Locally managed tablespace (LMT) causes no dictionary updates and so no rollback action is generated. It also keep track of adjacent free block. So Dba’s does not have to worry about coalescing the tablespace to make a contiguous free space.

If you create the system tablespace as locally managed , all of the rollback segment must be created in a locally managed tablespace. The other tablespaces can be dictionary managed tablespaces.

TYPE OF ALLOCATION IN LMT

Ø UNIFORM

Ø AUTOALLOCATE .

Uniform means that all the extent are of uniform size.

 Autoallocate means that all the extent sizes are determined by the system i.e. Oracle determines the

 optimal size of  additional extents, with a minimum extent size of 64 KB.

Examples

Let us see few statement to create a  Locally managed tablespace (LMT)

Statement 1:-

SQL> create tablespace local_uniform_size datafile 'e:\ora816\database\local_tbs01.dbf'  size 10m

extent management local uniform size 10K;

Tablespace created.

Statement 2:-

SQL> create tablespace local_uniform_Default datafile 'e:\ora816\database\local_tbs01.dbf' size 10m

extent management local uniform ;

Tablespace created.

If you do not define the size of uniform extent then the default size would be 1M. All the equal extents of 1M will be created in the database.

Statement 3 :-

SQL> create tablespace local_auto datafile 'e:\ora816\database\local_tbs02.dbf' size 10m

extent management local autoallocate;

Tablespace created.

You can also defined statement 3 as

SQL> create tablespace local_auto datafile 'e:\ora816\database\local_tbs02.dbf' size 10m

extent management local ;

Autoallocate is the default.

SQL> select substr(tablespace_name,1,20) Tablespace,initial_extent/(1024) "I-EX(KB)",next_extent/(1024) "N-EX(KB)",

Min_extlen,extent_management,allocation_type from dba_tablespaces where tablespace_name like 'LOC%';

TABLESPACE I-EX(KB) N-EX(KB) MIN_EXTLEN EXTENT_MAN ALLOCATIO

-------------------- ---------- ---------- ---------- ---------- ---------

LOCAL_AUTO 64 65536 LOCAL SYSTEM

LOCAL_UNIFORM_DEFAULT 1024 1024 1048576 LOCAL UNIFORM

LOCAL_UNIFORM_SIZE 16 16 16384 LOCAL UNIFORM

For tablespace LOCAL_AUTO which is SYSTEM-MANAGED LMT has a default initial extent size of 64KB

For tablespace LOCAL_UNIFORM_DEFAULT which is uniform LMT has a initial and next default size of 1M

For tablespace LOCAL_UNIFORM_SIZE which is uniform size defined LMT has a initial and next size of 16K. if you look the tablespace creation definition , you will found that I define the uniform size of 10K. Since my block size is 8K. Oracle rounds the size 10K to 16K.

Note : Storage parameter Next,Pctincrease,minextents,maxextents and defaults are not valid.

Oracle recommendation :-

The file size defined in the locally managed tablespace should be 64K + <Required size>.

Suppose you want to create 10mb datafile , then recommended size would be 10MB+64K = 10304K. We are adding 64K as LMT need this for the storage management information.

Also the uniform size should be 4 times of your database block size to avoid ORA-3237 error which commonly

 comes when you create a table with  LOB in LMT, or creating rollback segment in LMT .

create tablespace local_uniform_Default datafile 'e:\ora816\database\local_tbs01.dbf' size 10304K

extent management local uniform size 32K ;

Here the file size 1034k is nothing but 10MB+64K and uniform size 32K is 4 times of my block size (8k).

Temporay Tablespace as Locally Managed Tablespace :-

You can also create a temporary tablespace as Locally Managed tablespace as

   CREATE TEMPORARY TABLESPACE Local_Temporary

      TEMPFILE 'e:\ora816\database\locally_temp.dbf' SIZE 10M REUSE

      AUTOEXTEND ON NEXT 10M MAXSIZE 100M

      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 72K;

If we assume a default database block size of 8k, and that each

bit in the map represents one extent (72k), then each bit maps

(72k / 8k =) 9 Oracle blocks

Migration from Dictionary Managed Tablespace to Locally Managed Tablespace

With release 8.1.6 you can migrate dictionary managed tablespace to locally managed tablespace.

  Point to consider before migration :-

Ø set the compatible parameter in the init parameter file to higher version.

Ø should have enough space for the entire bitmap header in the datafile of the migrated tablespaces.

Ø Tablespace cannot be system , offline, bitmapped or Temporary

For more info on migration refer to doc 109627.1 on metalink.

SQL> execute SYS.dbms_space_admin.tablespace_migrate_TO_local('SAMTABLESPACE');

After migration ALLOCATION_TYPE in dba_tablespace is still USERS instead of LOCAL and also tables are still

extending with the next extent specified  , even though they are  locally managed tablespace . So in short there

 would not be any policy benefits from migration. Still it is good to convert dictionary managed tablespace to

 locally managed tablespace because of some performance benefits , efficient extent operation and to avoid

 ST enqueue contention.

You can also migrate from Locally mamaged tablespace to dictionary managed as follows :-

SQL> execute SYS.dbms_space_admin.tablespace_migrate_from_local('SAMTABLESPACE');

Conclusion : As you know till now that how can we use locally managed tablespace in the database, its usefulness , its advantages and how easy this is to maintain.

Your comments , feedback and question regarding locally managed tablespace are most welcome.

Feedback GuestBook

Sameer Wadhwa