Diagnosing and Repairing Locally Managed Tablespace Problems

Oracle Database includes the DBMS_SPACE_ADMIN package, which is a collection of aids for diagnosing and repairing problems in locally managed tablespaces.

DBMS_SPACE_ADMIN Package Procedures

The following table lists the DBMS_SPACE_ADMIN package procedures. See Oracle Database PL/SQL Packages and Types Reference for details on each procedure.

Procedure Description
ASSM_SEGMENT_VERIFY Verifies the integrity of segments created in tablespaces that have automatic segment space management enabled. Outputs a dump file named sid_ora_process_id.trc to the location that corresponds to the Diag Trace entry in the V$DIAG_INFO view.

Use SEGMENT_VERIFY for tablespaces with manual segment space management.

ASSM_TABLESPACE_VERIFY Verifies the integrity of tablespaces that have automatic segment space management enabled. Outputs a dump file named sid_ora_process_id.trc to the location that corresponds to the Diag Trace entry in the V$DIAG_INFO view.

Use TABLESPACE_VERIFY for tablespaces with manual segment space management.

SEGMENT_CORRUPT Marks the segment corrupt or valid so that appropriate error recovery can be done
SEGMENT_DROP_CORRUPT Drops a segment currently marked corrupt (without reclaiming space)
SEGMENT_DUMP Dumps the segment header and bitmap blocks of a specific segment to a dump file named sid_ora_process_id.trc in the location that corresponds to the Diag Trace entry in the V$DIAG_INFO view. Provides an option to select a slightly abbreviated dump, which includes segment header and includes bitmap block summaries, without percent-free states of each block.
SEGMENT_VERIFY Verifies the consistency of the extent map of the segment
TABLESPACE_FIX_BITMAPS Marks the appropriate DBA range (extent) as free or used in bitmap
TABLESPACE_FIX_SEGMENT_STATES Fixes the state of the segments in a tablespace in which migration was stopped
TABLESPACE_MIGRATE_FROM_LOCAL Migrates a locally managed tablespace to dictionary-managed tablespace
TABLESPACE_MIGRATE_TO_LOCAL Migrates a dictionary-managed tablespace to a locally managed tablespace
TABLESPACE_REBUILD_BITMAPS Rebuilds the appropriate bitmaps
TABLESPACE_REBUILD_QUOTAS Rebuilds quotas for a specific tablespace
TABLESPACE_RELOCATE_BITMAPS Relocates the bitmaps to the specified destination
TABLESPACE_VERIFY Verifies that the bitmaps and extent maps for the segments in the tablespace are synchronized

The following scenarios describe typical situations in which you can use the DBMS_SPACE_ADMIN package to diagnose and resolve problems.

Note:

Some of these procedures can result in lost and unrecoverable data if not used properly. You should work with Oracle Support Services if you have doubts about these procedures.

Scenario 1: Fixing Bitmap When Allocated Blocks are Marked Free (No Overlap)

The TABLESPACE_VERIFY procedure discovers that a segment has allocated blocks that are marked free in the bitmap, but no overlap between segments is reported.

In this scenario, perform the following tasks:

  1. Call the SEGMENT_DUMP procedure to dump the ranges that the administrator allocated to the segment.

  2. For each range, call the TABLESPACE_FIX_BITMAPS procedure with the TABLESPACE_EXTENT_MAKE_USED option to mark the space as used.

  3. Call TABLESPACE_REBUILD_QUOTAS to rebuild quotas.

Scenario 2: Dropping a Corrupted Segment

You cannot drop a segment because the bitmap has segment blocks marked "free". The system has automatically marked the segment corrupted.

In this scenario, perform the following tasks:

  1. Call the SEGMENT_VERIFY procedure with the SEGMENT_VERIFY_EXTENTS_GLOBAL option. If no overlaps are reported, then proceed with steps 2 through 5.

  2. Call the SEGMENT_DUMP procedure to dump the DBA ranges allocated to the segment.

  3. For each range, call TABLESPACE_FIX_BITMAPS with the TABLESPACE_EXTENT_MAKE_FREE option to mark the space as free.

  4. Call SEGMENT_DROP_CORRUPT to drop the SEG$ entry.

  5. Call TABLESPACE_REBUILD_QUOTAS to rebuild quotas.

Scenario 3: Fixing Bitmap Where Overlap is Reported

The TABLESPACE_VERIFY procedure reports some overlapping. Some of the real data must be sacrificed based on previous internal errors.

After choosing the object to be sacrificed, in this case say, table t1, perform the following tasks:

  1. Make a list of all objects that t1 overlaps.

  2. Drop table t1. If necessary, follow up by calling the SEGMENT_DROP_CORRUPT procedure.

  3. Call the SEGMENT_VERIFY procedure on all objects that t1 overlapped. If necessary, call the TABLESPACE_FIX_BITMAPS procedure to mark appropriate bitmap blocks as used.

  4. Rerun the TABLESPACE_VERIFY procedure to verify that the problem is resolved.

Scenario 4: Correcting Media Corruption of Bitmap Blocks

A set of bitmap blocks has media corruption.

In this scenario, perform the following tasks:

  1. Call the TABLESPACE_REBUILD_BITMAPS procedure, either on all bitmap blocks, or on a single block if only one is corrupt.

  2. Call the TABLESPACE_REBUILD_QUOTAS procedure to rebuild quotas.

  3. Call the TABLESPACE_VERIFY procedure to verify that the bitmaps are consistent.

Scenario 5: Migrating from a Dictionary-Managed to a Locally Managed Tablespace

Use the TABLESPACE_MIGRATE_TO_LOCAL procedure to migrate a dictionary-managed tablespace to a locally managed tablespace. This operation is done online, but space management operations are blocked until the migration has been completed. This means that you can read or modify data while the migration is in progress, but if you are loading a large amount of data that requires the allocation of additional extents, then the operation may be blocked.

Assume that the database block size is 2K and the existing extent sizes in tablespace tbs_1 are 10, 50, and 10,000 blocks (used, used, and free). The MINIMUM EXTENT value is 20K (10 blocks). Allow the system to choose the bitmap allocation unit. The value of 10 blocks is chosen, because it is the highest common denominator and does not exceed MINIMUM EXTENT.

The statement to convert tbs_1 to a locally managed tablespace is as follows:

EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('tbs_1');

If you choose to specify an allocation unit size, it must be a factor of the unit size calculated by the system.