Chinaunix首页 | 论坛 | 博客

UL

  • 博客访问: 24033
  • 博文数量: 10
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 0
  • 用 户 组: 普通用户
  • 注册时间: 2016-08-04 14:35
文章分类
文章存档

2015年(10)

我的朋友

分类: Oracle

2015-09-13 11:05:44

对分区表ANALYZE进行结构校验的时候需要创建表INVALID_ROWS(默认情况下into指向该表),或是创建跟INVALID_ROWS结构相同的表(结构不同会报ORA-14509错误),同时需要通过into具体指定该表.
  1. SQL> create table tb_hxl_normal
  2.   2 (
  3.   3 id number not null
  4.   4 );
  5. Table created.
  6. SQL> insert into tb_hxl_normal values(1);
  7. 1 row created.
  8. SQL> insert into tb_hxl_normal values(2);
  9. 1 row created.
  10. SQL> commit;
  11. Commit complete.
-- 普通表分析
  1. SQL> analyze table tb_hxl_normal validate structure cascade;
  2. Table analyzed.
-- 创建分区表
  1. SQL> create table tb_hxl_hash
  2.   2 (
  3.   3 id number not null
  4.   4 )
  5.   5 partition by hash(id)
  6.   6 (
  7.   7 partition p1,
  8.   8 partition p2
  9.   9 );
  10. Table created.
  11. SQL> insert into tb_hxl_hash values(1);
  12. 1 row created.
  13. SQL> insert into tb_hxl_hash values(2);
  14. 1 row created.
  15. SQL> commit;
  16. Commit complete.
-- 分区表分析

  1. SQL> analyze table tb_hxl_hash validate structure cascade;
  2. analyze table tb_hxl_hash validate structure cascade
  3. *
  4. ERROR at line 1:
  5. ORA-14508: specified VALIDATE INTO table not found
 
-- 需要创建INVALID_ROWS表
  1. SQL> @D:\app\Administrator\product\11.1.0\db_1\RDBMS\ADMIN\utlvalid.sql
  2. Table created.
  3. SQL> analyze table tb_hxl_hash validate structure cascade;
  4. Table analyzed.
-- 创建结构与invalid_rows相同的表my_invalid_rows
 
  1. SQL> create table my_invalid_rows
  2.   2 as
  3.   3 select * from invalid_rows where 1=2;
  4. Table created.
  5. SQL> drop table invalid_rows;
  6. Table dropped.
 
-- 删除表invalid_rows后会报错

  1. SQL> analyze table tb_hxl_hash validate structure cascade;
  2. analyze table tb_hxl_hash validate structure cascade
  3. *
  4. ERROR at line 1:
  5. ORA-14508: specified VALIDATE INTO table not found
 
-- 通过into指定跟INVALID_ROWS同结构的表
  1. SQL> analyze table tb_hxl_hash validate structure cascade into my_invalid_rows;
  2. Table analyzed.
 
-- Into指定的表跟invalid_rows表结构不相同,这个时候会报ORA-14509: specified VALIDATE INTO table form incorrect错误.
  1. SQL> alter table my_invalid_rows drop column analyze_timestamp;
  2. Table altered.
  3. SQL> analyze table tb_hxl_hash validate structure cascade into my_invalid_rows;
  4. analyze table tb_hxl_hash validate structure cascade into my_invalid_rows
  5. *
  6. ERROR at line 1:
  7. ORA-14509: specified VALIDATE INTO table form incorrect
 
阅读(1333) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~