登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

飞哥的技术博客

世上无难事,只怕有心人!

 
 
 

日志

 
 
 
 

分区表与普通表数据交换:exchange partition  

2009-06-09 23:45:13|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

lifewise's blog

exchange partition
发表人:lifewise | 发表时间: 2007年十月31日, 16:12

分区操作相关测试

[oracle@test oracle]$ cybercafe

SQL*Plus: Release 9.2.0.5.0 - Production on Wed Oct 31 15:03:37 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL> create table partition_test as select * from reseller_log where 1=2;

Table created.

Elapsed: 00:00:00.12
SQL> alter table reseller_log exchange partition RL_200300 with table partition_test;
alter table reseller_log exchange partition RL_200300 with table partition_test
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION


Elapsed: 00:00:00.02
SQL> desc reseller_log
Name Null? Type
----------------------------------------------------------------- -------- ---------------------------------------------
RESELLER_LOG_ID NOT NULL NUMBER(11)
RESELLER_ID NOT NULL NUMBER(11)
TRANSACTION_TYPE CHAR(2)
TRANSACTION_AMOUNT NUMBER(11,2)
SALES_AMOUNT NUMBER(11,2)
REST_OF_MONEY NOT NULL NUMBER(11,2)
RESELLER_USER_ID NUMBER(11)
LOG_TIME DATE
REMARK VARCHAR2(4000)
PAYMENT_LOG_ID NUMBER(11)
PROVIDER NUMBER(11)
CANCEL_LOG_ID NUMBER(11)
PRODUCT_TYPE CHAR(1)
GOLD_POINT NUMBER(11,2)
GREEN_POINT NUMBER(11,2)

SQL> desc partition_test
Name Null? Type
----------------------------------------------------------------- -------- ---------------------------------------------
RESELLER_LOG_ID NUMBER(11)
RESELLER_ID NOT NULL NUMBER(11)
TRANSACTION_TYPE CHAR(2)
TRANSACTION_AMOUNT NUMBER(11,2)
SALES_AMOUNT NUMBER(11,2)
REST_OF_MONEY NOT NULL NUMBER(11,2)
RESELLER_USER_ID NUMBER(11)
LOG_TIME DATE
REMARK VARCHAR2(4000)
PAYMENT_LOG_ID NUMBER(11)
PROVIDER NUMBER(11)
CANCEL_LOG_ID NUMBER(11)
PRODUCT_TYPE CHAR(1)
GOLD_POINT NUMBER(11,2)
GREEN_POINT NUMBER(11,2)


表没有主键约束,增加一个
SQL> alter table partition_test add constraint sys_test primary key (reseller_log_id);

Table altered.

Elapsed: 00:00:00.15
SQL> alter table reseller_log exchange partition RL_200300 with table partition_test;

Table altered.

Elapsed: 00:00:00.05

SQL> select count(*) from partition_test;

COUNT(*)
----------
0

Elapsed: 00:00:00.00
SQL> select count(*) from reseller_log partition(RL_200300);

COUNT(*)
----------
0

Elapsed: 00:00:00.01
SQL> alter table reseller_log exchange partition RL_200300 with table partition_test;
alter table reseller_log exchange partition RL_200300 with table partition_test
*
ERROR at line 1:
ORA-01502: index 'CYBERCAFE.SYS_TEST' or partition of such index is in unusable state


Elapsed: 00:00:00.03
SQL> alter index sys_test rebuild;

Index altered.

Elapsed: 00:00:00.06
SQL> alter table reseller_log exchange partition RL_200300 with table partition_test;

Table altered.

Elapsed: 00:00:00.04
SQL> select count(*) from partition_test;

COUNT(*)
----------
0

Elapsed: 00:00:00.00
SQL> select count(*) from reseller_log partition(RL_200300);

COUNT(*)
----------
0

Elapsed: 00:00:00.00

该分区无数据,重新测试

SQL> alter table reseller_log exchange partition RL_200401 with table partition_test;
alter table reseller_log exchange partition RL_200401 with table partition_test
*
ERROR at line 1:
ORA-01502: index 'CYBERCAFE.SYS_TEST' or partition of such index is in unusable state


