oracle 分区增加list,ORACLE 分區添加管理

創建範圍分區表

CREATE TABLE test_range_tab

(

range_tab_seq number not null,

range_tab_no varchar2(20),

range_tab_name varchar2(20),

range_tab_date date --範圍分區列

)partition by range (range_tab_date)

(

partition P1901 values less than (TO_DATE('2019-02-01', 'YYYY-MM-DD')) tablespace MPBUS_1901,

partition P1902 values less than (TO_DATE('2019-03-01', 'YYYY-MM-DD')) tablespace MPBUS_1902

);

alter table test_range_tab

add constraint pk_test_range_tab_local primary key (range_tab_date,range_tab_seq)

using index local

(

PARTITION P1901 TABLESPACE I_MPBUS_1901,

PARTITION P1902 TABLESPACE I_MPBUS_1902

);

create index idx_test_range_tab_1 on test_range_tab(range_tab_date,range_tab_no)

local

(

PARTITION P1901 TABLESPACE I_MPBUS_1901,

PARTITION P1902 TABLESPACE I_MPBUS_1902

);

select table_name,partition_name,tablespace_name from user_tab_partitions

where table_name=upper('test_range_tab') order by partition_position;

table_name partition_name tablespace_name

1 TEST_RANGE_TAB P1901 MPBUS_1901

2 TEST_RANGE_TAB P1902 MPBUS_1902

select INDEX_NAME,PARTITION_NAME,STATUS,a1.tablespace_name from dba_ind_partitions a1

where a1.index_name in(upper('idx_test_range_tab_1'),upper('pk_test_range_tab_local'));

1 IDX_TEST_RANGE_TAB_1 P1901 USABLE I_MPBUS_1901

2 IDX_TEST_RANGE_TAB_1 P1902 USABLE I_MPBUS_1902

3 PK_TEST_RANGE_TAB_LOCAL P1901 USABLE I_MPBUS_1901

4 PK_TEST_RANGE_TAB_LOCAL P1902 USABLE I_MPBUS_1902

添加分區

#添加分區

alter table test_range_tab add PARTITION P1903 VALUES

LESS THAN (TO_DATE('2019-04-01', 'YYYY-MM-DD')) TABLESPACE MPBUS_1903;

alter index pk_test_range_tab_local rebuild partition P1903 TABLESPACE I_MPBUS_1903;

alter index idx_test_range_tab_1 rebuild partition P1903 TABLESPACE I_MPBUS_1903;

select table_name,partition_name,tablespace_name from user_tab_partitions

where table_name=upper('test_range_tab') order by partition_position;

table_name partition_name tablespace_name

1 TEST_RANGE_TAB P1901 MPBUS_1901

2 TEST_RANGE_TAB P1902 MPBUS_1902

2 TEST_RANGE_TAB P1903 MPBUS_1903

select INDEX_NAME,PARTITION_NAME,STATUS,a1.tablespace_name from dba_ind_partitions a1

where a1.index_name in(upper('idx_test_range_tab_1'),upper('pk_test_range_tab_local'));

1 IDX_TEST_RANGE_TAB_1 P1901 USABLE I_MPBUS_1901

2 IDX_TEST_RANGE_TAB_1 P1902 USABLE I_MPBUS_1902

3 IDX_TEST_RANGE_TAB_1 P1903 USABLE I_MPBUS_1903

4 PK_TEST_RANGE_TAB_LOCAL P1901 USABLE I_MPBUS_1901

5 PK_TEST_RANGE_TAB_LOCAL P1902 USABLE I_MPBUS_1902

6 PK_TEST_RANGE_TAB_LOCAL P1902 USABLE I_MPBUS_1903

創建帶有maxvalue的範圍分区表

drop TABLE test_range_tab;

CREATE TABLE test_range_tab

(

range_tab_seq number not null,

range_tab_no varchar2(20),

range_tab_name varchar2(20),

range_tab_date date --範圍分區列

)partition by range (range_tab_date)

(

partition P1901 values less than (TO_DATE('2019-02-01', 'YYYY-MM-DD')) tablespace MPBUS_1901,

partition P1902 values less than (TO_DATE('2019-03-01', 'YYYY-MM-DD')) tablespace MPBUS_1902,

partition PMAX values less than (maxvalue) tablespace MPBUS_1903

);

alter table test_range_tab

add constraint pk_test_range_tab_local primary key (range_tab_date,range_tab_seq)

using index local

(

PARTITION P1901 TABLESPACE I_MPBUS_1901,

PARTITION P1902 TABLESPACE I_MPBUS_1902,

PARTITION PMAX TABLESPACE I_MPBUS_1903

);

