ITPub博客

首页 > 数据库 > Oracle > exchange partition

exchange partition

原创 Oracle 作者:lifewise 时间:2007-10-31 16:12:48 0 删除 编辑
分区操作相关测试[@more@]

[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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/620862/viewspace-980168/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-07

  • 博文量
    52
  • 访问量
    488452