How to ANALYZE partitioned tables with VALIDATE STRUCTURE

If you got the error:

ORA-14508: specified VALIDATE INTO table not found

This may be because you are trying to analyze a partitioned table and INVALID_ROWS table is
not found in the current schema.

To validate structure (checks objects for logical corruption) for partitioned tables you
first need to ensure that you’ve got INVALID_ROWS table in your database, if you don’t you
can create it:

cd ${ORACLE_HOME}/rdbms/admin
sqlplus / @utlvalid.sql

This will create the table in OPS$ORACLE schema (if you have that user of course, if you
don't create it in different schema)

You can then run validate structure for a table i.e.

SQL> analyze table scott.orders validate structure into ops$oracle.invalid_rows;

To generate an analyze script for partitioned tables in SYS schema (may be required for
Oracle upgrade) use the script below:

$ sqlplus '/as sysdba'
set verify off
set space 0
set heading off
set feedback off       
set pages 1000
set linesize 150   
spool analyze_part.sql       
select 'analyze table ' || table_name || ' validate structure into ops$oracle.invalid_rows;'
from dba_tables 
where owner = 'SYS' 
and partitioned = 'YES';
spool off