47
DBMS_SPACE_ADMIN

The DBMS_SPACE_ADMIN package provides functionality for locally managed tablespaces.

Security

This package runs with SYS privileges; therefore, any user who has privilege to execute the package can manipulate the bitmaps.

Constants

SEGMENT_VERIFY_EXTENTS	Verifies that the space owned by segment is appropriately reflected in the bitmap as used.
SEGMENT_VERIFY_EXTENTS_ GLOBAL	Verifies that the space owned by segment is appropriately reflected in the bitmap as used and that no other segment claims any of this space to be used by it.
SEGMENT_MARK_CORRUPT	Marks a temp segment as corrupt whereby facilitating its elimination from the dictionary (without space reclaim).
SEGMENT_MARK_VALID	Marks a corrupt temp segment as valid. It is useful when the corruption in the segment extent map or elsewhere has been resolved and the segment can be dropped normally.
SEGMENT_DUMP_EXTENT_MAP	Dumps the extent map for a given segment.
TABLESPACE_VERIFY_ BITMAP	Verifies the bitmap of the tablespace with extent maps of the segments in that tablespace to make sure everything is consistent.
TABLESPACE_EXTENT_MAKE_ FREE	Makes this range (extent) of space free in the bitmaps.
TABLESPACE_EXTENT_MAKE_ USED	Makes this range (extent) of space used in the bitmaps.

Summary of Subprograms

Table 47-1 DBMS_SPACE_ADMIN Package Subprograms

SEGMENT_VERIFY procedure

SEGMENT_CORRUPT procedure

SEGMENT_DROP_CORRUPT 
procedure

SEGMENT_DUMP procedure

TABLESPACE_VERIFY procedure

TABLESPACE_FIX_BITMAPS 
procedure

TABLESPACE_REBUILD_BITMAPS 
procedure

TABLESPACE_REBUILD_QUOTAS 
procedure

TABLESPACE_MIGRATE_FROM_LOCAL 
procedure

SEGMENT_VERIFY procedure

This procedure verifies that the extent map of the segment is consistent with the bitmap.

Syntax

DBMS_SPACE_ADMIN.SEGMENT_VERIFY (
   tablespace_name         IN    VARCHAR2,
   header_relative_file    IN    POSITIVE,
   header_block            IN    POSITIVE,
   verify_option           IN    POSITIVE  DEFAULT SEGMENT_VERIFY_EXTENTS);

Parameters

Table 47-2 SEGMENT_VERIFY Procedure Parameters

tablespace_name

header_relative_file

header_block

verify_option

Usage Notes

Anomalies are output as dba-range, bitmap-block, bitmap-block-range, anomaly-information, in the trace file for all dba-ranges found to have incorrect space representation. The kinds of problems which would be reported are free space not considered free, used space considered free, and the same space considered used by multiple segments.

Example

The following example verifies that the segment with segment header at relative file number 4, block number 33, has its extent maps and bitmaps in sync.

SQLPLUS > EXECUTE DBMS_SPACE_ADMIN.SEGMENT_VERIFY('USERS', 4, 33, 1); 

SEGMENT_CORRUPT procedure

This procedure marks the segment corrupt or valid so that appropriate error recovery can be done.

Syntax

DBMS_SPACE_ADMIN.SEGMENT_CORRUPT (
   tablespace_name         IN    VARCHAR2,
   header_relative_file    IN    POSITIVE,
   header_block            IN    POSITIVE,
   corrupt_option          IN    POSITIVE  DEFAULT SEGMENT_MARK_CORRUPT);

Parameters

Table 47-3 SEGMENT_CORRUPT Procedure Parameters

tablespace_name

header_relative_file

header_block

corrupt_option

Example

The following example marks the segment as corrupt:

SQLPLUS > EXECUTE DBMS_SPACE_ADMIN.SEGMENT_CORRUPT('USERS', 4, 33, 3); 

Alternately, the next example marks a corrupt segment valid:

SQLPLUS > EXECUTE DBMS_SPACE_ADMIN.SEGMENT_CORRUPT('USERS', 4, 33, 4); 

SEGMENT_DROP_CORRUPT procedure

This procedure drops a segment currently marked corrupt (without reclaiming space). For this to work, the segment should have been marked temporary. To mark a corrupt segment as temporary, issue a DROP command on the segment.

The space for the segment is not released, and it must be fixed by using the TABLESPACE_FIX_BITMAPS or TABLESPACE_REBUILD_BITMAPS procedure. These are described later in this chapter.

Syntax

DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT (
   tablespace_name         IN    VARCHAR2,
   header_relative_file    IN    POSITIVE,
   header_block            IN    POSITIVE);

Parameters

Table 47-4 SEGMENT_DROP_CORRUPT Procedure Parameters

tablespace_name

header_relative_file

header_block

Example

SQLPLUS > EXECUTE DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT('USERS', 4, 33); 

SEGMENT_DUMP procedure

This procedure dumps the segment header and extent map blocks of the given segment.

Syntax

DBMS_SPACE_ADMIN.SEGMENT_DUMP (
   tablespace_name         IN    VARCHAR2,
   header_relative_file    IN    POSITIVE,
   header_block            IN    POSITIVE,
   dump_option             IN    POSITIVE  DEFAULT SEGMENT_DUMP_EXTENT_MAP);

Parameters

Table 47-5 SEGMENT_DUMP Procedure Parameters

tablespace_name

header_relative_file

header_block

dump_option

Example

