VALIDATE statement

Validates the current database, or a single table, materialized view, or index in the current database.

Syntax 1 - Validating a database

VALIDATE { CHECKSUM | DATABASE }

Syntax 2 - Validating tables and materialized views

VALIDATE { 
TABLE [ owner.]table-name
| MATERIALIZED VIEW [ owner.]materialized-view-name }
[ WITH EXPRESS CHECK ]

Syntax 3 - Validating indexes

 VALIDATE {
INDEX index-name 
| [ INDEX ] FOREIGN KEY role-name 
| [ INDEX ] PRIMARY KEY  }
ON [ owner.]object-name
}

object-name : 
table-name 
| materialized-view-name

Syntax 4 - Validating text indexes

VALIDATE TEXT INDEX index-name
ON [ owner.]table-name

Use the VALIDATE CHECKSUM statement to validate the checksum on each page of a database. The VALIDATE CHECKSUM statement ensures that database pages have not been modified on disk. When a database is created with checksums enabled, a checksum is calculated for each database page before it is written to disk. VALIDATE CHECKSUM reads each database page directly from disk—not via the database server's cache—and calculates the checksum for each page. If the calculated checksum for a page does not match the stored checksum for that page, an error occurs and information about the invalid page appears in the database server messages window. The VALIDATE CHECKSUM statement is not recommended for databases that have checksums disabled because it reads the entire database from disk.

Use the VALIDATE DATABASE statement to ensure that the free map correctly identifies pages as either allocated or free and that no BLOBs have been orphaned. VALIDATE DATABASE also performs checksum validation and verifies that each database page belongs to the correct object. For example, on a table page, the table ID must identify a valid table whose definition must include the current page in its set of table pages. The VALIDATE DATABASE statement brings pages into the database server's cache in sequential order. This results in their validation, as the database server always verifies the contents and checksums of pages brought into the cache. If you start database validation while the database cleaner is running, the validation does not run until the database cleaner is finished running.

The VALIDATE TABLE statement validates the specified table and all of its indexes by checking that the set of all rows and values in the base table matches the set of rows and values contained in each index. VALIDATE TABLE also traverses all the table's BLOBs, verifies BLOB allocation maps, and detects orphaned BLOBs. The VALIDATE TABLE statement checks the physical structure of the table's index pages and verifies the order of the index hash values, and the index's uniqueness requirements (if any are specified). For foreign key indexes, unless the WITH EXPRESS CHECK clause is specified, each value is looked up in the primary key table to verify that referential integrity is intact. Because the VALIDATE TABLE statement, like VALIDATE DATABASE, uses the database server's cache, the database server also verifies the checksums and basic validity of all pages in use by a table and its indexes.

The VALIDATE INDEX statement performs the same operations as the VALIDATE TABLE statement except that it only validates the specified index and its underlying table; other indexes are not checked. For foreign key indexes, unless the WITH EXPRESS CHECK clause is specified, each value is looked up in the primary key table to verify that referential integrity is intact. Specifying the WITH EXPRESS CHECK clause disables referential integrity checking and can therefore significantly improve performance. If the specified index is not a foreign key index, WITH EXPRESS CHECK has no effect.

Use the VALIDATE TEXT INDEX statement to verify that the positional information for the terms in the index is intact. If the positional information is not intact, an error is generated and you must rebuild the text index. If the text index is either auto or manual, you can rebuild the text index by executing the REFRESH TEXT INDEX statement.

If the generated error concerns an immediate text index, you must drop the immediate index and create a new one.

Caution

Validating a table or an entire database should be performed while no connections are making changes to the database; otherwise, errors may be reported indicating some form of database corruption even though no corruption actually exists.