create index idx_test_range_tab_1 on test_range_tab(range_tab_date,range_tab_no)

local

(

PARTITION P1901 TABLESPACE I_MPBUS_1901,

PARTITION P1902 TABLESPACE I_MPBUS_1902,

PARTITION PMAX TABLESPACE I_MPBUS_1903

);

select table_name,partition_name,tablespace_name from user_tab_partitions

where table_name=upper('test_range_tab') order by partition_position;

1 TEST_RANGE_TAB P1901 MPBUS_1901

2 TEST_RANGE_TAB P1902 MPBUS_1902

3 TEST_RANGE_TAB PMAX MPBUS_1903

select INDEX_NAME,PARTITION_NAME,STATUS,a1.tablespace_name from dba_ind_partitions a1

where a1.index_name in(upper('idx_test_range_tab_1'),upper('pk_test_range_tab_local'));

1 IDX_TEST_RANGE_TAB_1 P1901 USABLE I_MPBUS_1901

2 IDX_TEST_RANGE_TAB_1 P1902 USABLE I_MPBUS_1902

3 IDX_TEST_RANGE_TAB_1 PMAX USABLE I_MPBUS_1903

4 PK_TEST_RANGE_TAB_LOCAL P1901 USABLE I_MPBUS_1901

5 PK_TEST_RANGE_TAB_LOCAL P1902 USABLE I_MPBUS_1902

6 PK_TEST_RANGE_TAB_LOCAL PMAX USABLE I_MPBUS_1903

insert into test_range_tab(range_tab_seq,range_tab_no,range_tab_name,range_tab_date)

values(1,'a1','n1',date'2019-02-01');

insert into test_range_tab(range_tab_seq,range_tab_no,range_tab_name,range_tab_date)

values(2,'a2','n2',date'2019-03-01');

insert into test_range_tab(range_tab_seq,range_tab_no,range_tab_name,range_tab_date)

values(3,'a3','n3',date'2019-04-01');

commit;

select * from test_range_tab partition(PMAX);

2 a2 n2 1/3/2019

3 a3 n3 1/4/2019

#分割分區

alter table test_range_tab split partition PMAX at(TO_DATE('2019-04-01', 'YYYY-MM-DD'))

into (partition P1903 tablespace MPBUS_1903, partition PMAX tablespace MPBUS_1904);

alter index pk_test_range_tab_local rebuild partition P1903 TABLESPACE I_MPBUS_1903;

alter index idx_test_range_tab_1 rebuild partition P1903 TABLESPACE I_MPBUS_1903;

alter index pk_test_range_tab_local rebuild partition PMAX TABLESPACE I_MPBUS_1904;

alter index idx_test_range_tab_1 rebuild partition PMAX TABLESPACE I_MPBUS_1904;

select * from test_range_tab partition(PMAX);

3 a3 n3 1/4/2019

select table_name,partition_name,tablespace_name,partition_position,high_value from user_tab_partitions

where table_name=upper('test_range_tab') order by partition_position;

1 TEST_RANGE_TAB P1901 MPBUS_1901 1

2 TEST_RANGE_TAB P1902 MPBUS_1902 2

3 TEST_RANGE_TAB P1903 MPBUS_1903 3

4 TEST_RANGE_TAB PMAX MPBUS_1904 4

select INDEX_NAME,PARTITION_NAME,STATUS,a1.tablespace_name from dba_ind_partitions a1

where a1.index_name in(upper('idx_test_range_tab_1'),upper('pk_test_range_tab_local'));

1 IDX_TEST_RANGE_TAB_1 P1901 USABLE I_MPBUS_1901

2 IDX_TEST_RANGE_TAB_1 P1902 USABLE I_MPBUS_1902

3 IDX_TEST_RANGE_TAB_1 P1903 USABLE I_MPBUS_1903

4 IDX_TEST_RANGE_TAB_1 PMAX USABLE I_MPBUS_1904

5 PK_TEST_RANGE_TAB_LOCAL P1901 USABLE I_MPBUS_1901

6 PK_TEST_RANGE_TAB_LOCAL P1902 USABLE I_MPBUS_1902

7 PK_TEST_RANGE_TAB_LOCAL P1903 USABLE I_MPBUS_1903

8 PK_TEST_RANGE_TAB_LOCAL PMAX USABLE I_MPBUS_1904

帶default 的列表分區 添加新分區

#drop TABLE test_list_tab;

CREATE TABLE test_list_tab