SQLPLUS > EXECUTE DBMS_SPACE_ADMIN.SEGMENT_DUMP('USERS', 4, 33); 

TABLESPACE_VERIFY procedure

This procedure verifies that the bitmaps and extent maps for the segments in the tablespace are in sync.

Syntax

DBMS_SPACE_ADMIN.TABLESPACE_VERIFY (
   tablespace_name         IN    VARCHAR2,
   verify_option           IN    POSITIVE DEFAULT TABLESPACE_VERIFY_BITMAP);

Parameters

Table 47-6 TABLESPACE_VERIFY Procedure Parameters

tablespace_name

verify_option

Example

SQLPLUS > EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_VERIFY('USERS'); 

TABLESPACE_FIX_BITMAPS procedure

This procedure marks the appropriate DBA range (extent) as free or used in bitmap.

Syntax

DBMS_SPACE_ADMIN.TABLESPACE_FIX_BITMAPS (
   tablespace_name         IN    VARCHAR2,
   dbarange_relative_file  IN    POSITIVE,
   dbarange_begin_block    IN    POSITIVE,
   dbarange_end_block      IN    POSITIVE,
   fix_option              IN    POSITIVE);

Parameters

Table 47-7 TABLESPACE_FIX_BITMAPS Procedure Parameters

tablespace_name

dbarange_relative_file

dbarange_begin_block

dbarange_end_block

fix_option

Example

The following example marks bits for 50 blocks for relative file number 4, beginning at block number 33 and ending at 83, as USED in bitmaps.

SQLPLUS > EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_FIX_BITMAPS('USERS', 4, 33, 83, 
7); 

Alternately, specifying an option of 8 marks the bits FREE in bitmaps. The BEGIN and END blocks should be in extent boundary and should be extent multiple. Otherwise, an error is raised.

TABLESPACE_REBUILD_BITMAPS procedure

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

Syntax

DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS (
   tablespace_name         IN    VARCHAR2,
   bitmap_relative_file    IN    POSITIVE   DEFAULT NULL,
   bitmap_block            IN    POSITIVE   DEFAULT NULL);

Parameters

Table 47-8 TABLESPACE_REBUILD_BITMAPS Procedure Parameters

tablespace_name

bitmap_relative_file

bitmap_block

Example

The following example rebuilds bitmaps for all the files in the USERS tablespace.

SQLPLUS > EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('USERS'); 

TABLESPACE_REBUILD_QUOTAS procedure

This procedure rebuilds quotas for the given tablespace.

Syntax

DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_QUOTAS (
   tablespace_name         IN    VARCHAR2);

Parameters

Table 47-9 TABLESPACE_REBUILD_QUOTAS Procedure Parameters

tablespace_name

Example

SQLPLUS > EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_QUOTAS('USERS'); 

TABLESPACE_MIGRATE_FROM_LOCAL procedure

This procedure migrates a locally-managed tablespace to a dictionary-managed tablespace.

Syntax

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL (
   tablespace_name         IN    VARCHAR2);

Parameters

Table 47-10 TABLESPACE_MIGRATE_FROM_LOCAL Procedure Parameters

tablespace_name

Usage Notes

The tablespace must be kept online and read write during migration.

Migration of temporary tablespaces and migration of SYSTEM tablespaces are not supported.

Example

SQLPLUS > EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS'); 

Examples

1. TABLESPACE_VERIFY has discovered that some segment has allocated blocks which are marked free in the bitmap, but no overlap between segments was reported. In this case, the following actions are recommended:
   • Call SEGMENT_EXTENT_MAP_DUMP procedure to dump the DBA ranges allocated to the segment.

   • For each range, call TABLESPACE_FIX_BITMAPS with the TABLESPACE_MAKE_USED option to mark the space as used.

   • Call TABLESPACE_REBUILD_QUOTAS to fix up quotas.

2. Segment could not be dropped because the bitmap has some of the segment blocks marked free. The system has automatically marked it corrupted. In this case, do the following:
   • Call SEGMENT_VERIFY procedure with the SEGMENT_CHECK_ALL options. If no overlaps were reported, then do the following:
     • Call SEGMENT_EXTENT_MAP_DUMP procedure to dump the DBA ranges allocated to the segment.

     • For each range, call TABLESPACE_FIX_BITMAPS with the TABLESPACE_MAKE_FREE option to mark the space as free.

     • Call SEGMENT_DROP_CORRUPT to drop the SEG$ entry.

     • Call TABLESPACE_REBUILD_QUOTAS to fix up quotas

3. TABLESPACE_VERIFY has reported some overlaps. In this case, some of the real data will need to be sacrificed, perhaps, based on the previous internal errors. After the victim object is chosen, table T1, do the following:
   • Make a list of all objects which T1 overlaps.

   • Drop T1 using SQL. If necessary, then follow up by SEGMENT_DROP_CORRUPT.

   • Call SEGMENT_VERIFY on all objects that T1 has overlapped. If necessary, then call TABLESPACE_FIX_BITMAPS to mark appropriate bitmap blocks as used.

   • Rerun TABLESPACE_VERIFY.

4. A set of bitmap blocks is media corrupt. Do the following:
   • Call TABLESPACE_REBUILD_BITMAPS, either on all bitmap blocks or on a single block, if only one block is corrupt.

   • Call TABLESPACE_REBUILD_QUOTAS to rebuild quotas.

   • Call TABLESPACE_VERIFY to check if the bitmaps are consistent.