Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 24, 2003

Understanding Oracle's Locally Managed Tablespaces - Page 3

By Amar Kumar Padhi


This Oracle supplied package is used for managing LMTs. The following key options are available.


The first parameter is the tablespace name and the next is the verify option (this defaults to the constant TABLESPACE_VERIFY_BITMAP). This routine verifies the bitmap at tablespace level with the extent maps of the segments present in the tablespace. This ensures the consistency of the bitmap.

      exec dbms_space_admin.tablespace_verify('GLD');


This procedure rebuilds the appropriate bitmap(s). If no bitmap block DBA is specified, then it rebuilds all bitmaps for the given tablespace.

      exec dbms_space_admin.tablespace_rebuild_bitmaps('ECXX');


This procedure rebuilds quota allocations for the given tablespace.

      exec dbms_space_admin.tablespace_rebuild_quotas('USERS');


To migrate from LMT to DMT. The tablespace should be online and read write during migration.

      exec dbms_space_admin.tablespace_migrate_from_local('USERS');


To move from DMT to LMT. The tablespace should be online and read write during migration. SYSTEM tablespace migration is not supported in 8i releases; this is available in 9i. Migration of temporary tablespace (contents temporary) is not supported; these could be dropped and rebuilt as LMTs.

Tablespaces migrated to locally managed format are USER managed. Thus uniform extent size allocation should be manually achieved. The tables and indexes in such tablespaces will grow according to the storage clause specified.

This procedure takes three parameters: tablespace name, the allocation unit size in bytes (optional) and the relative file number (optional) where the bitmap block should be placed for the tablespace.

The relative file number is not required when only one datafile exists in a tablespace. For multiple datafiles, if it is not specified, the system will automatically choose one to place the bitmap into. Only one bitmap header is created for all existing files.

The allocation unit size specified should be a factor of the unit size calculated by the system. By default, the system calculates the allocation unit size based on the highest common divisor of all extents for the concerned tablespace. This number is further trimmed based on the Minimum Extent of the tablespace. If the specified unit size allocation is not a factor of the unit size calculated by the system, an error message is returned. Preferably, allow the system to compute this value for you.

      exec dbms_space_admin.tablespace_migrate_to_local('ECXX');

Please refer to the examples below for using the DBMS_SPACE_ADMIN package.

Checking space availability in LMTs

The existing DBA_FREE_SPACE is still available for checking available space in LMT AND DMT tablespaces. Specifically, two more views were introduced by Oracle - DBA_LMT_FREE_SPACE and DBA_DMT_FREE_SPACE. These views show the available blocks that should be multiplied with the block size to get the total bytes.

select name, (sum(a.blocks * 8192))/1024/1024  "size MB"
from   dba_lmt_free_space a, v$tablespace b
where  a.tablespace_id = b.ts#
group  by name;

select name, (sum(a.blocks * 8192))/1024/1024  "size MB"
from   dba_dmt_free_space a, v$tablespace b
where  a.tablespace_id = b.ts#
group by name;

Beware of ORA-600 error that may be encountered when using DBA_LMT_FREE_SPACE. For example, the following statement gave me trouble until I found the reason to be an internal problem that would get resolved in higher releases.

SQL> select * from dba_lmt_free_space where tablespace_id = 1000;
select * from dba_lmt_free_space where tablespace_id = 1000
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsitbs_info1], [1000], [], [], [], [], [], []

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM