How to Validate Data in Oracle

What does ANALYZE … VALIDATE STRUCTURE do?

The VALIDATE STRUCTURE option of the ANALYZE statement inspects the physical and logical integrity of a table, index, or cluster. Oracle scans every block, looks for corrupt blocks, orphaned rows, and chained rows, and records findings in DBA_ANALYZE_OBJECT and related views.

When should I run VALIDATE STRUCTURE?

Use it after hardware failures, before backups, or when you suspect data corruption. Because it performs a full scan, schedule it during low-traffic windows to avoid I/O spikes.

How do I validate a single table?

Issue ANALYZE TABLE Orders VALIDATE STRUCTURE;. Oracle reports problems in DBA_ANALYZE_OBJECT and DBA_INVALID_ROWS (if the latter exists).

Example

ANALYZE TABLE Orders VALIDATE STRUCTURE;
SELECT obj#, type#, blocks_corrupt
FROM DBA_ANALYZE_OBJECT
WHERE owner = 'ECOMMERCE' AND name = 'ORDERS';


How do I validate a table and its indexes?

Add the CASCADE keyword to include all indexes: ANALYZE TABLE Orders VALIDATE STRUCTURE CASCADE;. This guarantees both table blocks and index leaves are clean.

How do I capture invalid rows?

Create an INVALID_ROWS table beforehand, then add INTO INVALID_ROWS. Oracle inserts the ROWID of every bad row so you can review or delete them.

CREATE TABLE INVALID_ROWS (rowid UROWID);
ANALYZE TABLE Orders VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;


What privileges are required?

You need ANALYZE ANY or ownership of the object. DBAs typically run the command, but developers can validate their own tables in dev environments.

Best practices for VALIDATE STRUCTURE

Run in quiet periods, enable parallelism with PARALLEL hint if disks allow, and always capture results in INVALID_ROWS to speed up triage.

Common mistakes and fixes

Skipping CASCADE leaves index corruption undetected; always use CASCADE for production tables. Omitting INVALID_ROWS forces manual log mining later; pre-create the table to collect bad rows automatically.

Key takeaways

VALIDATE STRUCTURE is Oracle’s built-in health check. Use it proactively, capture invalid rows, and watch for heavy I/O during large table scans.