对分区表ANALYZE进行结构校验的时候需要创建表INVALID_ROWS(默认情况下into指向该表),或是创建跟INVALID_ROWS结构相同的表(结构不同会报ORA-14509错误),同时需要通过into具体指定该表.- SQL>createtable tb_hxl_normal
- 2 (
- 3 id numbernotnull
- 4 );
- Table created.
- SQL>insertinto tb_hxl_normal values(1);
- 1 row created.
- SQL>insertinto tb_hxl_normal values(2);
- 1 row created.
- SQL> commit;
- Commit complete.
-- 普通表分析
- SQL> analyze table tb_hxl_normal validate structure cascade;
- Table analyzed.
-- 创建分区表
- SQL>createtable tb_hxl_hash
- 2 (
- 3 id numbernotnull
- 4 )
- 5 partition by hash(id)
- 6 (
- 7 partition p1,
- 8 partition p2
- 9 );
- Table created.
- SQL>insertinto tb_hxl_hash values(1);
- 1 row created.
- SQL>insertinto 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 VALIDATEINTOtablenot 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>createtable my_invalid_rows
- 2 as
- 3 select*from invalid_rows where 1=2;
- Table created.
- SQL>droptable 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 VALIDATEINTOtablenot found
-- 通过into指定跟INVALID_ROWS同结构的表
- SQL> analyze table tb_hxl_hash validate structure cascadeinto my_invalid_rows;
- Table analyzed.
-- Into指定的表跟invalid_rows表结构不相同,这个时候会报ORA-14509: specified VALIDATE INTO table form incorrect错误.
- SQL>altertable my_invalid_rows dropcolumn analyze_timestamp;
- Table altered.
- SQL> analyze table tb_hxl_hash validate structure cascadeinto my_invalid_rows;
- analyze table tb_hxl_hash validate structure cascadeinto my_invalid_rows
- *
- ERROR at line 1:
- ORA-14509: specified VALIDATEINTOtable form incorrect
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2015-5-8 11:15:44 |
赞(
操作
