ORA-14099: all rows in table do not qualify for specified partition
来源:互联网 发布:安徽网络教育报名时间 编辑:程序博客网 时间:2024/06/05 17:54
1. 创建分区表
create table range_part_range(id number, deal_date date, contents varchar2(1000))
partition by range(deal_date)
(
partition p1 values less than (to_date('2015-01-21', 'yyyy-mm-dd')),
partition p2 values less than (to_date('2015-01-22', 'yyyy-mm-dd')),
partition p3 values less than (to_date('2015-01-23', 'yyyy-mm-dd')),
partition p_max values less than (maxvalue)
);
partition by range(deal_date)
(
partition p1 values less than (to_date('2015-01-21', 'yyyy-mm-dd')),
partition p2 values less than (to_date('2015-01-22', 'yyyy-mm-dd')),
partition p3 values less than (to_date('2015-01-23', 'yyyy-mm-dd')),
partition p_max values less than (maxvalue)
);
插入记录
insert into range_part_range values(1, to_date('2015-01-21', 'yyyy-mm-dd'), 'a');
insert into range_part_range values(2, to_date('2015-01-22', 'yyyy-mm-dd'), 'b');
insert into range_part_range values(3, to_date('2015-01-23', 'yyyy-mm-dd'), 'c');
检索记录
select count(*) from range_part_range;
COUNT(*)
----------
3
----------
3
select count(*) from range_part_range partition(p1);
COUNT(*)
----------
0
----------
0
select count(*) from range_part_range partition(p2);
COUNT(*)
----------
1
----------
1
select count(*) from range_part_range partition(p3);
COUNT(*)
----------
1
----------
1
select count(*) from range_part_range partition(p_max);
COUNT(*)
----------
1
----------
1
2.创建普通表,用于分区交换
create table range_tbl (id number, deal_date date, contents varchar2(1000));
插入记录
insert into range_tbl values(1000, to_date('2015-01-25', 'yyyy-mm-dd'), 'j');
3.执行分区交换
alter table range_part_range exchange partition p3 with table range_tbl
*
ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition
*
ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition
注意:此处range_tbl中的数据明显不是在p3分区的范围之内,而是在p_max分区范围内,因此报了错。
4. 解决方案一:换一个正确的分区
alter table range_part_range exchange partition p_max with table range_tbl;
select * from range_tbl;中记录现在是原来p_max的记录,
select * from range_part_range partition(p_max);中记录现在是原来range_tbl的记录。
解决方案二:使用without validation
alter table range_part_range exchange partition p3 with table range_tbl without validation;
select * from range_tbl;中记录现在是原来p3的记录,
select * from range_part_range partition(p3);中记录现在是原来range_tbl的记录。
总结:
1. 对于交换分区,普通表中若有数据,要确保其值在交换的分区范围内,否则会提示错误。
2. 尽量不要用without validation,这样会绕开校验,像上例,p3分区会包含一个不在其分区范围内的数据,有可能扰乱了分区的目的。
0 1
- ORA-14099 : all rows in table do not qualify for specified partition & ORA-14126
- ORA-14099: all rows in table do not qualify for specified partition
- ORA-39921: Default Partition (Table) Tablespace USERS for TP not contained in transportable set
- Get the rows count of all the table in Sybase
- 表分区问题 ORA-02149: Specified partition does not exist
- Getting all selected rows in ADF Table with multiple rows selection enabled
- ORA-14185: incorrect physical attribute specified for this index partition
- ERROR OGG-01163 Bad column length (30) specified for column name in table TEST.demo_src, maximum all
- ORA-14508: specified VALIDATE INTO table not found
- ORA-14508:specified VALIDATE INTO table not found
- [Bug]ArcSDE分区表报:ORA-14301: table-level attributes must be specified before partition-level attribute
- Partition table entries are not in disk order 解决办法
- Partition table entries are not in disk order 解决方法
- Calling sequence for inserting or deleting rows in a table view
- The entitlements specified in your application’s Code Signing Entitlements file do not match those
- The entitlements specified in your application’s Code Signing Entitlements file do not match ...
- The entitlements specified in your application’s Code Signing Entitlements file do not match those s
- “The entitlements specified in your application’s Code Signing Entitlements file do not match those
- 59-通讯录案例
- ubuntu文件/文件夹都带锁标记
- task divide example
- 60-给自定义的tableView的cell创建分割线(代码封装)
- 宏碁施振荣:大陆是国际化的练兵场
- ORA-14099: all rows in table do not qualify for specified partition
- 【哈希表】设计client类,实现插入、删除、addAll(全体增减)、max()等操作
- 61-给系统的tableView的cell创建分割线(通用)
- PHP给title=""赋值变量 PHP title弹出字符串拼接变量
- 用户操作Linux的一般常用查询
- 62-IOS事件类型
- RevitAPI: 如何获取MirrorElement函数镜像之后的结果元素
- LINUX软件包的一般安装方法
- 63-iOS事件产生和传递过程(包括底层)