VALIDATE statement

Syntax

Validate a database
```
VALIDATE { CHECKSUM | DATABASE }
```

Validate a table or materialized view

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

Validate an index

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

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

Validate a text index

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

Parameters

CHECKSUM clause
Use this clause to validate the checksum on each page of a database. The database must have check sums enabled. The CHECKSUM clause 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. CHECKSUM reads each database page directly from disk (not from 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, then an error occurs and information about the invalid page appears in the database server messages window.
DATABASE clause
The VALIDATE DATABASE statement ensures that the free map correctly identifies pages as either allocated or free and that no BLOBs are 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. 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, then the validation does not run until the database cleaner is finished running.
[INDEX] PRIMARY KEY | FOREIGN KEY
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. If the specified index is not a foreign key index, then WITH EXPRESS CHECK has no effect.
MATERIALIZED VIEW
Validates the specified materialized view.
TABLE
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).

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.
TEXT INDEX
The VALIDATE TEXT INDEX statement verifies that the positional information for the terms in the index is intact. If the positional information is not intact, then an error is generated and you must rebuild the text index. If the text index is either auto or manual, then you can rebuild the text index by executing the REFRESH TEXT INDEX statement.

If the generated error concerns an immediate text index, then drop the immediate index and create a new one.
WITH EXPRESS CHECK
Specifying the WITH EXPRESS CHECK clause disables referential integrity checking and can therefore significantly improve performance.
WITH DATA LOCK | WITH SNAPSHOT
When validating tables that have active transactions, choose one of the following options to prevent receiving false errors about corrupt tables:
- WITH DATA LOCK
  Prevents transactions from modifying the table schema or data by applying exclusive data locks on the specified tables. Concurrent transactions can read, but not modify the table data or schema.
  
  When the FOREIGN KEY clause is specified, then exclusive data locks are also applied to the primary key tables.
- WITH SNAPSHOT
  Ensures that only committed data is checked by applying snapshot isolation. Transactions can read and modify the data. This clause requires that the database have snapshot isolation enabled (with the allow_snapshot_isolation database option). Because this clause uses snapshot isolation, performance is often affected.

Remarks

Caution If WITH DATA LOCK or WITH SNAPSHOT is not specified, then perform validation while no connections are making changes to the database; otherwise, false errors may be reported indicating some form of database corruption.

Privileges

You must have the VALIDATE ANY OBJECT system privilege.

Side effects

When you specify the WITH DATA LOCK clause, then exclusive data locks are applied to the specified tables and views.

When the FOREIGN KEY clause is specified along with the WITH DATA LOCK clause, then exclusive data locks are also applied to the primary key tables.

Automatic commit for the WITH DATA LOCK and WITH SNAPSHOT clauses.

Standards

ANSI/ISO SQL Standard
Not in the standard.

VALIDATE statement

Example