The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

Just a follow-up from this question on DBVALID:

If my understanding of John's clarifications in the cited question is correct, then DBVALID with no arguments does a VALIDATE DATABASE and a VALIDATE TABLE for each table/materialized view and a VALIDATE INDEX for each index.

Currently, the SA 12 docs state that ...

by default, dbvalid validates all the tables, materialized views, and indexes, in the database, and validates the database itself.

For sa_validate(), the SA 12 docs (as corrected in DCX) specify, that....

if neither owner nor tbl_name are specified, all tables and materialized views in the database are validated. Also, the database itself is validated, including checksum validation, and validation that the number of rows in the each table or materialized view matches the number of rows in each associated index.

So, is my conclusion correct that both DBVALID with no arguments and sa_validate() with no arguments perform the same set of checks?

If so, that might be another reason the clarify the docs:)

asked 15 Oct '10, 08:40

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 15 Oct '10, 09:31

Breck%20Carter's gravatar image

Breck Carter


@Breck: Thanks for the correction (cf. - oh well, I'd nearly written sa_failidate()...

(15 Oct '10, 09:53) Volker Barth

Yes, a default invocation of dbvalid appears to be equivalent to calling sa_validate() with no userid & no table name. For the record, I didn't say that dbvalid executes VALIDATE INDEX for each index. It executes VALIDATE TABLE for each table which also validates all indexes on the table.

The documentation also says that VALIDATE INDEX is a "complement" to the checking done by VALIDATE TABLE but it looks like that is not the case. VALIDATE TABLE actually does slightly more index checking than VALIDATE INDEX does.

permanent link

answered 15 Oct '10, 19:14

John%20Smirnios's gravatar image

John Smirnios
accept rate: 40%


"Shall we break for lunch or open another can of worms?" - anonymous

(15 Oct '10, 21:04) Breck Carter

I feel it's primarily the fact that some checks seem to be done from different starting points with slightly different results (e.g. a table check checking the indizes, too, vs. an index check) that make all these validation issues that complicated. In the end, I just want to validate as much as possible - and do that with confidence:) - That being said, a BIG THANKS to you, John, for keeping on with exploring and explaining!

(16 Oct '10, 17:35) Volker Barth

I'm guessing here but I think some forms of VALIDATE were added so that a subset of the (very expensive) full validation could be performed. From where I sit, I would have expected "VALIDATE DATABASE" to validate everything it could. At one point, I'm pretty sure VALIDATE INDEX actually did do extra work just as I'm sure that there are historical reasons that made sense at the time for how things were implemented at that time. Largely, I think it is the documentation that has fallen behind.

(17 Oct '10, 14:48) John Smirnios

@Breck. Mmmmm. Worms! Open away :)

(17 Oct '10, 14:49) John Smirnios
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 15 Oct '10, 08:40

question was seen: 1,109 times

last updated: 15 Oct '10, 19:14