--创建hash分区表hash_part_tab
create table hash_part_tab (id number,deal_date date,area_code number,contents varchar2(4000))
partition by hash (deal_date)
PARTITIONS 12;
--以下是插入一整年日期随机数和表示福建地区号含义(591到599)的随机数记录,共有10万条,如下:
insert into hash_part_tab
(id, deal_date, area_code, contents)
select rownum,
to_date(to_char(sysdate - 365, 'J') +
TRUNC(DBMS_RANDOM.VALUE(0, 365)),
'J'),
ceil(dbms_random.value(590, 599)),
rpad('*', 400, '*')
from dual
connect by rownum <= 100000;
commit;
--查看当前用户下有哪些分区表
select TABLE_NAME from user_tables a where a.partitioned = 'YES';
--查看分区表名,分区名,表空间等信息
select table_name, partition_name, tablespace_name, high_value from user_tab_partitions where table_name = 'HASH_PART_TAB';
--通过object_id查看每个分区数据分布情况
select * from dba_segments where segment_name = 'HASH_PART_TAB';
select * from dba_objects where object_name='HASH_PART_TAB';
select dbms_rowid.rowid_object(rowid) obj_id, count(*)
from HASH_PART_TAB
group by dbms_rowid.rowid_object(rowid)
order by 1;
--增加新分区P1(13分区,由5分区分裂成12,13分区,原5分区被后浪卷上来)
alter table HASH_PART_TAB add partition P1;
5分区: 11094
select 4476+6618 from dual;--12,13分区
--增加新分区P2
alter table HASH_PART_TAB add partition P2;
--增加新分区P3
alter table HASH_PART_TAB add partition P3;
--增加新分区P4
alter table HASH_PART_TAB add partition P4;
--增加新分区P5,第一个分区OBJ_ID=77369分裂成两个新分区,其他分区数据不变。
alter table HASH_PART_TAB add partition P5;
--HASH 分区表不能通过如下方式进行删除
alter table HASH_PART_TAB drop partition P5;
--ORA-14255: table is not partitioned by Range, List, Composite Range or Composite List method