DBMS_SPACE_ADMIN Package

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

TABLESPACE_VERIFY

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');

TABLESPACE_REBUILD_BITMAPS

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');

TABLESPACE_REBUILD_QUOTAS

This procedure rebuilds quota allocations for the given tablespace.

      exec dbms_space_admin.tablespace_rebuild_quotas('USERS');

TABLESPACE_MIGRATE_FROM_LOCAL

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');

TABLESPACE_MIGRATE_TO_LOCAL

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], [], [], [], [], [], []

Previous  |   Next

Prev  [1] [2] [3] [4] Next