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.
`DROP_EMPTY_SEGMENTS`	Drops segments from empty tables or table partitions and dependent objects
`MATERIALIZE_DEFERRED_SEGMENTS`	Materializes segments for tables and table partitions with deferred segment creation and their dependent objects.
`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:

Call the SEGMENT_DUMP procedure to dump the ranges that the administrator allocated to the segment.
For each range, call the TABLESPACE_FIX_BITMAPS procedure with the TABLESPACE_EXTENT_MAKE_USED option to mark the space as used.
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:

Call the SEGMENT_VERIFY procedure with the SEGMENT_VERIFY_EXTENTS_GLOBAL option. If no overlaps are reported, then proceed with steps 2 through 5.
Call the SEGMENT_DUMP procedure to dump the DBA ranges allocated to the segment.
For each range, call TABLESPACE_FIX_BITMAPS with the TABLESPACE_EXTENT_MAKE_FREE option to mark the space as free.
Call SEGMENT_DROP_CORRUPT to drop the SEG$ entry.
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:

Make a list of all objects that t1 overlaps.
Drop table t1. If necessary, follow up by calling the SEGMENT_DROP_CORRUPT procedure.
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.
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:

Call the TABLESPACE_REBUILD_BITMAPS procedure, either on all bitmap blocks, or on a single block if only one is corrupt.
Call the TABLESPACE_REBUILD_QUOTAS procedure to rebuild quotas.
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. Therefore, 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.