ORA-14508:specified VALIDATE INTO table not found

对分区表ANALYZE进行结构校验的时候需要创建表INVALID_ROWS(默认情况下into指向该表),或是创建跟INVALID_ROWS结构相同的表(结构不同会报ORA-14509错误),同时需要通过into具体指定该表.

SQL> create table tb_hxl_normal
  2 (
  3 id number not null
  4 );
Table created.
SQL> insert into tb_hxl_normal values(1);
1 row created.
SQL> insert into tb_hxl_normal values(2);
1 row created.
SQL> commit;
Commit complete.

-- 普通表分析

SQL> analyze table tb_hxl_normal validate structure cascade;
Table analyzed.

-- 创建分区表

SQL> create table tb_hxl_hash
  2 (
  3 id number not null
  4 )
  5 partition by hash(id)
  6 (
  7 partition p1,
  8 partition p2
  9 );
Table created.
SQL> insert into tb_hxl_hash values(1);
1 row created.
SQL> insert into tb_hxl_hash values(2);
1 row created.
SQL> commit;
Commit complete.

-- 分区表分析

SQL> analyze table tb_hxl_hash validate structure cascade;
analyze table tb_hxl_hash validate structure cascade
*
ERROR at line 1:
ORA-14508: specified VALIDATE INTO table not found

 

-- 需要创建INVALID_ROWS表

SQL> @D:\app\Administrator\product\11.1.0\db_1\RDBMS\ADMIN\utlvalid.sql
Table created.
SQL> analyze table tb_hxl_hash validate structure cascade;
Table analyzed.

-- 创建结构与invalid_rows相同的表my_invalid_rows

 

SQL> create table my_invalid_rows
  2 as
  3 select * from invalid_rows where 1=2;
Table created.
SQL> drop table invalid_rows;
Table dropped.

 

-- 删除表invalid_rows后会报错

SQL> analyze table tb_hxl_hash validate structure cascade;
analyze table tb_hxl_hash validate structure cascade
*
ERROR at line 1:
ORA-14508: specified VALIDATE INTO table not found

 

-- 通过into指定跟INVALID_ROWS同结构的表

SQL> analyze table tb_hxl_hash validate structure cascade into my_invalid_rows;
Table analyzed.

 

-- Into指定的表跟invalid_rows表结构不相同,这个时候会报ORA-14509: specified VALIDATE INTO table form incorrect错误.

SQL> alter table my_invalid_rows drop column analyze_timestamp;
Table altered.
SQL> analyze table tb_hxl_hash validate structure cascade into my_invalid_rows;
analyze table tb_hxl_hash validate structure cascade into my_invalid_rows
*
ERROR at line 1:
ORA-14509: specified VALIDATE INTO table form incorrect