Elapsed: 00:00:00.00
每做一次交换需重建index
SQL> alter index sys_test rebuild;

Index altered.

Elapsed: 00:00:00.02
SQL> select count(*) from reseller_log partition(RL_200401);

COUNT(*)
----------
132

Elapsed: 00:00:00.01
SQL> alter table reseller_log exchange partition RL_200401 with table partition_test;

Table altered.

Elapsed: 00:00:00.06
SQL> select count(*) from partition_test;

COUNT(*)
----------
132

Elapsed: 00:00:00.01
SQL>
SQL> select count(*) from reseller_log partition(RL_200401);

COUNT(*)
----------
0

Elapsed: 00:00:00.01
交换成功

SQL> alter table reseller_log exchange partition RL_200401 with table partition_test;
alter table reseller_log exchange partition RL_200401 with table partition_test
*
ERROR at line 1:
ORA-01502: index 'CYBERCAFE.SYS_TEST' or partition of such index is in unusable state


Elapsed: 00:00:00.01

rebuild index

SQL> alter index sys_TEST rebuild;

Index altered.

Elapsed: 00:00:01.83
SQL> alter table reseller_log exchange partition RL_200401 with table partition_test;
alter table reseller_log exchange partition RL_200401 with table partition_test
*
ERROR at line 1:
ORA-14128: FOREIGN KEY constraint mismatch in ALTER TABLE EXCHANGE PARTITION


Elapsed: 00:00:00.08

SQL> select table_name,constraint_name,constraint_type from dba_constraints where table_name like 'RESELLER_LOG';

TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
RESELLER_LOG SYS_C0019255 C
RESELLER_LOG SYS_C0019256 C
RESELLER_LOG SYS_C0019257 P
RESELLER_LOG SYS_C0019258 R

Elapsed: 00:00:00.91
禁用外建
SQL> alter table reseller_log disable constraint sys_c0019258;

Table altered.

Elapsed: 00:00:00.01
SQL> alter table reseller_log exchange partition RL_200401 with table partition_test;

Table altered.

Elapsed: 00:00:00.13
SQL> select count(*) from partition_test;

COUNT(*)
----------
0

Elapsed: 00:00:00.00
SQL> select count(*) from reseller_log partition(RL_200401);

COUNT(*)
----------
132

Elapsed: 00:00:00.00
SQL>

第二次交换成功

split partition

SQL> alter table reseller_log split partition RL_200401 at (to_date('2004-02-01','YYYY-MM-DD'))
2 into (partition RL_200312,partition RL_200401);
alter table reseller_log split partition RL_200401 at (to_date('2004-02-01','YYYY-MM-DD'))
*
ERROR at line 1:
ORA-14080: partition cannot be split along the specified high bound


Elapsed: 00:00:00.01
SQL> alter table reseller_log split partition RL_200401 at (to_date('2004-01-10','YYYY-MM-DD'))
2 into (partition RL_200312,partition RL_200401);

Table altered.

Elapsed: 00:00:00.30


merge partition;

SQL> alter table reseller_log merge partitions RL_200401,RL_200312 into partition RL_200401;
alter table reseller_log merge partitions RL_200401,RL_200312 into partition RL_200401
*
ERROR at line 1:
ORA-14273: lower-bound partition must be specified first


Elapsed: 00:00:00.00
SQL> alter table reseller_log merge partitions RL_200401,RL_200312 into partition RL_2004013;
alter table reseller_log merge partitions RL_200401,RL_200312 into partition RL_2004013
*
ERROR at line 1:
ORA-14273: lower-bound partition must be specified first


Elapsed: 00:00:00.00
SQL> alter table reseller_log merge partitions RL_200312,RL_200401 into partition RL_2004013;

Table altered.

Elapsed: 00:00:00.13
SQL> alter table reseller_log merge partitions RL_2004013,RL_200402 into partition RL_200402;

Table altered.

Elapsed: 00:00:00.19
SQL>

分区交换将整个数据交换到表,或将表里的数据交换到分区,方法一样,只是过程中需注意外建及index相关问题
操作完后注意检查index

发表评论

标题

在此添加评论

称呼

邮箱地址(可选)

个人主页(可选)







引文来源  lifewise's blog
  评论这张
 
阅读(910)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018