There is however a difference between basic verification and deep verification:

• Basic verification involves consistency checks of space metadata, such as integrity among level 1, level 2, level 3 bitmap blocks, consistency of segment extent map and level 1 bitmap ranges.

• Deep verification involves consistency checks between datablocks and space metadata blocks such as whether the datablocks point correctly to the parent level 1 bitmap blocks, and whether the freeness states in the datablocks are consistent with the freeness states of bits in level 1 bitmap blocks corresponding to the datablocks.

Syntax

DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY (
   segment_owner   IN VARCHAR2,
   segment_name    IN VARCHAR2,
   segment_type    IN VARCHAR2,
   partition_name  IN VARCHAR2,
   verify_option   IN POSITIVE  DEFAULT SEGMENT_VERIFY_BASIC,
   attrib          IN POSITIVE  DEFAULT NULL);

Parameters

Usage Notes

• Using this procedure requires SYSDBA privileges.

• You can determine the relative file # and header block # (header_relative_file and header_block parameters) by querying DBA_SEGMENTS.

• This procedure outputs a dump file named sid_ora_process_ID.trc to the location specified in the USER_DUMP_DEST initialization parameter.

Syntax

DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY (
   tablespace_name   IN VARCHAR2,
   ts_option         IN POSITIVE,
   segment_option    IN POSITIVE DEFAULT NULL);

Parameters

Usage Notes

• Using this procedure requires SYSDBA privileges.

• This procedure outputs a dump file named sid_ora_process_ID.trc to the location specified in the USER_DUMP_DEST initialization parameter.

Syntax

DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS (
   schema_name       IN     VARCHAR2   DEFAULT NULL,
   table_name        IN     VARCHAR2   DEFAULT NULL,
   partition_name    IN     VARCHAR2   DEFAULT NULL);

Parameters

Usage Notes

Given a schema name, this procedure scans all tables in the schema. For each table, if the table or any of its fragments are found to be empty, and the table satisfies certain criteria (restrictions being the same as those described in "Restrictions on Deferred Segment Creation"), then the empty table fragment and associated index segments are dropped along with the corresponding LOB data and index segments. A subsequent insert creates segments with the same properties.

Optionally:

• No schema_name is specified, in which case tables belonging to all schemas are scanned

• Both schema_name and table_name are specified to perform the operation on a specified table

• All three arguments are supplied, restricting the operation to the partition and its dependent objects

Syntax

DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS (
   schema_name       IN     VARCHAR2   DEFAULT NULL,
   table_name        IN     VARCHAR2   DEFAULT NULL,
   partition_name    IN     VARCHAR2   DEFAULT NULL);

Parameters

Usage Notes

Given a schema name, this procedure scans all tables in the schema. For each table, if the deferred or delayed segment property is set for the table or any of its fragments, then a new segment is created for those fragments and their dependent objects.

Optionally:

• No schema_name is specified, in which case tables belonging to all schemas are scanned

• Both schema_name and table_name are specified to perform the operation on a specified table

• All three arguments are supplied, restricting the operation to the partition and its dependent objects

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    NUMBER,
   corrupt_option          IN    POSITIVE  DEFAULT SEGMENT_MARK_CORRUPT);

Parameters

Usage Noes

You can determine the relative file number and block number (header_relative_file and header_block parameter) of the segment header block by querying DBA_SEGMENTS.

Examples

The following example marks the segment as corrupt:

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

Alternately, the next example marks a corrupt segment valid:

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

For this to work, the segment must be marked temporary. To mark a corrupt segment as temporary, issue a DROP command on the segment.

Syntax

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

Parameters

Usage Notes

• 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.

• The procedure cannot be used on the SYSTEM tablespace.

• You can determine the relative file number and block number (header_relative_file and header_block parameter) of the segment header block by querying DBA_SEGMENTS.

Examples

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

Syntax

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

Parameters

Usage Notes

• You can produce a slightly abbreviated dump, which includes the segment header and bitmap block summaries, without percent-free states of each block if you pass SEGMENT_DUMP_BITMAP_SUMMARY as the dump_option parameter.

• You can determine the relative file number and block number (header_relative_file and header_block parameter) of the segment header block by querying DBA_SEGMENTS.HEADER_FILE. If HEADER_FILE is greater than 1023 then use DBA_DATA_FILES.RELATIVE_FNO.

Examples

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

Syntax

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

Parameters

Usage Notes

• Anomalies are output as block range, bitmap-block, bitmap-block-range, anomaly-information, in the trace file for all block 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.

• You can determine the relative file number and block number (header_relative_file and header_block parameter) of the segment header block by querying DBA_SEGMENTS.

Examples

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

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

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

Examples

The following example marks bits for 51 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, DBMS_SPACE_ADMIN.EXTENT_MAKE_USED); 

Alternatively, specifying an option of TABLESPACE_EXTENT_MAKE_FREE marks the bits free in bitmaps. The BEGIN and END blocks must be in extent boundary and be extent multiple; otherwise, an error is raised.

During tablespace migration to or from local, the segments are put in a transient state. If migration is aborted, then the segment states are corrected by SMON when event 10906 is set. A database with segments in 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     IN    VARCHAR);

Parameters

Usage Notes

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

Examples

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_STATES('TS1') 

Syntax

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL (
   tablespace_name         IN    VARCHAR2);

Parameter

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.

Examples

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS'); 

Syntax

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL (
   tablespace_name     IN     VARCHAR2,
   unit_size           IN     POSITIVE DEFAULT NULL,
   rfno                IN     POSITIVE DEFAULT NULL);

Parameters

Usage Notes

• Before you migrate the SYSTEM tablespace, migrate any dictionary-managed tablespaces that you want to use in read/write mode to locally managed. After the SYSTEM tablespace is migrated, you cannot change dictionary-managed tablespaces to read/write.

  See Also:

  Oracle Database Administrator's Guide

• 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 EXTENT 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 is ignored for GCD calculation. If you specify the unit size, then it must 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, then an error is issued. The data file specified must be part of the tablespace being migrated. If the dataflow is not specified, then the system chooses a dataflow in which to place the initial bitmap blocks. If space is not found for the initial bitmaps, then an error is raised.

Examples

To migrate a tablespace 'TS1' in 2KB blocksize with minimum extent size 1MB:

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TS1', 512, 2);

The bitmaps are placed in file with relative file number 2.

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

Usage Notes

Only full rebuild is supported.

Examples

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

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('USERS'); 

Syntax

DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_QUOTAS (
   tablespace_name         IN    VARCHAR2);

Parameters

Examples

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_QUOTAS('USERS');

Syntax

DBMS_SPACE_ADMIN.TABLESPACE_RELOCATE_BITMAPS (
   tablespace_name    IN      VARCHAR2,
   filno              IN      POSITIVE,
   blkno              IN      POSITIVE);

Parameters

Usage Notes

• 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 you explicitly resize a file at or below the space header segment, then 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.

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

Examples

EXECUTE  DBMS_SPACE_ADMIN.TABLESPACE_RELOCATE_BITMAPS('TS1', 3, 4);

Moves the bitmaps to file 3, block 4.

Syntax

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

Parameters

Examples

EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_VERIFY('USERS');