通过案例学调优之--分区表基本管理
1、建立tablespace并将数据文件存储到不同表空间（分散I/O)
15:15:14 SYS@ test1 >select file_id,file_name,tablespace_name from dba_data_files order by 3;
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 TBS42、创建分区
创建range 分区：
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.查看分区信息：
15:27:38 SYS@ test1 >select count(*) from part_t1 partition(p1);
COUNT(*)
----------
3931每个分区都是一个都是的segment：
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 515: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 VALID15:38:23 SYS@ test1 >select table_name,PARTITION_NAME,TABLESPACE_NAME from dba_tab_partitions
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 分区：
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.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 4sql> 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分区：
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.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 13、管理分区
拆分分区（split）：
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 116: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）：
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）：
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 TBS416: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）：
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 TBS412: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）：
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）：
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）：
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本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/1549407
扫一扫
3620
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
