Use this statement to validate the current database, or a single table or materialized view in the current database.

Syntax 1 - Validating tables and materialized views

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

Syntax 2 - Validating a database

VALIDATE { CHECKSUM | DATABASE }

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

Parameters

Remarks

Validation of tables includes a checksum validation, and validation that the number of rows in a table matches the number of rows in each index associated with the table. If you specify WITH EXPRESS CHECK, a checksum validation is not performed.

The VALIDATE DATABASE statement validates that all table pages in the database belong to the correct object. VALIDATE DATABASE also performs a checksum validation, but does not validate the indexes, or check data correctness.

Use the VALIDATE CHECKSUM statement to perform a checksum validation on the 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 from disk 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 can also be useful on databases with checksums disabled, since critical database pages still include checksums.

Use the VALIDATE INDEX statement to validate an index, including index statistics, on a table or a materialized view. The VALIDATE INDEX statement ensures that every row referenced in the index actually exists. For foreign key indexes, it also ensures that the corresponding row exists in the primary table. This check complements the validity checking carried out by the VALIDATE TABLE statement. The VALIDATE INDEX statement also verifies that the statistics reported on the specified indexes are accurate. If they are not accurate, an error is generated.

Permissions

Must have DBA or VALIDATE authority.

Side effects

None.

See also

• Validation utility (dbvalid)
• sa_validate system procedure
• Validating databases
• CREATE DATABASE statement
• CREATE INDEX statement

Standards and compatibility