(

list_tab_seq number not null,

list_tab_no varchar2(20),

list_type varchar2(20)

) partition by list(list_type)

(

partition PAA values('A') tablespace MPBUS_1901,

partition PBC values('B','C') tablespace MPBUS_1902,

partition PDEFAULT VALUES(DEFAULT) tablespace MPBUS_1903

)

alter table test_list_tab

add constraint pk_test_list_tab primary key (list_type,list_tab_seq)

using index local

(

PARTITION PAA TABLESPACE I_MPBUS_1901,

PARTITION PBC TABLESPACE I_MPBUS_1902,

PARTITION PDEFAULT TABLESPACE I_MPBUS_1903

);

insert into test_list_tab(list_tab_seq,list_tab_no,list_type)

select 1,'A1','A' from dual

union all

select 2,'B1','B' from dual

union all

select 3,'C1','C' from dual

union all

select 4,'D1','D' from dual

union all

select 5,'E1','E' from dual;

coommit;

select table_name,partition_name,tablespace_name,partition_position,high_value from user_tab_partitions

where table_name=upper('test_list_tab') order by partition_position;

1 TEST_LIST_TAB PAA MPBUS_1901 1

2 TEST_LIST_TAB PBC MPBUS_1902 2

3 TEST_LIST_TAB PDEFAULT MPBUS_1903 3

select INDEX_NAME,PARTITION_NAME,STATUS,a1.tablespace_name from dba_ind_partitions a1

where a1.index_name in(upper('pk_test_list_tab'));

1 PK_TEST_LIST_TAB PAA USABLE I_MPBUS_1901

2 PK_TEST_LIST_TAB PBC USABLE I_MPBUS_1902

3 PK_TEST_LIST_TAB PDEFAULT USABLE I_MPBUS_1903

select * from test_list_tab partition(PDEFAULT)

4 D1 D

5 E1 E

alter table test_list_tab split partition PDEFAULT values ('D') into

(partition PDD tablespace MPBUS_1903,partition PDEFAULT tablespace MPBUS_1904);

select * from test_list_tab partition(PDEFAULT)

5 E1 E

alter index pk_test_list_tab rebuild partition PDD TABLESPACE I_MPBUS_1903;

alter index pk_test_list_tab rebuild partition PDEFAULT TABLESPACE I_MPBUS_1904;

select table_name,partition_name,tablespace_name,partition_position,high_value from user_tab_partitions

where table_name=upper('test_list_tab') order by partition_position;

1 TEST_LIST_TAB PAA MPBUS_1901 1

2 TEST_LIST_TAB PBC MPBUS_1902 2

3 TEST_LIST_TAB PDD MPBUS_1903 3

4 TEST_LIST_TAB PDEFAULT MPBUS_1904 4

select INDEX_NAME,PARTITION_NAME,STATUS,a1.tablespace_name from dba_ind_partitions a1

where a1.index_name in(upper('pk_test_list_tab'));

1 PK_TEST_LIST_TAB PAA USABLE I_MPBUS_1901

2 PK_TEST_LIST_TAB PBC USABLE I_MPBUS_1902

3 PK_TEST_LIST_TAB PDD USABLE I_MPBUS_1903

4 PK_TEST_LIST_TAB PDEFAULT USABLE I_MPBUS_1904

創建自動分區(按月)

CREATE TABLE test_range_tab

(

range_tab_seq number not null,

range_tab_no varchar2(20),

range_tab_name varchar2(20),

range_tab_date date --範圍分區列

)partition by range (range_tab_date) INTERVAL(numtoyminterval(1, 'month'))

(

partition P1901 values less than (TO_DATE('2019-02-01', 'YYYY-MM-DD')) tablespace MPBUS_1901,

partition P1902 values less than (TO_DATE('2019-03-01', 'YYYY-MM-DD')) tablespace MPBUS_1902

);

alter table test_range_tab

add constraint pk_test_range_tab primary key (range_tab_date,range_tab_seq)

using index local

(

PARTITION P1901 TABLESPACE I_MPBUS_1901,

PARTITION P1902 TABLESPACE I_MPBUS_1902

);

insert into test_range_tab(range_tab_seq,range_tab_no,range_tab_name,range_tab_date)

values(1,'a1','n1',date'2019-01-01');

insert into test_range_tab(range_tab_seq,range_tab_no,range_tab_name,range_tab_date)

values(2,'a2','n2',date'2019-02-01');

insert into test_range_tab(range_tab_seq,range_tab_no,range_tab_name,range_tab_date)

values(3,'a3','n3',date'2019-03-01');

