通过案例学调优之--分区表基本管理
来源：互联网 发布：js代码混淆加密工具 编辑：程序博客网 时间：2024/05/24 05:43
通过案例学调优之--分区表基本管理
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 TBS4
2、创建分区
创建range 分区：
15:26:04 SYS@ test1 >create table part_t115: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_segments15:35:22 2 WHERE segment_name='PART_T1';SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES EXTENTS-------------------- ------------------ ------------------------------ ---------- ----------PART_T1 TABLE PARTITION TBS4 131072 2PART_T1 TABLE PARTITION TBS3 393216 6PART_T1 TABLE PARTITION TBS2 393216 6PART_T1 TABLE PARTITION TBS1 327680 5
15:31:38 SYS@ test1 >select table_name,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from dba_part_tables15: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
15:38:44 2 where table_name ='PART_T1';TABLE_NAME PARTITION_NAME TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------PART_T1 P1 TBS1PART_T1 P2 TBS2PART_T1 P3 TBS3PART_T1 P4 TBS4
创建hash 分区：
15:43:33 SYS@ test1 >create table part_hash_t215: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_segments15: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 5PART_T1 TABLE PARTITION TBS2 393216 6PART_T1 TABLE PARTITION TBS3 393216 6PART_T1 TABLE PARTITION TBS4 131072 2PART_HASH_T2 TABLE PARTITION TBS1 393216 6PART_HASH_T2 TABLE PARTITION TBS2 458752 7PART_HASH_T2 TABLE PARTITION TBS3 131072 2PART_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 5PART_T1 TABLE PARTITION P2 TBS2 393216 6PART_T1 TABLE PARTITION P3 TBS3 393216 6PART_T1 TABLE PARTITION P4 TBS4 131072 2PART_HASH_T2 TABLE PARTITION SYS_P41 TBS1 393216 6PART_HASH_T2 TABLE PARTITION SYS_P42 TBS2 458752 7PART_HASH_T2 TABLE PARTITION SYS_P43 TBS3 131072 2PART_HASH_T2 TABLE PARTITION SYS_P44 TBS4 262144 4创建List分区：
16:07:00 SYS@ test1 >create table part_ls_t316: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_objects16: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_segments16: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 5PART_T1 TABLE PARTITION P2 TBS2 393216 6PART_T1 TABLE PARTITION P3 TBS3 393216 6PART_T1 TABLE PARTITION P4 TBS4 131072 2PART_HASH_T2 TABLE PARTITION SYS_P41 TBS1 393216 6PART_HASH_T2 TABLE PARTITION SYS_P42 TBS2 458752 7PART_HASH_T2 TABLE PARTITION SYS_P43 TBS3 131072 2PART_HASH_T2 TABLE PARTITION SYS_P44 TBS4 262144 4PART_ls_T3 TABLE PARTITION LS_P1 TBS1 720896 11PART_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_segments16: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 5PART_T1 TABLE PARTITION P2 TBS2 393216 6PART_T1 TABLE PARTITION P4 TBS4 131072 2PART_HASH_T2 TABLE PARTITION SYS_P41 TBS1 393216 6PART_HASH_T2 TABLE PARTITION SYS_P42 TBS2 458752 7PART_HASH_T2 TABLE PARTITION SYS_P43 TBS3 131072 2PART_HASH_T2 TABLE PARTITION SYS_P44 TBS4 262144 4PART_T1 TABLE PARTITION P31 TBS1 196608 3PART_T1 TABLE PARTITION P32 TBS2 196608 3PART_LS_T3 TABLE PARTITION LS_P1 TBS1 720896 11PART_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_partitions16:34:03 2 where table_name='PART_T1';TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME------------------------------ ------------------------------ ------------------ --------------------PART_T1 P1 1 TBS1PART_T1 P2 2 TBS2PART_T1 P31 3 TBS1PART_T1 P32 4 TBS2PART_T1 P4 5 TBS4
截断分区（truncate）：
16:34:14 SYS@ test1 >select count(*) from part_t1 partition(p32); COUNT(*)---------- 199316: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_partitions12:44:09 2 where table_name='PART_T1';TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME------------------------------ ------------------------------ ------------------ ------------------------------PART_T1 P1 1 TBS1PART_T1 P2 2 TBS2PART_T1 P3 3 SYSTEMPART_T1 P4 4 TBS4PART_T1 P_MAX 5 TBS4
16:40:36 SYS@ test1 >select count(*) from part_t1 partition(p3); COUNT(*)---------- 1874Elapsed: 00:00:00.0112:44:14 SYS@ test1>alter table part_t1 move partition p3 tablespace tbs3;Table altered.Elapsed: 00:00:00.1312: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 TBS1PART_T1 P2 2 TBS2PART_T1 P3 3 TBS3PART_T1 P4 4 TBS4PART_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 TBS1PART_T1 P2 2 TBS2PART_T1 P3A 3 TBS3PART_T1 P3B 4 TBS3PART_T1 P4 5 TBS4PART_T1 P_MAX 6 TBS4
12:50:23 SYS@ test1>alter table part_t1 merge partitions p3a,p3b into partition p3 tablespace tbs3;Table altered.Elapsed: 00:00:00.2012: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 TBS1PART_T1 P2 2 TBS2PART_T1 P3 3 TBS3PART_T1 P4 4 TBS4PART_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_partitions16:44:24 2 where table_name='PART_T1';TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME------------------------------ ------------------------------ ------------------ --------------------PART_T1 P1 1 TBS1PART_T1 P2 2 TBS2PART_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_partitions12:40:35 2 where table_name='PART_T1';TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME------------------------------ ------------------------------ ------------------ ------------------------------PART_T1 P1 1 TBS1PART_T1 P2 2 TBS2PART_T1 P3 3 TBS3PART_T1 P4 4 TBS4PART_T1 P_MAX 5 TBS416: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_objects16:51:26 4 where object_id >12000 and object_id <14000;Table created.16:52:07 SYS@ test1 >select count(*) from t5_obj; COUNT(*)---------- 99616: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
0 0
- 通过案例学调优之--分区表基本管理
- 通过案例学调优之--分区表基本概念
- 通过案例学调优之--分区表索引
- 通过案例学调优之--JOB管理
- 通过案例学调优之--AWR Snapshot管理
- 通过案例学调优之--AWR BaseLine管理
- 通过案例学调优之--10046事件
- 通过案例学调优之--Oracle ADDM
- 通过案例学调优之--10046事件
- 通过案例学调优之--AWR基本概念
- 通过案例学调优之--SQL Profile
- 通过案例学调优之--Oracle Latch基础知识
- 通过案例学调优之--RECORDS_PER_BLOCK参数
- 通过案例学调优之--Oracle 全文索引
- 通过案例学调优之--Oracle Cluster Table
- 通过案例学调优之--RECORDS_PER_BLOCK参数
- Hive之管理表 外部表 分区表
- 管理分区表
- Oracle参数open_cursors和session_cached_cursor详解
- 通过案例学调优之--IOSTAT(查看磁盘I/O)
- 通过案例学调优之--分区表基本概念
- mac系统怎样使用xcode从svn服务器签出(checkout)项目源代码
- Java-Collections Framework学习与总结-LinkedHashMap
- 通过案例学调优之--分区表基本管理
- Oracle GoldenGate学习之--基本概念和配置（1）
- Oracle GoldenGate学习之--基本概念和配置(2)
- Oracle GoldenGate学习之--基本概念和配置（3）
- 通过案例学调优之--分区表索引
- Oracle Study--Oracle Supplemental Log补全日志介绍
- Oracle Study--RAW Device在Oracle下的应用
- 通过案例学Oracle之--一次AIX rac误操作引起的“血案”
- 【LeetCode】100.Same Tree
