oracle 分区

 

1.查看现在oracle的大表sql：

select owner, segment_name, segment_type, num_ext, tot
  from (select owner,
               segment_name,
               segment_type,
               sum(extents) as num_ext,
               sum(bytes) / 1024 / 1024 tot
          from dba_segments
         group by owner, segment_name, segment_type
         order by sum(bytes) desc) t
 where rownum < 10

一张大表如果加上了业务逻辑，表明你可以考虑进行分区。当我们分区一张表会需要更多的设置与维护的工作。

所以在最初设计时能预计到增长速度的时候。而不是等以后增长到难以处理的时候进行分区。

 

--1.找出比较大的表
select owner, segment_name, segment_type, num_ext, tot
from (select owner,
segment_name,
segment_type,
sum(extents) as num_ext,
sum(bytes) / 1024 / 1024 tot
from dba_segments
group by owner, segment_name, segment_type
order by sum(bytes) desc) t
where rownum < 10

--2.范围分区表 partition by rang
create table p_rang_test
( id number,
name varchar2(20),
sex char(2)
)
partition by range(id)
(
partition p_200 values less than(200),
partition p_400 values less than(400),
partition p_600 values less than(600),
partition p_max values less than(maxvalue)
);

--3.hash分区

create table p_hash_test
( id number,
name varchar2(20),
sex char(2)
)
partition by hash(id);

--4.列表分区
create table p_list_test
( id number,
name varchar2(20),
sex char(2)
)
partition by list(sex)
(
partition nan_1 values('男'),
partition nv_1 values('女'),
partition def_1 values(default)
)

--5.可以将每个分区移动到指定的表空间。
create tablespace tt1 datafile'D:\ORACLE\ADMINISTRATOR\ORADATA\YFTEST\tt1.dbf' size 100m;
alter table p_rang_test move partition p_200 tablespace tt1;

SQL> select * from dba_segments t where t.owner='TEST';


OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SEGMENT_SUBTYPE TABLESPACE_NAME HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE RETENTION MINRETENTION PCT_INCREASE FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ --------------- ------------------------------ ----------- ------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ---------- --------- ------------ ------------ ---------- --------------- ------------ ----------- ----------- ----------------
TEST T TABLE ASSM USERS 4 165658 18874368 2304 33 65536 1048576 1 2147483645 2147483645 4 DEFAULT DEFAULT DEFAULT
TEST CHECK_TEST TABLE ASSM USERS 4 172906 65536 8 1 65536 1048576 1 2147483645 2147483645 4 DEFAULT DEFAULT DEFAULT
TEST P_RANG_TEST P_200 TABLE PARTITION ASSM TT1 13 130 65536 8 1 65536 1048576 1 2147483645 2147483645 13 DEFAULT DEFAULT DEFAULT
TEST P_RANG_TEST P_400 TABLE PARTITION ASSM USERS 4 172922 65536 8 1 65536 1048576 1 2147483645 2147483645 4 DEFAULT DEFAULT DEFAULT
TEST P_RANG_TEST P_600 TABLE PARTITION ASSM USERS 4 172930 65536 8 1 65536 1048576 1 2147483645 2147483645 4 DEFAULT DEFAULT DEFAULT
TEST P_RANG_TEST P_MAX TABLE PARTITION ASSM USERS 4 172938 65536 8 1 65536 1048576 1 2147483645 2147483645 4 DEFAULT DEFAULT DEFAULT
TEST P_HASH_TEST SYS_P21 TABLE PARTITION ASSM USERS 4 172946 65536 8 1 65536 1048576 1 2147483645 2147483645 4 DEFAULT DEFAULT DEFAULT
TEST P_LIST_TEST NAN_1 TABLE PARTITION ASSM USERS 4 172954 65536 8 1 65536 1048576 1 2147483645 2147483645 4 DEFAULT DEFAULT DEFAULT
TEST P_LIST_TEST NV_1 TABLE PARTITION ASSM USERS 4 172962 65536 8 1 65536 1048576 1 2147483645 2147483645 4 DEFAULT DEFAULT DEFAULT
TEST P_LIST_TEST DEF_1 TABLE PARTITION ASSM USERS 4 172970 65536 8 1 65536 1048576 1 2147483645 2147483645 4 DEFAULT DEFAULT DEFAULT

10 rows selected

SQL> SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='P_RANG_TEST'
2 /

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
P_RANG_TEST P_200 TT1
P_RANG_TEST P_400 USERS
P_RANG_TEST P_600 USERS
P_RANG_TEST P_MAX USERS

--6.自动移动到不同分区。
如果我们需要更新一个分区的键值，浙江导致这条记录归属不同的分区。
update f_regs set id=201 where id=199;
这样我们会收到错误信息：ora-14402

办法是 使用：
alter table p_rang_test enable row movement;
如果要禁止迁移则使用 disable row movement;

--7.重命名分区

alter table p_rang_test rename partition p_200 to p_201;