commit;

select table_name,partition_name,tablespace_name,b1.partition_position from user_tab_partitions b1

where table_name=upper('test_range_tab') order by partition_position;

1 TEST_RANGE_TAB P1901 MPBUS_1901 1

2 TEST_RANGE_TAB P1902 MPBUS_1902 2

3 TEST_RANGE_TAB SYS_P2453 MPBUS_UD 3

#重新定義分區名稱及其表空間

alter table test_range_tab rename partition SYS_P2452 TO P1903;

alter table test_range_tab move partition P1903 tablespace MPBUS_1903;

select INDEX_NAME,PARTITION_NAME,STATUS,a1.tablespace_name,a1.partition_position from dba_ind_partitions a1

where a1.index_name in(upper('pk_test_range_tab_local'));

1 PK_TEST_RANGE_TAB P1901 USABLE I_MPBUS_1901 1

2 PK_TEST_RANGE_TAB P1902 USABLE I_MPBUS_1902 2

3 PK_TEST_RANGE_TAB SYS_P2453 UNUSABLE MPBUS_UD 3

#重新定義分區索引分區名稱及其表空間

alter index pk_test_range_tab rename partition SYS_P2453 TO P1903;

alter index pk_test_range_tab rebuild partition P1903 TABLESPACE I_MPBUS_1903;

select table_name,partition_name,tablespace_name,b1.partition_position from user_tab_partitions b1

where table_name=upper('test_range_tab') order by partition_position;

1 TEST_RANGE_TAB P1901 MPBUS_1901 1

2 TEST_RANGE_TAB P1902 MPBUS_1902 2

3 TEST_RANGE_TAB P1903 MPBUS_1903 3

select INDEX_NAME,PARTITION_NAME,STATUS,a1.tablespace_name,a1.partition_position from dba_ind_partitions a1

where a1.index_name in(upper('pk_test_range_tab'));

1 PK_TEST_RANGE_TAB P1901 USABLE I_MPBUS_1901 1

2 PK_TEST_RANGE_TAB P1902 USABLE I_MPBUS_1902 2

3 PK_TEST_RANGE_TAB P1903 USABLE I_MPBUS_1903 3

5.1 創建自動分區(按月)--修改默認表及索引的表空

CREATE TABLE test_range_tab

(

range_tab_seq number not null,

range_tab_no varchar2(20),

range_tab_date date --範圍分區列

)

partition by range (range_tab_date) INTERVAL(numtoyminterval(1, 'month'))

(

partition P1901 values less than (TO_DATE('2019-02-01', 'YYYY-MM-DD')) tablespace MPBUS_1901

);

alter table test_range_tab

add constraint pk_test_range_tab primary key (range_tab_date,range_tab_seq)

using index local

(

PARTITION P1901 TABLESPACE I_MPBUS_1901

);

#查看该分区表的默认表空间：

select table_name,def_tablespace_name from dba_part_tables where table_name=upper('test_range_tab');

1 TEST_RANGE_TAB MPBUS_UD

#查看该表上的分区索引的默认表空间：

select index_name,def_tablespace_name from dba_part_indexes where table_name=upper('test_range_tab');

1 PK_TEST_RANGE_TAB

#修改分区表的默认表空间：

alter table test_range_tab modify default attributes tablespace MPBUS_1902;

#修改该表上某个索引的默认表空间：

alter index pk_test_range_tab modify default attributes tablespace I_MPBUS_1902;

insert into test_range_tab(range_tab_seq,range_tab_no,range_tab_date)

values(1,'a1',date'2019-01-01');

insert into test_range_tab(range_tab_seq,range_tab_no,range_tab_date)

values(2,'a2',date'2019-02-01');

commit;

select table_name,partition_name,tablespace_name,b1.partition_position from user_tab_partitions b1

where table_name=upper('test_range_tab') order by partition_position;

1 TEST_RANGE_TAB P1901 MPBUS_1901 1

2 TEST_RANGE_TAB SYS_P2454 MPBUS_1902 2

select INDEX_NAME,PARTITION_NAME,STATUS,a1.tablespace_name,a1.partition_position from dba_ind_partitions a1

where a1.index_name in(upper('pk_test_range_tab'));

1 PK_TEST_RANGE_TAB P1901 USABLE I_MPBUS_1901 1

2 PK_TEST_RANGE_TAB SYS_P2454 USABLE I_MPBUS_1902 2

alter table test_range_tab rename partition SYS_P2454 TO P1902;

alter index pk_test_range_tab rename partition SYS_P2454 TO P1902;