DBMS_SPACE_ADMIN, 3 of 3

Summary of DBMS_SPACE_ADMIN Subprograms

Table 68-2 DBMS_SPACE_ADMIN Subprograms

Subprogram	Description
SEGMENT_VERIFY Procedure	Verifies the consistency of the extent map of the segment.
SEGMENT_CORRUPT Procedure	Marks the segment corrupt or valid so that appropriate error recovery can be done.
SEGMENT_DROP_CORRUPT Procedure	Drops a segment currently marked corrupt (without reclaiming space).
SEGMENT_DUMP Procedure	Dumps the segment header and extent maps of a given segment.
TABLESPACE_VERIFY Procedure	Verifies that the bitmaps and extent maps for the segments in the tablespace are in sync.
TABLESPACE_FIX_BITMAPS Procedure	Marks the appropriate DBA range (extent) as free or used in bitmap.
TABLESPACE_REBUILD_BITMAPS Procedure	Rebuilds the appropriate bitmaps.
TABLESPACE_REBUILD_QUOTAS Procedure	Rebuilds quotas for given tablespace.
TABLESPACE_MIGRATE_FROM_LOCAL Procedure	Migrates a locally-managed tablespace to dictionary-managed tablespace.
TABLESPACE_MIGRATE_TO_LOCAL Procedure	Migrates a tablespace from dictionary managed format to locally managed format.
TABLESPACE_RELOCATE_BITMAPS Procedure	Relocates the bitmaps to the destination specified.
TABLESPACE_FIX_SEGMENT_STATES Procedure	Fixes the state of the segments in a tablespace in which migration was aborted.

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 68-3 SEGMENT_VERIFY Procedure Parameters

Parameters	Description
tablespace_name	Name of tablespace in which segment resides.
header_relative_file	Relative file number of segment header.
header_block	Block number of segment header.
verify_option	What kind of check to do: `SEGMENT_VERIFY_EXTENTS` or `SEGMENT_VERIFY_EXTENTS_GLOBAL`.

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.

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

Note:

All DBMS_SPACE_ADMIN package examples use the tablespace USERS which contains SCOTT.EMP.

SEGMENT_CORRUPT Procedure

This procedure marks the segment corrupt or valid so that appropriate error recovery can be done. It cannot be used on the SYSTEM tablespace.

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 68-4 SEGMENT_CORRUPT Procedure Parameters

Parameter	Description
tablespace_name	Name of tablespace in which segment resides.
header_relative_file	Relative file number of segment header.
header_block	Block number of segment header.
corrupt_option	`SEGMENT_MARK_CORRUPT` (default) or `SEGMENT_MARK_VALID`.

Example

The following example marks the segment as corrupt:

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

Alternately, the next example marks a corrupt segment valid:

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 procedure cannot be used on the SYSTEM tablespace.

The space for the segment is not released, and it must be fixed by using the TABLESPACE_FIX_BITMAPS Procedure or the TABLESPACE_REBUILD_BITMAPS Procedure.

Syntax

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

Parameters

Table 68-5 SEGMENT_DROP_CORRUPT Procedure Parameters

Parameter	Description
tablespace_name	Name of tablespace in which segment resides.
header_relative_file	Relative file number of segment header.
header_block	Block number of segment header.

Example

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 68-6 SEGMENT_DUMP Procedure Parameters

Parameter	Description
tablespace_name	Name of tablespace in which segment resides.
header_relative_file	Relative file number of segment header.
header_block	Block number of segment header.
dump_option	`SEGMENT_DUMP_EXTENT_MAP`

Example

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 68-7 TABLESPACE_VERIFY Procedure Parameters

Parameter	Description
tablespace_name	Name of tablespace.
verify_option	`TABLESPACE_VERIFY_BITMAP`

Example

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. It cannot be used on the SYSTEM tablespace.

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 68-8 TABLESPACE_FIX_BITMAPS Procedure Parameters

Parameter	Description
tablespace_name	Name of tablespace.
dbarange_relative_file	Relative file number of DBA range (extent).
dbarange_begin_block	Block number of beginning of extent.
dbarange_end_block	Block number (inclusive) of end of extent.
fix_option	`TABLESPACE_EXTENT_MAKE_FREE` or `TABLESPACE_EXTENT_MAKE_USED`.

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.

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 bitmaps. If no bitmap block DBA is specified, then it rebuilds all bitmaps for the given tablespace.

The procedure cannot be used on the SYSTEM 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 68-9 TABLESPACE_REBUILD_BITMAPS Procedure Parameters

Parameter	Description
tablespace_name	Name of tablespace.
bitmap_relative_file	Relative file number of bitmap block to rebuild.
bitmap_block	Block number of bitmap block to rebuild.

Example

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

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('USERS');

Note:

