1、建立tablespace并将数据文件存储到不同表空间（分散I/O)
15:15:14 SYS@ test1 >select file_id,file_name,tablespace_name from dba_data_files order by 3;
|
1
2
3
4
5
6
|
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
5 /dsk1/oradata/test1/tbs1.dbf TBS1
6 /dsk2/oradata/test1/tbs2.dbf TBS2
8 /dsk3/oradata/test1/tbs3.dbf TBS3
9 /dsk4/oradata/test1/tbs4.dbf TBS4
|
2、创建分区
创建range 分区：
|
1
2
3
4
5
6
7
8
9
|
15:26:04 SYS@ test1 >create table part_t1
15:27:35 2 PARTITION BY RANGE (object_id)
15:27:35 3 (partition p1 values less than (4000) tablespace tbs1,
15:27:35 4 partition p2 values less than (8000) tablespace tbs2,
15:27:35 5 partition p3 values less than (12000) tablespace tbs3,
15:27:35 6 partition p4 values less than (maxvalue) tablespace tbs4)
15:27:35 7 as
15:27:35 8 select owner,object_name,object_id,object_type,TIMESTAMP,status from dba_objects;
Table created.
|
查看分区信息：
|
1
2
3
4
|
15:27:38 SYS@ test1 >select count(*) from part_t1 partition(p1);
COUNT(*)
----------
3931
|
每个分区都是一个都是的segment：
|
1
2
3
4
5
6
7
8
|
15:34:42 SYS@ test1 >select segment_name,segment_type,tablespace_name,bytes,extents from dba_segments
15:35:22 2 WHERE segment_name=
'PART_T1'
;
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES EXTENTS
-------------------- ------------------ ------------------------------ ---------- ----------
PART_T1 TABLE PARTITION TBS4 131072 2
PART_T1 TABLE PARTITION TBS3 393216 6
PART_T1 TABLE PARTITION TBS2 393216 6
PART_T1 TABLE PARTITION TBS1 327680 5
|
|
1
2
3
4
5
|
15:31:38 SYS@ test1 >select table_name,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from dba_part_tables
15:32:21 2 where table_name=
'PART_T1'
;
TABLE_NAME PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
PART_T1 RANGE 4 VALID
|
15:38:23 SYS@ test1 >select table_name,PARTITION_NAME,TABLESPACE_NAME from dba_tab_partitions
|
1
2
3
4
5
6
7
|
15:38:44 2 where table_name =
'PART_T1'
;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PART_T1 P1 TBS1
PART_T1 P2 TBS2
PART_T1 P3 TBS3
PART_T1 P4 TBS4
|
创建hash 分区：
|
1
2
3
4
5
6
|
15:43:33 SYS@ test1 >create table part_hash_t2
15:43:45 2 PARTITION BY HASH (OBJECT_TYPE)
15:43:45 3 partitions 4 store in (tbs1,tbs2,tbs3,tbs4)
15:43:45 4 as
15:43:45 5 select owner,object_name,object_id,object_type,TIMESTAMP,status from dba_objects;
Table created.
|
|
1
2
3
4
5
6
7
8
9
10
11
12
|
15:43:46 SYS@ test1 >select segment_name,segment_type,tablespace_name,bytes,extents from dba_segments
15:44:39 2 where tablespace_name in (
'TBS1'
,
'TBS2'
,
'TBS3'
,
'TBS4'
);
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES EXTENTS
-------------------- ------------------ ------------------------------ ---------- ----------
PART_T1 TABLE PARTITION TBS1 327680 5
PART_T1 TABLE PARTITION TBS2 393216 6
PART_T1 TABLE PARTITION TBS3 393216 6
PART_T1 TABLE PARTITION TBS4 131072 2
PART_HASH_T2 TABLE PARTITION TBS1 393216 6
PART_HASH_T2 TABLE PARTITION TBS2 458752 7
PART_HASH_T2 TABLE PARTITION TBS3 131072 2
PART_HASH_T2 TABLE PARTITION TBS4 262144 4
|
|
1
2
3
4
5
6
7
8
9
10
11
12
|
sql> select segment_name,segment_type,PARTITION_NAME,tablespace_name,bytes,extents from dba_segments
2
* where tablespace_name in (
'TBS1'
,
'TBS2'
,
'TBS3'
,
'TBS4'
)
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME BYTES EXTENTS
-------------------- ------------------ ------------------------------ -------------------- ---------- ----------
PART_T1 TABLE PARTITION P1 TBS1
327680
5
PART_T1 TABLE PARTITION P2 TBS2
393216
6
PART_T1 TABLE PARTITION P3 TBS3
393216
6
PART_T1 TABLE PARTITION P4 TBS4
131072
2
PART_HASH_T2 TABLE PARTITION SYS_P41 TBS1
393216
6
PART_HASH_T2 TABLE PARTITION SYS_P42 TBS2
458752
7
PART_HASH_T2 TABLE PARTITION SYS_P43 TBS3
131072
2
PART_HASH_T2 TABLE PARTITION SYS_P44 TBS4
262144
4
|
创建List分区：
|
1
2
3
4
5
6
7
8
|
16:07:00 SYS@ test1 >create table part_ls_t3
16:07:52 2 PARTITION BY list(owner)
16:07:52 3 (partition ls_p1 values (
'SYS'
) tablespace tbs1,
16:07:53 4 partition ls_p2 values (
'SCOTT'
) tablespace tbs2)
16:07:53 5 as
16:07:53 6 select owner,object_name,object_id,object_type,TIMESTAMP,status from dba_objects
16:07:53 7 where owner in (
'SYS'
,
'SCOTT'
);
Table created.
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
16:07:54 SYS@ test1 >select segment_name,segment_type,PARTITION_NAME,tablespace_name,bytes,extents from dba_segments
16:08:54 2 where tablespace_name in (
'TBS1'
,
'TBS2'
,
'TBS3'
,
'TBS4'
);
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME BYTES EXTENTS
-------------------- ------------------ ------------------------------ -------------------- ---------- ----------
PART_T1 TABLE PARTITION P1 TBS1 327680 5
PART_T1 TABLE PARTITION P2 TBS2 393216 6
PART_T1 TABLE PARTITION P3 TBS3 393216 6
PART_T1 TABLE PARTITION P4 TBS4 131072 2
PART_HASH_T2 TABLE PARTITION SYS_P41 TBS1 393216 6
PART_HASH_T2 TABLE PARTITION SYS_P42 TBS2 458752 7
PART_HASH_T2 TABLE PARTITION SYS_P43 TBS3 131072 2
PART_HASH_T2 TABLE PARTITION SYS_P44 TBS4 262144 4
PART_ls_T3 TABLE PARTITION LS_P1 TBS1 720896 11
PART_ls_T3 TABLE PARTITION LS_P2 TBS2 65536 1
|
3、管理分区
拆分分区（split）：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
16:29:47 SYS@ test1 >alter table part_t1 split partition p3 at(10000) into(partition p31 tablespace tbs1,partition p32 tablespace tbs2);
Table altered.
16:30:46 SYS@ test1 >select segment_name,segment_type,PARTITION_NAME,tablespace_name,bytes,extents from dba_segments
16:31:45 2 where tablespace_name in (
'TBS1'
,
'TBS2'
,
'TBS3'
,
'TBS4'
)
16:31:55 3 /
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME BYTES EXTENTS
-------------------- ------------------ ------------------------------ -------------------- ---------- ----------
PART_T1 TABLE PARTITION P1 TBS1 327680 5
PART_T1 TABLE PARTITION P2 TBS2 393216 6
PART_T1 TABLE PARTITION P4 TBS4 131072 2
PART_HASH_T2 TABLE PARTITION SYS_P41 TBS1 393216 6
PART_HASH_T2 TABLE PARTITION SYS_P42 TBS2 458752 7
PART_HASH_T2 TABLE PARTITION SYS_P43 TBS3 131072 2
PART_HASH_T2 TABLE PARTITION SYS_P44 TBS4 262144 4
PART_T1 TABLE PARTITION P31 TBS1 196608 3
PART_T1 TABLE PARTITION P32 TBS2 196608 3
PART_LS_T3 TABLE PARTITION LS_P1 TBS1 720896 11
PART_LS_T3 TABLE PARTITION LS_P2 TBS2 65536 1
|
|
1
2
3
4
5
6
7
8
9
|
16:33:13 SYS@ test1 >select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions
16:34:03 2 where table_name=
'PART_T1'
;
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ --------------------
PART_T1 P1 1 TBS1
PART_T1 P2 2 TBS2
PART_T1 P31 3 TBS1
PART_T1 P32 4 TBS2
PART_T1 P4 5 TBS4
|
截断分区（truncate）：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
16:34:14 SYS@ test1 >select count(*) from part_t1 partition(p32);
COUNT(*)
----------
1993
16:36:26 SYS@ test1 >alter table part_t1 truncate partition p32;
Table truncated.
16:36:53 SYS@ test1 >select count(*) from part_t1 partition(p32);
COUNT(*)
----------
0
|
移动分区（move）：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
12:40:45 SYS@ test1>alter table part_t1 move partition p3 tablespace
system
;
Table altered.
12:44:02 SYS@ test1>select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions
12:44:09 2 where table_name=
'PART_T1'
;
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
PART_T1 P1 1 TBS1
PART_T1 P2 2 TBS2
PART_T1 P3 3 SYSTEM
PART_T1 P4 4 TBS4
PART_T1 P_MAX 5 TBS4
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
16:40:36 SYS@ test1 >select count(*) from part_t1 partition(p3);
COUNT(*)
----------
1874
Elapsed: 00:00:00.01
12:44:14 SYS@ test1>alter table part_t1 move partition p3 tablespace tbs3;
Table altered.
Elapsed: 00:00:00.13
12:44:50 SYS@ test1>select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions where table_name=
'PART_T1'
;
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
PART_T1 P1 1 TBS1
PART_T1 P2 2 TBS2
PART_T1 P3 3 TBS3
PART_T1 P4 4 TBS4
PART_T1 P_MAX 5 TBS4
|
合并分区（merge）：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
12:44:57 SYS@ test1>alter table part_t1 split partition p3 at (10000) into (partition p3a tablespace tbs3,partition p3b tablespace tbs3);
Table altered.
12:47:21 SYS@ test1>select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions where table_name=
'PART_T1'
;
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
PART_T1 P1 1 TBS1
PART_T1 P2 2 TBS2
PART_T1 P3A 3 TBS3
PART_T1 P3B 4 TBS3
PART_T1 P4 5 TBS4
PART_T1 P_MAX 6 TBS4
|
|
1
2
3
4
5
6
7
8
9
10
11
|
12:50:23 SYS@ test1>alter table part_t1 merge partitions p3a,p3b into partition p3 tablespace tbs3;
Table altered.
Elapsed: 00:00:00.20
12:51:03 SYS@ test1>select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions where table_name=
'PART_T1'
;
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
PART_T1 P1 1 TBS1
PART_T1 P2 2 TBS2
PART_T1 P3 3 TBS3
PART_T1 P4 4 TBS4
PART_T1 P_MAX 5 TBS4
|
删除分区（drop）：
|
1
2
3
4
5
6
7
8
|
16:44:28 SYS@ test1 > alter table part_t1 drop partition p4;
16:44:18 SYS@ test1 >select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions
16:44:24 2 where table_name=
'PART_T1'
;
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ --------------------
PART_T1 P1 1 TBS1
PART_T1 P2 2 TBS2
PART_T1 P3 3 TBS3
|
增加新分区（add）：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
12:33:32 SYS@ test1>alter table part_t1 add partition p5 values less than (maxvalue) tablespace tbs4;
alter table part_t1 add partition p5 values less than (maxvalue) tablespace tbs4
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
如果设定了max values 参数，增加新分区，应用split 拆分
12:38:13 SYS@ test1>alter table part_t1 split partition p4 at(20000) into (partition p4 tablespace tbs4,partition p_max tablespace tbs4);
Table altered.
12:40:24 SYS@ test1>select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions
12:40:35 2 where table_name=
'PART_T1'
;
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
PART_T1 P1 1 TBS1
PART_T1 P2 2 TBS2
PART_T1 P3 3 TBS3
PART_T1 P4 4 TBS4
PART_T1 P_MAX 5 TBS4
16:47:03 SYS@ test1 >select count(*) from part_t1 partition(p4);
COUNT(*)
----------
0
16:47:58 SYS@ test1 >select count(*) from part_t1 partition(p5);
COUNT(*)
----------
0
|
分区表交换（exchange）：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
16:49:46 SYS@ test1 >create table t5_obj
16:50:59 2 as
16:51:01 3 select owner,object_name,object_id,object_type,TIMESTAMP,status from dba_objects
16:51:26 4 where object_id >12000 and object_id <14000;
Table created.
16:52:07 SYS@ test1 >select count(*) from t5_obj;
COUNT(*)
----------
996
16:54:23 SYS@ test1 >alter table part_t1 exchange partition p4 with table t5_obj;
Table altered.
16:55:22 SYS@ test1 >select count(*) from part_t1 partition(p4);
COUNT(*)
----------
996
|
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1549407，如需转载请自行联系原作者
