SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
---------- --- ----------------- ------------------ ---------
2 csmisc2
cs_dc02
9.2.0.4.0 20-MAY-05 OPEN YES 2 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL
Elapsed: 00:00:00.10
SQL>
一下测试以 SUBSCRIPTION_HISTORY 为例：
===============================================================================
1、建立测试用分区表 Subscription_History
===============================================================================
SQL> l
1 create table SUBSCRIPTION_HISTORY (
2 HistoryId NUMBER(10) DEFAULT 0 NOT NULL,
3 Mid VARCHAR2(14) DEFAULT 'a' NOT NULL,
4 SubsId VARCHAR2(15) DEFAULT 'a' NOT NULL,
5 ActionId VARCHAR2(3) DEFAULT 'a' NOT NULL,
6 ActionDesc VARCHAR2(100) DEFAULT 'a' NOT NULL,
7 ActionDate DATE default sysdate,
8 ActionPlace VARCHAR2(2),
9 ServId VARCHAR2(12) DEFAULT 'a' NOT NULL,
10 ICPid VARCHAR2(10) DEFAULT 'a' NOT NULL,
11 AccessModeId NUMBER(7) DEFAULT 0 NOT NULL,
12 ServCatId NUMBER(4) DEFAULT 0 NOT NULL,
13 ServiceGradeId NUMBER(3) DEFAULT 0 NOT NULL,
14 Staff_id NUMBER(10) ,
15 Comments VARCHAR2(200),
16 ServicePackageId VARCHAR2(8),
17 ChargeMid VARCHAR2(14),
18 TmpFlag NUMBER(1) DEFAULT 0,
19 VisitCount NUMBER(3) DEFAULT 0,
20 SubnPlace VARCHAR2(2),
21 SubsCat NUMBER(3) DEFAULT 99,
22 ChargeSubsid varchar2(15),
23 constraint PK_SUBSCRIPTION_HISTORY primary key (HistoryId) USING INDEX TABLESPACE index_history,
24 constraint FK_SUBSCRIP_REFERENCE_MISC_ACT foreign key (ActionId)
25 references Misc_Action (Actionid)
26 )
27 PARTITION BY RANGE(actiondate)
28 (
29 PARTITION subnhis_P1 VALUES LESS THAN (to_date('2004/02/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN01,
30 PARTITION subnhis_P2 VALUES LESS THAN (to_date('2004/03/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN02,
31 PARTITION subnhis_P3 VALUES LESS THAN (to_date('2004/04/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN03,
32 PARTITION subnhis_P4 VALUES LESS THAN (to_date('2004/05/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN04,
33 PARTITION subnhis_P5 VALUES LESS THAN (to_date('2004/06/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN05,
34 PARTITION subnhis_P6 VALUES LESS THAN (to_date('2004/07/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN06,
35 PARTITION subnhis_P7 VALUES LESS THAN (to_date('2004/08/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN07,
36 PARTITION subnhis_P8 VALUES LESS THAN (to_date('2004/09/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN08,
37 PARTITION subnhis_P9 VALUES LESS THAN (to_date('2004/10/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN01,
38 PARTITION subnhis_P10 VALUES LESS THAN (to_date('2004/11/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN02,
39 PARTITION subnhis_P11 VALUES LESS THAN (to_date('2004/12/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN03,
40 PARTITION subnhis_P12 VALUES LESS THAN (to_date('2005/01/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN04
41* )
SQL> /
Table created.
Elapsed: 00:00:00.29
SQL>
===============================================================================
2、建立分区表 （Subscription_History）的索引
===============================================================================
SQL> l
1 create index IDX_SUBHISTORY_MID on SUBSCRIPTION_HISTORY (MID)
2 local
3 (
4 PARTITION ind_subnhis_mid_P1 TABLESPACE INDEX_SUBN01,
5 PARTITION ind_subnhis_mid_P2 TABLESPACE INDEX_SUBN02,
6 PARTITION ind_subnhis_mid_P3 TABLESPACE INDEX_SUBN03,
7 PARTITION ind_subnhis_mid_P4 TABLESPACE INDEX_SUBN04,
8 PARTITION ind_subnhis_mid_P5 TABLESPACE INDEX_SUBN05,
9 PARTITION ind_subnhis_mid_P6 TABLESPACE INDEX_SUBN06,
10 PARTITION ind_subnhis_mid_P7 TABLESPACE INDEX_SUBN07,
11 PARTITION ind_subnhis_mid_P8 TABLESPACE INDEX_SUBN08,
12 PARTITION ind_subnhis_mid_P9 TABLESPACE INDEX_SUBR01,
13 PARTITION ind_subnhis_mid_P10 TABLESPACE INDEX_SUBR02,
14 PARTITION ind_subnhis_mid_P11 TABLESPACE INDEX_SUBR03,
15 PARTITION ind_subnhis_mid_P12 TABLESPACE INDEX_SUBR04
16* )
SQL> /
Index created.
Elapsed: 00:00:00.27
SQL>
SQL> l
1 create index IDX_SUBHISTORY_ServId on SUBSCRIPTION_HISTORY (ServId)
2 local
3 (
4 PARTITION ind_subnhis_serv_P1 TABLESPACE INDEX_SUBN01,
5 PARTITION ind_subnhis_serv_P2 TABLESPACE INDEX_SUBN02,
6 PARTITION ind_subnhis_serv_P3 TABLESPACE INDEX_SUBN03,
7 PARTITION ind_subnhis_serv_P4 TABLESPACE INDEX_SUBN04,
8 PARTITION ind_subnhis_serv_P5 TABLESPACE INDEX_SUBN05,
9 PARTITION ind_subnhis_serv_P6 TABLESPACE INDEX_SUBN06,
10 PARTITION ind_subnhis_serv_P7 TABLESPACE INDEX_SUBN07,
11 PARTITION ind_subnhis_serv_P8 TABLESPACE INDEX_SUBN08,
12 PARTITION ind_subnhis_serv_P9 TABLESPACE INDEX_SUBR01,
13 PARTITION ind_subnhis_serv_P10 TABLESPACE INDEX_SUBR02,
14 PARTITION ind_subnhis_serv_P11 TABLESPACE INDEX_SUBR03,
15 PARTITION ind_subnhis_serv_P12 TABLESPACE INDEX_SUBR04
16* )
SQL> /
Index created.
Elapsed: 00:00:00.22
SQL>
SQL> l
1 create index IDX_SUBHISTORY_SubsId on SUBSCRIPTION_HISTORY (SubsId)
2 local
3 (
4 PARTITION ind_subnhis_id_P1 TABLESPACE INDEX_SUBN01,
5 PARTITION ind_subnhis_id_P2 TABLESPACE INDEX_SUBN02,
6 PARTITION ind_subnhis_id_P3 TABLESPACE INDEX_SUBN03,
7 PARTITION ind_subnhis_id_P4 TABLESPACE INDEX_SUBN04,
8 PARTITION ind_subnhis_id_P5 TABLESPACE INDEX_SUBN05,
9 PARTITION ind_subnhis_id_P6 TABLESPACE INDEX_SUBN06,
10 PARTITION ind_subnhis_id_P7 TABLESPACE INDEX_SUBN07,
11 PARTITION ind_subnhis_id_P8 TABLESPACE INDEX_SUBN08,
12 PARTITION ind_subnhis_id_P9 TABLESPACE INDEX_SUBR01,
13 PARTITION ind_subnhis_id_P10 TABLESPACE INDEX_SUBR02,
14 PARTITION ind_subnhis_id_P11 TABLESPACE INDEX_SUBR03,
15 PARTITION ind_subnhis_id_P12 TABLESPACE INDEX_SUBR04
16* )
SQL> /
Index created.
Elapsed: 00:00:00.22
SQL>
SQL> select count(*) from SUBSCRIPTION_HISTORY;
COUNT(*)
----------
0
Elapsed: 00:00:00.02
SQL>
===============================================================================
3、插入并检验测试数据
===============================================================================
SQL> ALTER TABLE SUBSCRIPTION_HISTORY DISABLE CONSTRAINT FK_SUBSCRIP_REFERENCE_MISC_ACT CASCADE;
Table altered.
Elapsed: 00:00:00.04
SQL>
SQL> insert /*+ APPEND */ into SUBSCRIPTION_HISTORY(HistoryId,actiondate)
2 select rownum,to_date('31-dec-2004')-mod(rownum+1000,360) from t
3 nologging;
928352 rows created.
Elapsed: 00:02:12.74
SQL> commit;
Commit complete.
Elapsed: 00:00:00.04
SQL>
SQL> update SUBSCRIPTION_HISTORY nologging set Actionid=(select Actionid from Misc_Action where rownum<2);
928352 rows updated.
Elapsed: 00:01:02.22
SQL> commit;
Commit complete.
Elapsed: 00:00:00.04
SQL>
SQL> ALTER TABLE SUBSCRIPTION_HISTORY ENABLE CONSTRAINT FK_SUBSCRIP_REFERENCE_MISC_ACT;
Table altered.
Elapsed: 00:00:06.16
SQL>
==================================================================================================
4、将老的历史表改名为 Subscription_History_bak
==================================================================================================
SQL> rename SUBSCRIPTION_HISTORY to SUBSCRIPTION_HISTORY_bak;
Table renamed.
Elapsed: 00:00:00.11
SQL>
----------------------------------------------------------------
检查rename后的状态：
----------------------------------------------------------------
drop table user_segment_tmp;
create table user_segment_tmp nologging as select * from user_segments;
set linesize 120
BREAK ON SEGMENT_NAME SKIP 1
col TABLESPACE_NAME for a30
col SEGMENT_NAME for a30
col PARTITION_NAME for a30
SELECT /*+ RULE */ SEGMENT_NAME,PARTITION_NAME,BYTES/1024/1024 MB ,TABLESPACE_NAME
FROM user_segment_tmp WHERE SEGMENT_NAME ='SUBSCRIPTION_HISTORY_BAK'
ORDER BY 1,2,3,4
SQL> l
1 SELECT /*+ RULE */ SEGMENT_NAME,PARTITION_NAME,BYTES/1024/1024 MB ,TABLESPACE_NAME
2 FROM user_segment_tmp WHERE SEGMENT_NAME ='SUBSCRIPTION_HISTORY_BAK'
3* ORDER BY 1,2,3,4
SQL> /
SEGMENT_NAME PARTITION_NAME MB TABLESPACE_NAME
------------------------------ ------------------------------ ---------- ------------------------------
SUBSCRIPTION_HISTORY_BAK SUBNHIS_P1 7.90625 DATA_SUBN01
SUBNHIS_P10 8 DATA_SUBN02
SUBNHIS_P11 8 DATA_SUBN03
SUBNHIS_P12 8 DATA_SUBN04
SUBNHIS_P2 8 DATA_SUBN02
SUBNHIS_P3 8 DATA_SUBN03
SUBNHIS_P4 8 DATA_SUBN04
SUBNHIS_P5 8 DATA_SUBN05
SUBNHIS_P6 8 DATA_SUBN06
SUBNHIS_P7 8 DATA_SUBN07
SUBNHIS_P8 8 DATA_SUBN08
SUBNHIS_P9 7.90625 DATA_SUBN01
12 rows selected.
Elapsed: 00:00:00.02
SQL>
break on table_name skip 1
set pages 1000
select table_name,partition_name,tablespace_name,PARTITION_POSITION from user_tab_partitions
where TABLE_NAME ='SUBSCRIPTION_HISTORY_BAK' order by table_name,PARTITION_POSITION
/
SQL> l
1 select table_name,partition_name,tablespace_name,PARTITION_POSITION from user_tab_partitions
2* where TABLE_NAME ='SUBSCRIPTION_HISTORY_BAK' order by table_name,PARTITION_POSITION
SQL> /
TABLE_NAME PARTITION_NAME TABLESPACE_NAME PARTITION_POSITION
------------------------------ ------------------------------ ------------------------------ ------------------
SUBSCRIPTION_HISTORY_BAK SUBNHIS_P1 DATA_SUBN01 1
SUBNHIS_P2 DATA_SUBN02 2
SUBNHIS_P3 DATA_SUBN03 3
SUBNHIS_P4 DATA_SUBN04 4
SUBNHIS_P5 DATA_SUBN05 5
SUBNHIS_P6 DATA_SUBN06 6
SUBNHIS_P7 DATA_SUBN07 7
SUBNHIS_P8 DATA_SUBN08 8
SUBNHIS_P9 DATA_SUBN01 9
SUBNHIS_P10 DATA_SUBN02 10
SUBNHIS_P11 DATA_SUBN03 11
SUBNHIS_P12 DATA_SUBN04 12
12 rows selected.
Elapsed: 00:00:00.17
SQL>
break on TAB.IND skip 1
col table_name format a20
col index_name format a25
col partition_name format a25
col high_value format a100
set linesize 1000
select b.table_name || '.' || a.index_name "TAB.IND", a.partition_name, a.high_value,PARTITION_POSITION
from user_ind_partitions a, user_ind_columns b
where a.index_name=b.index_name
and b.table_name ='SUBSCRIPTION_HISTORY_BAK'
order by 1,PARTITION_POSITION
/
SQL> l
1 select b.table_name || '.' || a.index_name "TAB.IND", a.partition_name, a.high_value,PARTITION_POSITION
2 from user_ind_partitions a, user_ind_columns b
3 where a.index_name=b.index_name
4 and b.table_name ='SUBSCRIPTION_HISTORY_BAK'
5* order by 1,PARTITION_POSITION
SQL> /
TAB.IND PARTITION_NAME HIGH_VALUE PARTITION_POSITION
------------------------------------------------------------- ------------------------- ---------------------------------------------------------------------------------------------------- ------------------
SUBSCRIPTION_HISTORY_BAK.IDX_SUBHISTORY_MID IND_SUBNHIS_MID_P1 TO_DATE(' 2004-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1
IND_SUBNHIS_MID_P2 TO_DATE(' 2004-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
IND_SUBNHIS_MID_P3 TO_DATE(' 2004-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 3
IND_SUBNHIS_MID_P4 TO_DATE(' 2004-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 4
IND_SUBNHIS_MID_P5 TO_DATE(' 2004-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 5
IND_SUBNHIS_MID_P6 TO_DATE(' 2004-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 6
IND_SUBNHIS_MID_P7 TO_DATE(' 2004-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 7
IND_SUBNHIS_MID_P8 TO_DATE(' 2004-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 8
IND_SUBNHIS_MID_P9 TO_DATE(' 2004-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 9
IND_SUBNHIS_MID_P10 TO_DATE(' 2004-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 10
IND_SUBNHIS_MID_P11 TO_DATE(' 2004-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 11
IND_SUBNHIS_MID_P12 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 12
SUBSCRIPTION_HISTORY_BAK.IDX_SUBHISTORY_SERVID IND_SUBNHIS_SERV_P1 TO_DATE(' 2004-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1
IND_SUBNHIS_SERV_P2 TO_DATE(' 2004-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
IND_SUBNHIS_SERV_P3 TO_DATE(' 2004-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 3
IND_SUBNHIS_SERV_P4 TO_DATE(' 2004-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 4
IND_SUBNHIS_SERV_P5 TO_DATE(' 2004-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 5
IND_SUBNHIS_SERV_P6 TO_DATE(' 2004-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 6
IND_SUBNHIS_SERV_P7 TO_DATE(' 2004-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 7
IND_SUBNHIS_SERV_P8 TO_DATE(' 2004-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 8
IND_SUBNHIS_SERV_P9 TO_DATE(' 2004-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 9
IND_SUBNHIS_SERV_P10 TO_DATE(' 2004-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 10
IND_SUBNHIS_SERV_P11 TO_DATE(' 2004-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 11
IND_SUBNHIS_SERV_P12 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 12
SUBSCRIPTION_HISTORY_BAK.IDX_SUBHISTORY_SUBSID IND_SUBNHIS_ID_P1 TO_DATE(' 2004-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1
IND_SUBNHIS_ID_P2 TO_DATE(' 2004-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
IND_SUBNHIS_ID_P3 TO_DATE(' 2004-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 3
IND_SUBNHIS_ID_P4 TO_DATE(' 2004-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 4
IND_SUBNHIS_ID_P5 TO_DATE(' 2004-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 5
IND_SUBNHIS_ID_P6 TO_DATE(' 2004-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 6
IND_SUBNHIS_ID_P7 TO_DATE(' 2004-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 7
IND_SUBNHIS_ID_P8 TO_DATE(' 2004-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 8
IND_SUBNHIS_ID_P9 TO_DATE(' 2004-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 9
IND_SUBNHIS_ID_P10 TO_DATE(' 2004-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 10
IND_SUBNHIS_ID_P11 TO_DATE(' 2004-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 11
IND_SUBNHIS_ID_P12 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 12
36 rows selected.
Elapsed: 00:00:00.30
SQL>
SQL> select count(*) from SUBSCRIPTION_HISTORY_BAK partition(SUBNHIS_P1);
COUNT(*)
----------
64475
Elapsed: 00:00:00.16
SQL> select count(*) from SUBSCRIPTION_HISTORY_BAK partition(SUBNHIS_P10);
COUNT(*)
----------
79949
Elapsed: 00:00:00.08
SQL> select count(*) from SUBSCRIPTION_HISTORY_BAK partition(SUBNHIS_P9);
COUNT(*)
----------
77370
Elapsed: 00:00:00.08
SQL>
==================================================================================================
5、建立新的历史表 SUBSCRIPTION_HISTORY
==================================================================================================
spool createTbl_CS_lunar.log
drop table SUBSCRIPTION_HISTORY;
create table SUBSCRIPTION_HISTORY (
HistoryId NUMBER(10) not null,
Mid VARCHAR2(14) NOT NULL,
SubsId VARCHAR2(15) NOT NULL,
ActionId VARCHAR2(3) not null,
ActionDesc VARCHAR2(100) not null,
ActionDate DATE default sysdate,
ActionPlace VARCHAR2(2),
ServId VARCHAR2(12) NOT NULL,
ICPid VARCHAR2(10) NOT NULL,
AccessModeId NUMBER(7) not null,
ServCatId NUMBER(4) not null,
ServiceGradeId NUMBER(3) NOT NULL,
Staff_id NUMBER(10) ,
Comments VARCHAR2(200),
ServicePackageId VARCHAR2(8),
ChargeMid VARCHAR2(14),
TmpFlag NUMBER(1) DEFAULT 0,
VisitCount NUMBER(3) DEFAULT 0,
SubnPlace VARCHAR2(2),
SubsCat NUMBER(3) DEFAULT 99,
ChargeSubsid varchar2(15),
constraint FK_SUBN_REFERENCE_MISC_ACT foreign key (ActionId) references Misc_Action (Actionid)
)
PARTITION BY RANGE (ACTIONDATE)
(
PARTITION subnhis_P1 VALUES LESS THAN (to_date('2004/02/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN01,
PARTITION subnhis_P2 VALUES LESS THAN (to_date('2004/03/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN02,
PARTITION subnhis_P3 VALUES LESS THAN (to_date('2004/04/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN03,
PARTITION subnhis_P4 VALUES LESS THAN (to_date('2004/05/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN04,
PARTITION subnhis_P5 VALUES LESS THAN (to_date('2004/06/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN05,
PARTITION subnhis_P6 VALUES LESS THAN (to_date('2004/07/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN06,
PARTITION subnhis_P7 VALUES LESS THAN (to_date('2004/08/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN07,
PARTITION subnhis_P8 VALUES LESS THAN (to_date('2004/09/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN08,
PARTITION subnhis_P9 VALUES LESS THAN (to_date('2004/10/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN01,
PARTITION subnhis_P10 VALUES LESS THAN (to_date('2004/11/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN02,
PARTITION subnhis_P11 VALUES LESS THAN (to_date('2004/12/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN03,
PARTITION subnhis_P12 VALUES LESS THAN (to_date('2005/01/01','yyyy/mm/dd')) TABLESPACE DATA_SUBN04
);
create index IDX_SUBNHISTORY_MID_lunar on SUBSCRIPTION_HISTORY (MID)
local
(
PARTITION ind_subnhis_mid_P1 TABLESPACE INDEX_SUBN01,
PARTITION ind_subnhis_mid_P2 TABLESPACE INDEX_SUBN02,
PARTITION ind_subnhis_mid_P3 TABLESPACE INDEX_SUBN03,
PARTITION ind_subnhis_mid_P4 TABLESPACE INDEX_SUBN04,
PARTITION ind_subnhis_mid_P5 TABLESPACE INDEX_SUBN05,
PARTITION ind_subnhis_mid_P6 TABLESPACE INDEX_SUBN06,
PARTITION ind_subnhis_mid_P7 TABLESPACE INDEX_SUBN07,
PARTITION ind_subnhis_mid_P8 TABLESPACE INDEX_SUBN08,
PARTITION ind_subnhis_mid_P9 TABLESPACE INDEX_SUBR01,
PARTITION ind_subnhis_mid_P10 TABLESPACE INDEX_SUBR02,
PARTITION ind_subnhis_mid_P11 TABLESPACE INDEX_SUBR03,
PARTITION ind_subnhis_mid_P12 TABLESPACE INDEX_SUBR04
);
create index IDX_SUBNHISTORY_SERVID_lunar on SUBSCRIPTION_HISTORY (SERVID)
local
(
PARTITION ind_subnhis_serv_P1 TABLESPACE INDEX_SUBN01,
PARTITION ind_subnhis_serv_P2 TABLESPACE INDEX_SUBN02,
PARTITION ind_subnhis_serv_P3 TABLESPACE INDEX_SUBN03,
PARTITION ind_subnhis_serv_P4 TABLESPACE INDEX_SUBN04,
PARTITION ind_subnhis_serv_P5 TABLESPACE INDEX_SUBN05,
PARTITION ind_subnhis_serv_P6 TABLESPACE INDEX_SUBN06,
PARTITION ind_subnhis_serv_P7 TABLESPACE INDEX_SUBN07,
PARTITION ind_subnhis_serv_P8 TABLESPACE INDEX_SUBN08,
PARTITION ind_subnhis_serv_P9 TABLESPACE INDEX_SUBR01,
PARTITION ind_subnhis_serv_P10 TABLESPACE INDEX_SUBR02,
PARTITION ind_subnhis_serv_P11 TABLESPACE INDEX_SUBR03,
PARTITION ind_subnhis_serv_P12 TABLESPACE INDEX_SUBR04
);
create index IDX_SUBNHISTORY_SUBSID_lunar on SUBSCRIPTION_HISTORY (SUBSID)
local
(
PARTITION ind_subnhis_id_P1 TABLESPACE INDEX_SUBN01,
PARTITION ind_subnhis_id_P2 TABLESPACE INDEX_SUBN02,
PARTITION ind_subnhis_id_P3 TABLESPACE INDEX_SUBN03,
PARTITION ind_subnhis_id_P4 TABLESPACE INDEX_SUBN04,
PARTITION ind_subnhis_id_P5 TABLESPACE INDEX_SUBN05,
PARTITION ind_subnhis_id_P6 TABLESPACE INDEX_SUBN06,
PARTITION ind_subnhis_id_P7 TABLESPACE INDEX_SUBN07,
PARTITION ind_subnhis_id_P8 TABLESPACE INDEX_SUBN08,
PARTITION ind_subnhis_id_P9 TABLESPACE INDEX_SUBR01,
PARTITION ind_subnhis_id_P10 TABLESPACE INDEX_SUBR02,
PARTITION ind_subnhis_id_P11 TABLESPACE INDEX_SUBR03,
PARTITION ind_subnhis_id_P12 TABLESPACE INDEX_SUBR04
);
create index IDX_SUBNH_CHARGESUBSID_lunar on SUBSCRIPTION_HISTORY (CHARGESUBSID)
local
(
PARTITION ind_subnhis_CHARGESUBSID_0501 TABLESPACE INDEX_SUBN01,
PARTITION ind_subnhis_CHARGESUBSID_0502 TABLESPACE INDEX_SUBN02,
PARTITION ind_subnhis_CHARGESUBSID_0503 TABLESPACE INDEX_SUBN03,
PARTITION ind_subnhis_CHARGESUBSID_0504 TABLESPACE INDEX_SUBN04,
PARTITION ind_subnhis_CHARGESUBSID_0505 TABLESPACE INDEX_SUBN05,
PARTITION ind_subnhis_CHARGESUBSID_0506 TABLESPACE INDEX_SUBN06,
PARTITION ind_subnhis_CHARGESUBSID_0507 TABLESPACE INDEX_SUBN07,
PARTITION ind_subnhis_CHARGESUBSID_0508 TABLESPACE INDEX_SUBN08,
PARTITION ind_subnhis_CHARGESUBSID_0509 TABLESPACE INDEX_SUBR01,
PARTITION ind_subnhis_CHARGESUBSID_0510 TABLESPACE INDEX_SUBR02,
PARTITION ind_subnhis_CHARGESUBSID_0511 TABLESPACE INDEX_SUBR03,
PARTITION ind_subnhis_CHARGESUBSID_0512 TABLESPACE INDEX_SUBR04
);
create index IDX_SUBNH_HISTORYID_lunar on SUBSCRIPTION_HISTORY (HISTORYID)
local
(
PARTITION ind_subnhis_HISTORYID_0501 TABLESPACE INDEX_SUBN01,
PARTITION ind_subnhis_HISTORYID_0502 TABLESPACE INDEX_SUBN02,
PARTITION ind_subnhis_HISTORYID_0503 TABLESPACE INDEX_SUBN03,
PARTITION ind_subnhis_HISTORYID_0504 TABLESPACE INDEX_SUBN04,
PARTITION ind_subnhis_HISTORYID_0505 TABLESPACE INDEX_SUBN05,
PARTITION ind_subnhis_HISTORYID_0506 TABLESPACE INDEX_SUBN06,
PARTITION ind_subnhis_HISTORYID_0507 TABLESPACE INDEX_SUBN07,
PARTITION ind_subnhis_HISTORYID_0508 TABLESPACE INDEX_SUBN08,
PARTITION ind_subnhis_HISTORYID_0509 TABLESPACE INDEX_SUBR01,
PARTITION ind_subnhis_HISTORYID_0510 TABLESPACE INDEX_SUBR02,
PARTITION ind_subnhis_HISTORYID_0511 TABLESPACE INDEX_SUBR03,
PARTITION ind_subnhis_HISTORYID_0512 TABLESPACE INDEX_SUBR04
);
spool off
exit
SQL> select count(*) from SUBSCRIPTION_HISTORY partition(SUBNHIS_P1);
COUNT(*)
----------
0
Elapsed: 00:00:00.03
SQL> select count(*) from SUBSCRIPTION_HISTORY partition(SUBNHIS_P10);
COUNT(*)
----------
0
Elapsed: 00:00:00.01
SQL> select count(*) from SUBSCRIPTION_HISTORY partition(SUBNHIS_P9);
COUNT(*)
----------
0
Elapsed: 00:00:00.01
SQL> select count(*) from SUBSCRIPTION_HISTORY_BAK partition(SUBNHIS_P1);
COUNT(*)
----------
64475
Elapsed: 00:00:00.05
SQL> select count(*) from SUBSCRIPTION_HISTORY_BAK partition(SUBNHIS_P10);
COUNT(*)
----------
79949
Elapsed: 00:00:00.06
SQL> select count(*) from SUBSCRIPTION_HISTORY_BAK partition(SUBNHIS_P9);
COUNT(*)
----------
77370
Elapsed: 00:00:00.07
SQL>
==================================================================================================
6、 建立一个容纳临时分区数据的空表: SNH_TMP_P1
==================================================================================================
drop table SNH_TMP_P1;
create table SNH_TMP_P1 (
HistoryId NUMBER(10) DEFAULT 0 NOT NULL,
Mid VARCHAR2(14) DEFAULT 'a' NOT NULL,
SubsId VARCHAR2(15) DEFAULT 'a' NOT NULL,
ActionId VARCHAR2(3) DEFAULT 'a' NOT NULL,
ActionDesc VARCHAR2(100) DEFAULT 'a' NOT NULL,
ActionDate DATE default sysdate,
ActionPlace VARCHAR2(2),
ServId VARCHAR2(12) DEFAULT 'a' NOT NULL,
ICPid VARCHAR2(10) DEFAULT 'a' NOT NULL,
AccessModeId NUMBER(7) DEFAULT 0 NOT NULL,
ServCatId NUMBER(4) DEFAULT 0 NOT NULL,
ServiceGradeId NUMBER(3) DEFAULT 0 NOT NULL,
Staff_id NUMBER(10) ,
Comments VARCHAR2(200),
ServicePackageId VARCHAR2(8),
ChargeMid VARCHAR2(14),
TmpFlag NUMBER(1) DEFAULT 0,
VisitCount NUMBER(3) DEFAULT 0,
SubnPlace VARCHAR2(2),
SubsCat NUMBER(3) DEFAULT 99,
ChargeSubsid varchar2(15),
constraint FK_SNH_TMP_lunar foreign key (ActionId) references Misc_Action (Actionid)
)
/
create index IDX_SNH_TMP_MID_lunar on SNH_TMP_P1 (MID);
create index IDX_SNH_TMP_SERVID_lunar on SNH_TMP_P1 (SERVID);
create index IDX_SNH_TMP_SUBSID_lunar on SNH_TMP_P1 (SUBSID);
create index IDX_SNH_TMP_CHARGESUBSID_lunar on SNH_TMP_P1 (CHARGESUBSID);
create index IDX_SNH_TMP_HISTORYID_lunar on SNH_TMP_P1 (HISTORYID);
SQL> select count(*) from SNH_TMP_P1;
COUNT(*)
----------
0
Elapsed: 00:00:00.02
SQL> select count(*) from SUBSCRIPTION_HISTORY partition(SUBNHIS_P1);
COUNT(*)
----------
0
Elapsed: 00:00:00.04
SQL> select count(*) from SUBSCRIPTION_HISTORY_BAK partition(SUBNHIS_P1);
COUNT(*)
----------
64475
Elapsed: 00:00:00.07
SQL>
==================================================================================================
7、将空表和老表的第一分区（例子分区）作交换操作
==================================================================================================
alter table SUBSCRIPTION_HISTORY_BAK
exchange partition SUBNHIS_P1 with table SNH_TMP_P1
including indexes
without validation
/
SQL> alter table SUBSCRIPTION_HISTORY_BAK
2 exchange partition SUBNHIS_P1 with table SNH_TMP_P1
3 including indexes
4 without validation
5 /
exchange partition SUBNHIS_P1 with table SNH_TMP_P1
*
ERROR at line 2:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
Elapsed: 00:00:00.05
SQL>
上诉错误是由于新的表（SNH_TMP_P1，5个索引）比老表（SUBSCRIPTION_HISTORY_BAK，3个local索引和1个global索引）
索引数目不匹配。
删除两个多余的索引后，该问题得到解决：
drop index IDX_SNH_TMP_HISTORYID_lunar;
drop index IDX_SNH_TMP_CHARGESUBSID_lunar;
SQL> drop index IDX_SNH_TMP_HISTORYID_lunar;
Index dropped.
Elapsed: 00:00:00.10
SQL> drop index IDX_SNH_TMP_CHARGESUBSID_lunar;
Index dropped.
Elapsed: 00:00:00.14
SQL>
SQL> alter table SUBSCRIPTION_HISTORY_BAK
2 exchange partition SUBNHIS_P1 with table SNH_TMP_P1
3 including indexes
4 without validation
5 /
Table altered.
Elapsed: 00:00:00.29
SQL>
----------------------------------------------------------------
交换分区后的数据检查：
----------------------------------------------------------------
SQL> select count(*) from SNH_TMP_P1;
COUNT(*)
----------
64475
Elapsed: 00:00:00.09
SQL> select count(*) from SUBSCRIPTION_HISTORY partition(SUBNHIS_P1);
COUNT(*)
----------
0
Elapsed: 00:00:00.02
SQL> select count(*) from SUBSCRIPTION_HISTORY_BAK partition(SUBNHIS_P1);
COUNT(*)
----------
0
Elapsed: 00:00:00.01
SQL>
----------------------------------------------------------------
检查分区表状态：
----------------------------------------------------------------
SQL> l
1 select b.table_name || '.' || a.index_name "TAB.IND", a.partition_name, status,PARTITION_POSITION
2 from user_ind_partitions a, user_ind_columns b
3 where a.index_name=b.index_name
4 and b.table_name IN ('SUBSCRIPTION_HISTORY_BAK','SUBSCRIPTION_HISTORY')
5* order by 1,PARTITION_POSITION
SQL> /
TAB.IND PARTITION_NAME STATUS PARTITION_POSITION
------------------------------------------------------------- ------------------------------ -------- ------------------
SUBSCRIPTION_HISTORY.IDX_SUBNHISTORY_MID_LUNAR IND_SUBNHIS_MID_P1 USABLE 1
IND_SUBNHIS_MID_P2 USABLE 2
IND_SUBNHIS_MID_P3 USABLE 3
IND_SUBNHIS_MID_P4 USABLE 4
IND_SUBNHIS_MID_P5 USABLE 5
IND_SUBNHIS_MID_P6 USABLE 6
IND_SUBNHIS_MID_P7 USABLE 7
IND_SUBNHIS_MID_P8 USABLE 8
IND_SUBNHIS_MID_P9 USABLE 9
IND_SUBNHIS_MID_P10 USABLE 10
IND_SUBNHIS_MID_P11 USABLE 11
IND_SUBNHIS_MID_P12 USABLE 12
SUBSCRIPTION_HISTORY.IDX_SUBNHISTORY_SERVID_LUNAR IND_SUBNHIS_SERV_P1 USABLE 1
IND_SUBNHIS_SERV_P2 USABLE 2
IND_SUBNHIS_SERV_P3 USABLE 3
IND_SUBNHIS_SERV_P4 USABLE 4
IND_SUBNHIS_SERV_P5 USABLE 5
IND_SUBNHIS_SERV_P6 USABLE 6
IND_SUBNHIS_SERV_P7 USABLE 7
IND_SUBNHIS_SERV_P8 USABLE 8
IND_SUBNHIS_SERV_P9 USABLE 9
IND_SUBNHIS_SERV_P10 USABLE 10
IND_SUBNHIS_SERV_P11 USABLE 11
IND_SUBNHIS_SERV_P12 USABLE 12
SUBSCRIPTION_HISTORY.IDX_SUBNHISTORY_SUBSID_LUNAR IND_SUBNHIS_ID_P1 USABLE 1
IND_SUBNHIS_ID_P2 USABLE 2
IND_SUBNHIS_ID_P3 USABLE 3
IND_SUBNHIS_ID_P4 USABLE 4
IND_SUBNHIS_ID_P5 USABLE 5
IND_SUBNHIS_ID_P6 USABLE 6
IND_SUBNHIS_ID_P7 USABLE 7
IND_SUBNHIS_ID_P8 USABLE 8
IND_SUBNHIS_ID_P9 USABLE 9
IND_SUBNHIS_ID_P10 USABLE 10
IND_SUBNHIS_ID_P11 USABLE 11
IND_SUBNHIS_ID_P12 USABLE 12
SUBSCRIPTION_HISTORY.IDX_SUBNH_CHARGESUBSID_LUNAR IND_SUBNHIS_CHARGESUBSID_0501 USABLE 1
IND_SUBNHIS_CHARGESUBSID_0502 USABLE 2
IND_SUBNHIS_CHARGESUBSID_0503 USABLE 3
IND_SUBNHIS_CHARGESUBSID_0504 USABLE 4
IND_SUBNHIS_CHARGESUBSID_0505 USABLE 5
IND_SUBNHIS_CHARGESUBSID_0506 USABLE 6
IND_SUBNHIS_CHARGESUBSID_0507 USABLE 7
IND_SUBNHIS_CHARGESUBSID_0508 USABLE 8
IND_SUBNHIS_CHARGESUBSID_0509 USABLE 9
IND_SUBNHIS_CHARGESUBSID_0510 USABLE 10
IND_SUBNHIS_CHARGESUBSID_0511 USABLE 11
IND_SUBNHIS_CHARGESUBSID_0512 USABLE 12
SUBSCRIPTION_HISTORY.IDX_SUBNH_HISTORYID_LUNAR IND_SUBNHIS_HISTORYID_0501 USABLE 1
IND_SUBNHIS_HISTORYID_0502 USABLE 2
IND_SUBNHIS_HISTORYID_0503 USABLE 3
IND_SUBNHIS_HISTORYID_0504 USABLE 4
IND_SUBNHIS_HISTORYID_0505 USABLE 5
IND_SUBNHIS_HISTORYID_0506 USABLE 6
IND_SUBNHIS_HISTORYID_0507 USABLE 7
IND_SUBNHIS_HISTORYID_0508 USABLE 8
IND_SUBNHIS_HISTORYID_0509 USABLE 9
IND_SUBNHIS_HISTORYID_0510 USABLE 10
IND_SUBNHIS_HISTORYID_0511 USABLE 11
IND_SUBNHIS_HISTORYID_0512 USABLE 12
SUBSCRIPTION_HISTORY_BAK.IDX_SUBHISTORY_MID IND_SUBNHIS_MID_P1 USABLE 1
IND_SUBNHIS_MID_P2 USABLE 2
IND_SUBNHIS_MID_P3 USABLE 3
IND_SUBNHIS_MID_P4 USABLE 4
IND_SUBNHIS_MID_P5 USABLE 5
IND_SUBNHIS_MID_P6 USABLE 6
IND_SUBNHIS_MID_P7 USABLE 7
IND_SUBNHIS_MID_P8 USABLE 8
IND_SUBNHIS_MID_P9 USABLE 9
IND_SUBNHIS_MID_P10 USABLE 10
IND_SUBNHIS_MID_P11 USABLE 11
IND_SUBNHIS_MID_P12 USABLE 12
SUBSCRIPTION_HISTORY_BAK.IDX_SUBHISTORY_SERVID IND_SUBNHIS_SERV_P1 USABLE 1
IND_SUBNHIS_SERV_P2 USABLE 2
IND_SUBNHIS_SERV_P3 USABLE 3
IND_SUBNHIS_SERV_P4 USABLE 4
IND_SUBNHIS_SERV_P5 USABLE 5
IND_SUBNHIS_SERV_P6 USABLE 6
IND_SUBNHIS_SERV_P7 USABLE 7
IND_SUBNHIS_SERV_P8 USABLE 8
IND_SUBNHIS_SERV_P9 USABLE 9
IND_SUBNHIS_SERV_P10 USABLE 10
IND_SUBNHIS_SERV_P11 USABLE 11
IND_SUBNHIS_SERV_P12 USABLE 12
SUBSCRIPTION_HISTORY_BAK.IDX_SUBHISTORY_SUBSID IND_SUBNHIS_ID_P1 USABLE 1
IND_SUBNHIS_ID_P2 USABLE 2
IND_SUBNHIS_ID_P3 USABLE 3
IND_SUBNHIS_ID_P4 USABLE 4
IND_SUBNHIS_ID_P5 USABLE 5
IND_SUBNHIS_ID_P6 USABLE 6
IND_SUBNHIS_ID_P7 USABLE 7
IND_SUBNHIS_ID_P8 USABLE 8
IND_SUBNHIS_ID_P9 USABLE 9
IND_SUBNHIS_ID_P10 USABLE 10
IND_SUBNHIS_ID_P11 USABLE 11
IND_SUBNHIS_ID_P12 USABLE 12
96 rows selected.
Elapsed: 00:00:00.11
SQL>
---------------------------------------------------------
尝试向 SUBSCRIPTION_HISTORY_BAK 中插入结论，看看是否可行？
---------------------------------------------------------
select count(*) from SUBSCRIPTION_HISTORY_BAK partition(SUBNHIS_P1);
select count(*) from SNH_TMP_P1;
CREATE TABLE ttt as select * from SUBSCRIPTION_HISTORY_BAK where rownum<2;
select HistoryId,actiondate from ttt;
update ttt set actiondate=actiondate+5,HistoryId=9999999;
commit;
select HistoryId,actiondate from ttt;
insert into SUBSCRIPTION_HISTORY_BAK select * from ttt;
SQL> select count(*) from SUBSCRIPTION_HISTORY_BAK partition(SUBNHIS_P1);
COUNT(*)
----------
0
Elapsed: 00:00:00.02
SQL> select count(*) from SNH_TMP_P1;
COUNT(*)
----------
64475
Elapsed: 00:00:00.08
SQL> CREATE TABLE ttt as select * from SUBSCRIPTION_HISTORY_BAK where rownum<2;
Table created.
Elapsed: 00:00:00.24
SQL> select HistoryId,actiondate from ttt;
HISTORYID ACTIONDAT
---------- ---------
26 29-FEB-04
Elapsed: 00:00:00.03
SQL> select HistoryId,actiondate from ttt;
HISTORYID ACTIONDAT
---------- ---------
26 29-FEB-04
Elapsed: 00:00:00.03
SQL> update ttt set actiondate=actiondate-35,HistoryId=9999999;
1 row updated.
Elapsed: 00:00:00.02
SQL> select HistoryId,actiondate from ttt;
HISTORYID ACTIONDAT
---------- ---------
9999999 25-JAN-04
Elapsed: 00:00:00.00
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> insert into SUBSCRIPTION_HISTORY_BAK partition(SUBNHIS_P1) select * from ttt;
insert into SUBSCRIPTION_HISTORY_BAK partition(SUBNHIS_P1) select * from ttt
*
ERROR at line 1:
ORA-01502: index 'MISC.PK_SUBSCRIPTION_HISTORY' or partition of such index is in unusable state
Elapsed: 00:00:00.08
SQL>
可见这个时候，由于 SUBSCRIPTION_HISTORY_BAK 表存在global index，因此所了exchange partition后，该表变为不可用的
直到global index被rebuild才可以：
alter index PK_SUBSCRIPTION_HISTORY rebuild online COMPUTE STATISTICS ;
SQL> alter index PK_SUBSCRIPTION_HISTORY rebuild online COMPUTE STATISTICS ;
Index altered.
Elapsed: 00:00:15.71
SQL> select count(*) from SUBSCRIPTION_HISTORY_BAK;
COUNT(*)
----------
863877
Elapsed: 00:00:00.59
SQL>
==================================================================================================
8、测试，是否可以向新表( SUBSCRIPTION_HISTORY )中插入数据
==================================================================================================
insert into SUBSCRIPTION_HISTORY partition(SUBNHIS_P1) select * from ttt;
SQL> select count(*) from SUBSCRIPTION_HISTORY;
COUNT(*)
----------
0
Elapsed: 00:00:00.02
SQL> insert into SUBSCRIPTION_HISTORY partition(SUBNHIS_P1) select * from ttt;
1 row created.
Elapsed: 00:00:00.09
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> select count(*) from SUBSCRIPTION_HISTORY;
COUNT(*)
----------
1
Elapsed: 00:00:00.02
SQL> select count(*) from SUBSCRIPTION_HISTORY partition(SUBNHIS_P1);
COUNT(*)
----------
1
Elapsed: 00:00:00.02
SQL>
==================================================================================================
9、测试，使新表( SUBSCRIPTION_HISTORY )和已经装满数据的单独表（SNH_TMP_P1）做交换
==================================================================================================
SQL> delete from SUBSCRIPTION_HISTORY partition(SUBNHIS_P1);
1 row deleted.
Elapsed: 00:00:00.05
SQL> commit;
Commit complete.
Elapsed: 00:00:00.02
SQL>
alter table SUBSCRIPTION_HISTORY
exchange partition SUBNHIS_P1 with table SNH_TMP_P1
including indexes
without validation
/
SQL> alter table SUBSCRIPTION_HISTORY
2 exchange partition SUBNHIS_P1 with table SNH_TMP_P1
3 including indexes
4 without validation
5 /
exchange partition SUBNHIS_P1 with table SNH_TMP_P1
*
ERROR at line 2:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
Elapsed: 00:00:00.05
SQL>
该错误是由于单独临时表（ SNH_TMP_P1 ）和新的分区表（ SUBSCRIPTION_HISTORY ）的索引数目不同，
将SNH_TMP_P1的索引和新的分区表（ SUBSCRIPTION_HISTORY ）的索引统一后可以解决问题：
SQL> create index IDX_SNH_TMP_CHARGESUBSID_lunar on SNH_TMP_P1 (CHARGESUBSID);
Index created.
Elapsed: 00:00:00.43
SQL> create index IDX_SNH_TMP_HISTORYID_lunar on SNH_TMP_P1 (HISTORYID);
Index created.
Elapsed: 00:00:00.49
SQL>
SQL> alter table SUBSCRIPTION_HISTORY
2 exchange partition SUBNHIS_P1 with table SNH_TMP_P1
3 including indexes
4 without validation
5 /
Table altered.
Elapsed: 00:00:00.21
SQL> select count(*) from SUBSCRIPTION_HISTORY;
COUNT(*)
----------
64475
Elapsed: 00:00:00.05
SQL> select count(*) from SUBSCRIPTION_HISTORY partition(SUBNHIS_P1);
COUNT(*)
----------
64475
Elapsed: 00:00:00.06
SQL>
SQL> select count(*) from SNH_TMP_P1;
COUNT(*)
----------
0
Elapsed: 00:00:00.03
SQL>
---------------------------------------------------------------
检查这时是否可以向SUBSCRIPTION_HISTORY中插入记录：
---------------------------------------------------------------
select HistoryId,actiondate from ttt;
update ttt set actiondate=actiondate+35,HistoryId=9999999;
select count(*) from SUBSCRIPTION_HISTORY partition(SUBNHIS_P2);
select HistoryId,actiondate from SUBSCRIPTION_HISTORY partition(SUBNHIS_P2) where HISTORYID='9999999';
insert into SUBSCRIPTION_HISTORY partition(SUBNHIS_P2) select * from ttt;
select count(*) from SUBSCRIPTION_HISTORY partition(SUBNHIS_P2) where HISTORYID='9999999';
select HistoryId,actiondate from SUBSCRIPTION_HISTORY partition(SUBNHIS_P2) where HISTORYID='9999999';
先测试是否可以往2月份分区插入数据（以2月份为例子）：
SQL> update ttt set actiondate=actiondate+35,HistoryId=9999999;
1 row updated.
Elapsed: 00:00:00.05
SQL> select HistoryId,actiondate from ttt;
HISTORYID ACTIONDAT
---------- ---------
9999999 29-FEB-04
Elapsed: 00:00:00.02
SQL> select count(*) from SUBSCRIPTION_HISTORY partition(SUBNHIS_P2);
COUNT(*)
----------
0
Elapsed: 00:00:00.02
SQL> select HistoryId,actiondate from SUBSCRIPTION_HISTORY partition(SUBNHIS_P2) where HISTORYID='9999999';
no rows selected
Elapsed: 00:00:00.02
SQL>
SQL> insert into SUBSCRIPTION_HISTORY partition(SUBNHIS_P2) select * from ttt;
1 row created.
Elapsed: 00:00:00.09
SQL> select count(*) from SUBSCRIPTION_HISTORY partition(SUBNHIS_P2) where HISTORYID='9999999';
COUNT(*)
----------
1
Elapsed: 00:00:00.02
SQL> select HistoryId,actiondate from SUBSCRIPTION_HISTORY partition(SUBNHIS_P2) where HISTORYID='9999999';
HISTORYID ACTIONDAT
---------- ---------
9999999 29-FEB-04
Elapsed: 00:00:00.03
SQL>
再做一个测试，看看是否可以往3月份分区插入数据（以2月份为例子）：
select HistoryId,actiondate from ttt;
update ttt set actiondate=actiondate+20,HistoryId=9999999;
select HistoryId,actiondate from ttt;
select count(*) from SUBSCRIPTION_HISTORY partition(SUBNHIS_P3);
select HistoryId,actiondate from SUBSCRIPTION_HISTORY partition(SUBNHIS_P3) where HISTORYID='9999999';
insert into SUBSCRIPTION_HISTORY partition(SUBNHIS_P3) select * from ttt;
select count(*) from SUBSCRIPTION_HISTORY partition(SUBNHIS_P3) where HISTORYID='9999999';
select HistoryId,actiondate from SUBSCRIPTION_HISTORY partition(SUBNHIS_P3) where HISTORYID='9999999';
SQL> select HistoryId,actiondate from ttt;
HISTORYID ACTIONDAT
---------- ---------
9999999 29-FEB-04
Elapsed: 00:00:00.01
SQL> update ttt set actiondate=actiondate+20,HistoryId=9999999;
1 row updated.
Elapsed: 00:00:00.02
SQL> select HistoryId,actiondate from ttt;
HISTORYID ACTIONDAT
---------- ---------
9999999 20-MAR-04
Elapsed: 00:00:00.01
SQL> select count(*) from SUBSCRIPTION_HISTORY partition(SUBNHIS_P3);
COUNT(*)
----------
0
Elapsed: 00:00:00.03
SQL> select HistoryId,actiondate from SUBSCRIPTION_HISTORY partition(SUBNHIS_P3) where HISTORYID='9999999';
no rows selected
Elapsed: 00:00:00.03
SQL> insert into SUBSCRIPTION_HISTORY partition(SUBNHIS_P3) select * from ttt;
1 row created.
Elapsed: 00:00:00.08
SQL> select count(*) from SUBSCRIPTION_HISTORY partition(SUBNHIS_P3) where HISTORYID='9999999';
COUNT(*)
----------
1
Elapsed: 00:00:00.01
SQL> select HistoryId,actiondate from SUBSCRIPTION_HISTORY partition(SUBNHIS_P3) where HISTORYID='9999999';
HISTORYID ACTIONDAT
---------- ---------
9999999 20-MAR-04
Elapsed: 00:00:00.01
SQL>
==================================================================================================
10、检查新表( SUBSCRIPTION_HISTORY )做交换后，索引的状态
==================================================================================================
SQL> l
1 select b.table_name || '.' || a.index_name "TAB.IND", a.partition_name, status,PARTITION_POSITION
2 from user_ind_partitions a, user_ind_columns b
3 where a.index_name=b.index_name
4 and b.table_name ='SUBSCRIPTION_HISTORY'
5* order by 1,PARTITION_POSITION
SQL> /
TAB.IND PARTITION_NAME STATUS PARTITION_POSITION
------------------------------------------------------------- ------------------------------ -------- ------------------
SUBSCRIPTION_HISTORY.IDX_SUBNHISTORY_MID_LUNAR IND_SUBNHIS_MID_P1 USABLE 1
IND_SUBNHIS_MID_P2 USABLE 2
IND_SUBNHIS_MID_P3 USABLE 3
IND_SUBNHIS_MID_P4 USABLE 4
IND_SUBNHIS_MID_P5 USABLE 5
IND_SUBNHIS_MID_P6 USABLE 6
IND_SUBNHIS_MID_P7 USABLE 7
IND_SUBNHIS_MID_P8 USABLE 8
IND_SUBNHIS_MID_P9 USABLE 9
IND_SUBNHIS_MID_P10 USABLE 10
IND_SUBNHIS_MID_P11 USABLE 11
IND_SUBNHIS_MID_P12 USABLE 12
SUBSCRIPTION_HISTORY.IDX_SUBNHISTORY_SERVID_LUNAR IND_SUBNHIS_SERV_P1 USABLE 1
IND_SUBNHIS_SERV_P2 USABLE 2
IND_SUBNHIS_SERV_P3 USABLE 3
IND_SUBNHIS_SERV_P4 USABLE 4
IND_SUBNHIS_SERV_P5 USABLE 5
IND_SUBNHIS_SERV_P6 USABLE 6
IND_SUBNHIS_SERV_P7 USABLE 7
IND_SUBNHIS_SERV_P8 USABLE 8
IND_SUBNHIS_SERV_P9 USABLE 9
IND_SUBNHIS_SERV_P10 USABLE 10
IND_SUBNHIS_SERV_P11 USABLE 11
IND_SUBNHIS_SERV_P12 USABLE 12
SUBSCRIPTION_HISTORY.IDX_SUBNHISTORY_SUBSID_LUNAR IND_SUBNHIS_ID_P1 USABLE 1
IND_SUBNHIS_ID_P2 USABLE 2
IND_SUBNHIS_ID_P3 USABLE 3
IND_SUBNHIS_ID_P4 USABLE 4
IND_SUBNHIS_ID_P5 USABLE 5
IND_SUBNHIS_ID_P6 USABLE 6
IND_SUBNHIS_ID_P7 USABLE 7
IND_SUBNHIS_ID_P8 USABLE 8
IND_SUBNHIS_ID_P9 USABLE 9
IND_SUBNHIS_ID_P10 USABLE 10
IND_SUBNHIS_ID_P11 USABLE 11
IND_SUBNHIS_ID_P12 USABLE 12
SUBSCRIPTION_HISTORY.IDX_SUBNH_CHARGESUBSID_LUNAR IND_SUBNHIS_CHARGESUBSID_0501 USABLE 1
IND_SUBNHIS_CHARGESUBSID_0502 USABLE 2
IND_SUBNHIS_CHARGESUBSID_0503 USABLE 3
IND_SUBNHIS_CHARGESUBSID_0504 USABLE 4
IND_SUBNHIS_CHARGESUBSID_0505 USABLE 5
IND_SUBNHIS_CHARGESUBSID_0506 USABLE 6
IND_SUBNHIS_CHARGESUBSID_0507 USABLE 7
IND_SUBNHIS_CHARGESUBSID_0508 USABLE 8
IND_SUBNHIS_CHARGESUBSID_0509 USABLE 9
IND_SUBNHIS_CHARGESUBSID_0510 USABLE 10
IND_SUBNHIS_CHARGESUBSID_0511 USABLE 11
IND_SUBNHIS_CHARGESUBSID_0512 USABLE 12
SUBSCRIPTION_HISTORY.IDX_SUBNH_HISTORYID_LUNAR IND_SUBNHIS_HISTORYID_0501 USABLE 1
IND_SUBNHIS_HISTORYID_0502 USABLE 2
IND_SUBNHIS_HISTORYID_0503 USABLE 3
IND_SUBNHIS_HISTORYID_0504 USABLE 4
IND_SUBNHIS_HISTORYID_0505 USABLE 5
IND_SUBNHIS_HISTORYID_0506 USABLE 6
IND_SUBNHIS_HISTORYID_0507 USABLE 7
IND_SUBNHIS_HISTORYID_0508 USABLE 8
IND_SUBNHIS_HISTORYID_0509 USABLE 9
IND_SUBNHIS_HISTORYID_0510 USABLE 10
IND_SUBNHIS_HISTORYID_0511 USABLE 11
IND_SUBNHIS_HISTORYID_0512 USABLE 12
60 rows selected.
Elapsed: 00:00:00.15
SQL>
drop table user_segment_tmp;
create table user_segment_tmp nologging as select * from user_segments;
set linesize 120
BREAK ON SEGMENT_NAME SKIP 1
col TABLESPACE_NAME for a30
col SEGMENT_NAME for a30
col PARTITION_NAME for a30
SELECT /*+ RULE */ SEGMENT_NAME,PARTITION_NAME,BYTES/1024/1024 MB ,TABLESPACE_NAME
FROM user_segment_tmp WHERE SEGMENT_NAME IN ('SUBSCRIPTION_HISTORY_BAK','SUBSCRIPTION_HISTORY')
ORDER BY 1,2,3,4
/
SQL> drop table user_segment_tmp;
Table dropped.
Elapsed: 00:00:00.13
SQL> create table user_segment_tmp nologging as select * from user_segments;
Table created.
Elapsed: 00:00:00.38
SQL>
SQL> l
1 SELECT /*+ RULE */ SEGMENT_NAME,PARTITION_NAME,BYTES/1024/1024 MB ,TABLESPACE_NAME
2 FROM user_segment_tmp WHERE SEGMENT_NAME IN ('SUBSCRIPTION_HISTORY_BAK','SUBSCRIPTION_HISTORY')
3* ORDER BY 1,2,3,4
SQL> /
SEGMENT_NAME PARTITION_NAME MB TABLESPACE_NAME
------------------------------ ------------------------------ ---------- ------------------------------
SUBSCRIPTION_HISTORY SUBNHIS_P1 7.90625 DATA_SUBN01
SUBNHIS_P10 4 DATA_SUBN02
SUBNHIS_P11 4 DATA_SUBN03
SUBNHIS_P12 4 DATA_SUBN04
SUBNHIS_P2 4 DATA_SUBN02
SUBNHIS_P3 4 DATA_SUBN03
SUBNHIS_P4 4 DATA_SUBN04
SUBNHIS_P5 4 DATA_SUBN05
SUBNHIS_P6 4 DATA_SUBN06
SUBNHIS_P7 4 DATA_SUBN07
SUBNHIS_P8 4 DATA_SUBN08
SUBNHIS_P9 7.90625 DATA_SUBN01
SUBSCRIPTION_HISTORY_BAK SUBNHIS_P1 4 DATA_DYNAMIC
SUBNHIS_P10 8 DATA_SUBN02
SUBNHIS_P11 8 DATA_SUBN03
SUBNHIS_P12 8 DATA_SUBN04
SUBNHIS_P2 4 DATA_SUBN02
SUBNHIS_P3 8 DATA_SUBN03
SUBNHIS_P4 8 DATA_SUBN04
SUBNHIS_P5 8 DATA_SUBN05
SUBNHIS_P6 8 DATA_SUBN06
SUBNHIS_P7 8 DATA_SUBN07
SUBNHIS_P8 8 DATA_SUBN08
SUBNHIS_P9 7.90625 DATA_SUBN01
24 rows selected.
Elapsed: 00:00:00.02
SQL>
看到，老表中和临时表作交换的分区的表空间已经变化到DATA_DYNAMIC了，
因此，可以在老表每次做完exchange后，将空的分区drop 掉：
SQL> ALTER TABLE SUBSCRIPTION_HISTORY_BAK DROP PARTITION SUBNHIS_P1;
Table altered.
Elapsed: 00:00:00.26
SQL>
drop table user_segment_tmp;
create table user_segment_tmp nologging as select * from user_segments;
set linesize 120
BREAK ON SEGMENT_NAME SKIP 1
col TABLESPACE_NAME for a30
col SEGMENT_NAME for a30
col PARTITION_NAME for a30
SELECT /*+ RULE */ SEGMENT_NAME,PARTITION_NAME,BYTES/1024/1024 MB ,TABLESPACE_NAME
FROM user_segment_tmp WHERE SEGMENT_NAME IN ('SUBSCRIPTION_HISTORY_BAK','SUBSCRIPTION_HISTORY')
ORDER BY 1,2,3,4
/
SQL> drop table user_segment_tmp;
Table dropped.
Elapsed: 00:00:00.13
SQL> create table user_segment_tmp nologging as select * from user_segments;
Table created.
Elapsed: 00:00:00.38
SQL>
SQL> L
1 SELECT /*+ RULE */ SEGMENT_NAME,PARTITION_NAME,BYTES/1024/1024 MB ,TABLESPACE_NAME
2 FROM user_segment_tmp WHERE SEGMENT_NAME IN ('SUBSCRIPTION_HISTORY_BAK','SUBSCRIPTION_HISTORY')
3* ORDER BY 1,2,3,4
SQL> /
SEGMENT_NAME PARTITION_NAME MB TABLESPACE_NAME
------------------------------ ------------------------------ ---------- ------------------------------
SUBSCRIPTION_HISTORY SUBNHIS_P1 7.90625 DATA_SUBN01
SUBNHIS_P10 4 DATA_SUBN02
SUBNHIS_P11 4 DATA_SUBN03
SUBNHIS_P12 4 DATA_SUBN04
SUBNHIS_P2 4 DATA_SUBN02
SUBNHIS_P3 4 DATA_SUBN03
SUBNHIS_P4 4 DATA_SUBN04
SUBNHIS_P5 4 DATA_SUBN05
SUBNHIS_P6 4 DATA_SUBN06
SUBNHIS_P7 4 DATA_SUBN07
SUBNHIS_P8 4 DATA_SUBN08
SUBNHIS_P9 7.90625 DATA_SUBN01
SUBSCRIPTION_HISTORY_BAK SUBNHIS_P10 8 DATA_SUBN02
SUBNHIS_P11 8 DATA_SUBN03
SUBNHIS_P12 8 DATA_SUBN04
SUBNHIS_P2 4 DATA_SUBN02
SUBNHIS_P3 8 DATA_SUBN03
SUBNHIS_P4 8 DATA_SUBN04
SUBNHIS_P5 8 DATA_SUBN05
SUBNHIS_P6 8 DATA_SUBN06
SUBNHIS_P7 8 DATA_SUBN07
SUBNHIS_P8 8 DATA_SUBN08
SUBNHIS_P9 7.90625 DATA_SUBN01
23 rows selected.
Elapsed: 00:00:00.03
SQL>
对于每一个分区空间依次交换，如果没有global index，分别rebuild后就完全正常了。如果存在global index，还需要做改索引的rebuild。
扫一扫
153
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
