Check data integrity and validate index in Oracle

Analyze the integrity of a table, index, partition, cluster, or materialized view in Oracle

Validate the structure of table / index/ cluster / materialized view command in Oracle:

ANALYZE TABLE table_name VALIDATE STRUCTURE;

Validate the Structure with Indexes used cascade Option:

ANALYZE TABLE table_name VALIDATE STRUCTURE CASCADE;

Validate the table partition Structure:

analyze table table_name partition partition_name validate structure cascade;

If there is any issue, we can move the invalid rows into invalid table:


ANALYZE TABLE <schemaname>.<tablename> VALIDATE STRUCTURE INTO <schemaname>.INVALID_ROWS;

--for creating the table or resolve the sturcture issue ORA-14509: pecified VALIDATE INTO table form incorrect
$ORACLE_HOME/rdbms/admin/utlvalid.sql

Use FAST option: For validate structure for faster the command but if there is any error then we need to run command without FAST because it will not give detail of the error.

ANALYZE TABLE table_name VALIDATE STRUCTURE CASCADE FAST;

Use Online option: Run validate structure for online.

ANALYZE TABLE table_name VALIDATE STRUCTURE CASCADE ONLINE;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.