Only full rebuild is supported.

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 68-10 TABLESPACE_REBUILD_QUOTAS Procedure Parameters

Parameter	Description
tablespace_name	Name of tablespace

Example

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. You cannot use this procedure for SYSTEM tablespace.

Syntax

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL (
   tablespace_name         IN    VARCHAR2);

Parameter

Table 68-11 TABLESPACE_MIGRATE_FROM_LOCAL Procedure Parameter

Parameter	Description
tablespace_name	Name of tablespace

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

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS');

TABLESPACE_MIGRATE_TO_LOCAL Procedure

Use this procedure to migrate the tablespace from a dictionary-managed format to a locally managed format. Tablespaces migrated to locally managed format are user managed.

Caution:

Do not migrate the SYSTEM tablespace without a clear understanding of the conditions that must be met. Refer to "SYSTEM Tablespace Migration: Conditions".

Syntax

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL (
   tablespace_name
   allocation_unit
   relative_fno)

Parameters

Table 68-12 Parameters for TABLESPACE_MIGRATE_TO_LOCAL

Parameter Name	Purpose	Datatype	Parameter Type
tablespace_name	Name of the tablespace to be migrated.	`VARCHAR`	IN
allocation_unit	Unit size (which is the size of the smallest possible chunk of space that can be allocated) in the tablespace.	INTEGER	IN
relative_fno	Relative File Number of the file where the bitmap blocks should be placed (optional)	INTEGER	IN

Usage Notes

The tablespace must be kept online and read/write during migration. Note that temporary tablespaces cannot be migrated.

Allocation Unit may be specified optionally. The default is calculated by the system based on the highest common divisor of all extents (used or free) for the tablespace. This number is further trimmed based on the MINIMUM EXTENT for the tablespace (5 if MINIMUM EXTENTT is not specified). Thus, the calculated value will not be larger than the MINIMUM EXTENT for the tablespace. The last free extent in every file will be ignored for GCD calculation. If you specify the unit size, it has to be a factor of the UNIT size calculated by the system, otherwise an error message is returned.

The Relative File Number parameter is used to place the bitmaps in a desired file. If space is not found in the file, an error is issued. The datafile specified should be part of the tablespace being migrated. If the datafile is not specified then the system will choose a datafile in which to place the initial bitmap blocks. If space is not found for the initial bitmaps, an error will be raised.

Example

To migrate a tablespace 'TS1' with minimum extent size 1m, use

execute dbms_space_admin.tablespace_migrate_to_local('TS1', 512, 2);

The bitmaps will be placed in file with relative file number 2.

TABLESPACE_RELOCATE_BITMAPS Procedure

Use this procedure to relocate the bitmaps to the destination specified. Migration of a tablespace from dictionary managed to locally managed format could result in the creation of SPACE HEADER segment that contains the bitmap blocks. The SPACE HEADER segment is treated as user data. If the user wishes to explicitly resize a file at or below the space header segment, an error is issued. Use the tablespace_relocate_bitmaps command to move the control information to a different destination and then resize the file.

This procedure cannot be used on the SYSTEM tablespace.

Syntax

DBMS_SPACE_ADMIN.TABLESPACE_RELOCATE_BITMAPS (
   tablespace_name
   relative_fno
   block_number )

Parameters

Table 68-13 Parameters for TABLESPACE_RELOCATE_BITMAPS

Parameter Name	Purpose	Datatype	Parameter Type
tablespace_name	Name of Tablespace.	VARCHAR	IN
relative_fno	Relative File Number of the destination file.	NUMBER	IN
block_number	Block Number of the destination dba.	NUMBER	IN

Usage Notes

The tablespace must be kept online and read/write during relocation of bitmaps. Can be done only on migrated locally managed tablespaces.

Example

execute  dbms_space_admin.tablespace_relocate_bitmaps('TS1', 3, 4);

Moves the bitmaps to file 3, block 4.

Note:

The source and the destination addresses should not overlap. The destination block number is rounded down to the unit boundary. If there is user data in that location an error is raised.

TABLESPACE_FIX_SEGMENT_STATES Procedure

Use this procedure to fix the state of the segments in a tablespace in which migration was aborted. During tablespace migration to or from local, the segments are put in a transient state. If migration is aborted, the segment states are corrected by SMON when event 10906 is set. Database with segments is such a transient state cannot be downgraded. The procedure can be used to fix the state of such segments.

Syntax

DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_STATES (
   tablespace_name);

Parameters

Table 68-14 Parameter for TABLESPACE_FIX_SEGMENT_STATES

Parameter Name	Purpose	Datatype	Parameter Type
tablespace_name	Name of the tablespace whose segments need to be fixed.	VARCHAR	IN

Usage Notes

The tablespace must be kept online and read/write when this procedure is called.

Example

execute dbms_space_admin.tablespace_fix_segment_states('TS1');